SSIS XML 数据源 - 无界元素为 NULL [英] SSIS XML datasource - unbounded elements are NULL

问题描述

我们在尝试使用 SSIS 导入 XML 文件时遇到问题.我们有一个更复杂的 XML + XSD 出现此问题，但我们能够自行重现它.

We're experiencing a problem while trying to import an XML file using SSIS. We have a more complex XML + XSD for which this problem occurs, but we were able to reproduce it on its own.

我们有一个包含多个元素的 XML 文件.我们从中生成了 XSD.使用数据可视化工具，我们看到找到了 4 行，但值为 NULL.

We have an XML file with several elements. We generated the XSD from it. Using the data visualizer we see that 4 rows are found, but the values are NULL.

我们忽略了什么，或者我们如何解决这个问题?

What are we overlooking, or how can we solve this?

XML

<root>
  <index>a</index>
  <index>b</index>
  <index>n</index>
  <index>x</index>
</root>

XSD

<?xml version="1.0" encoding="Windows-1252"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="root">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="index" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

截图

是的，我见过这个:SSIS XMLSource只看到 XML 变量中的空值 - 但更改源 XML 并不是我们真正的选择.

Yes, I've seen this one: SSIS XMLSource only seeing null values in XML variable - but changing the source XML is not really an option for us.

推荐答案

我们没有解决这个问题，我们创建了一个解决方法.由于我们无法改变源数据的样子，我们不得不自己转换它.因此，我们编写了一个 XSLT 转换来转换 XML:

We didn't solve this issue we created a workaround. Since we can't change what the source data looks like, we had to transform it ourselves. Therefore, we wrote an XSLT transform to transform the XML from:

<Tag>
  <Folder>Whatever</Folder>
  <Index>2014-03-31</Index>
  <Index>31454534</Index>
  <Index>3274</Index>
  <Index>Testname1 Testname2</Index>
</Tag>

致:

<Tags>
  <Tag Folder="Whatever" Index1="2014-03-31" Index2="31454534" Index3="3274" Index4="Testname1 Testname2" />
</Tags>

XSLT 在下面(我去掉了所有的名字和东西，所以这不是一个有效的例子).我们从 XML 任务运行转换(如下所述:http://blogs.msdn.com/b/mattm/archive/2007/12/15/xml-source-making-things-easier-with-xslt.aspx).我们将结果保存到一个变量中.然后，我们使用 XML 源运行数据流任务，从变量加载 XML.然后所有行都出现.然后我们将其用作我们可以访问的脚本组件的输入:Row.Folder、Row.Index1 ...等.

XSLT is below ( I got rid of all names and stuff, so this is not a working example ). We run the transform from the XML task ( as described here: http://blogs.msdn.com/b/mattm/archive/2007/12/15/xml-source-making-things-easier-with-xslt.aspx ). We save the result into a variable. Then we run a data-flow task with an XML source that loads the XML from the variable. Then all the rows DO appear. We then use this as input for our script-component where we have access to: Row.Folder, Row.Index1 ... etc.

XSLT

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:pmd="http://something"
                xmlns:default="http://something"
                xmlns:k="http://something"
                exclude-result-prefixes="msxsl"
>
  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="/">
    <k:TagList>
      <k:Tags>
        <xsl:apply-templates/>
      </k:Tags>
    </k:TagList>
  </xsl:template>

  <xsl:template match="default:Metadata">
    <xsl:apply-templates/>
  </xsl:template>

  <xsl:template match="pmd:Tag">
    <k:Tag>
      <xsl:attribute name="Folder">
        <xsl:value-of select="pmd:Folder" />
      </xsl:attribute>
      <xsl:attribute name="Index1">
        <xsl:value-of select="pmd:Index[1]" />
      </xsl:attribute>
      <xsl:attribute name="Index2">
        <xsl:value-of select="pmd:Index[2]" />
      </xsl:attribute>
      <xsl:attribute name="Index3">
        <xsl:value-of select="pmd:Index[3]" />
      </xsl:attribute>
      <xsl:attribute name="Index4">
        <xsl:value-of select="pmd:Index[4]" />
      </xsl:attribute>
    </k:Tag>
  </xsl:template>

这篇关于SSIS XML 数据源 - 无界元素为 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！