如何使用SSIS包导入XML文件?

I’m trying to import a XML file into SQL server using SSIS. The XML file is structured like this

我正在尝试使用SSIS将XML文件导入SQL服务器。XML文件的结构是这样的

<?xml version="1.0" encoding="utf-8"?>
<dataset  xmlns="http://developer.cognos.com/schemas/xmldata/1/"  xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">
<!--
<dataset
    xmlns="http://developer.cognos.com/schemas/xmldata/1/"
    xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"
    xs:schemaLocation="http://developer.cognos.com/schemas/xmldata/1/ xmldata.xsd"
>
-->
    <metadata>
          <item name="ORDERNUM" type="xs:string" length="26"/>
          <item name="Business Stream" type="xs:string" length="482"/>
          <item name="ORDERNO" type="xs:decimal" precision="8"/>
          <item name="ORDERTYPE" type="xs:string"/>
          <item name="ITEM_NO" type="xs:decimal" precision="8"/>
          <item name="ITEM_NO2" type="xs:string" length="52"/>
          <item name="PG" type="xs:int" precision="1"/>
          <item name="SG" type="xs:int" precision="1"/>
          <item name="LINEDESC" type="xs:string" length="122"/>
          <item name="CUSTNO" type="xs:decimal" precision="8"/>
          <item name="ORDERQNTY" type="xs:decimal" scale="3" precision="77"/>
          <item name="COST_VALUE" type="xs:decimal" scale="5" precision="77"/>
          <item name="EXTD_LIST" type="xs:decimal" scale="4" precision="77"/>
          <item name="EXTD_VALUE" type="xs:decimal" scale="4" precision="77"/>
          <item name="LINE_NO" type="xs:decimal" precision="9"/>
          <item name="TDATE" type="xs:date"/>
          <item name="TQUARTER" type="xs:decimal" precision="1"/>
          <item name="INVOICEDAT" type="xs:date"/>
          <item name="IQUARTER" type="xs:decimal" precision="1"/>
          <item name="ORIGNUM" type="xs:decimal" precision="10"/>
          <item name="ORIGTYPE" type="xs:string" length="22"/>
          <item name="TYPECALC" type="xs:string"/>
          <item name="SEQ" type="xs:int" precision="1"/>
          <item name="INC_IN_COU" type="xs:string"/>
          <item name="COSTMOD" type="xs:string" length="4"/>
          <item name="GROSSMOD" type="xs:string" length="4"/>
          <item name="CHFLOOR" type="xs:string" length="16"/>
          <item name="Group Customer Description" type="xs:string" length="482"/>
          <item name="Sales Area Description" type="xs:string" length="482"/>
          <item name="Sales Area" type="xs:string" length="8"/>
          <item name="Segment / Region Description" type="xs:string" length="482"/>
          <item name="Segment / Region" type="xs:string" length="8"/>
          <item name="ScheduledDespatchDate" type="xs:date"/>
          <item name="Status Sid" type="xs:int" precision="1"/>
          <item name="ShiptoCustomerNumber" type="xs:decimal" precision="8"/>
          <item name="CONT_TYPE" type="xs:string" length="122"/>
          <item name="EST_START" type="xs:date"/>
          <item name="EST_COMPLTN" type="xs:date"/>
          <item name="ACT_START" type="xs:date"/>
          <item name="ACT_COMPLTN" type="xs:date"/>
          <item name="Project_Status" type="xs:string" length="18"/>
    </metadata>
    <data>
        <row>
            <value>SO1897977</value>
            <value>Products &amp; Applications</value>
            <value>1897977</value>
            <value>SO</value>
            <value>731305</value>
            <value>0670800</value>
            <value>67</value>
            <value>3</value>
            <value>3/4&quot; HM10/8 CI Steam TrapBSP</value>
            <value>20021</value>
            <value>1</value>
            <value>62.136</value>
            <value>187.1</value>
            <value>187.1</value>
            <value>1000</value>
            <value>2011-11-17</value>
            <value>4</value>
            <value>2011-11-17</value>
            <value>4</value>
            <value xs:nil="true" />
            <value> </value>
            <value>SO</value>
            <value>1</value>
            <value>Y</value>
            <value>N</value>
            <value>N</value>
            <value>PDQ/KSP</value>
            <value>Other Customers</value>
            <value>Crumb Jonathan</value>
            <value>917</value>
            <value>Southern Division</value>
            <value>STH</value>
            <value>2011-11-17</value>
            <value>70</value>
            <value>60206</value>
            <value xs:nil="true" />
            <value>1900-01-01</value>
            <value>1900-01-01</value>
            <value>1900-01-01</value>
            <value>1900-01-01</value>
            <value xs:nil="true" />
        </row>
        <row>
            <value>SO1897977</value>
            <value>Products &amp; Applications</value>
            <value>1897977</value>
            <value>SO</value>
            <value>799262</value>
            <value>1643100</value>
            <value>164</value>
            <value>60</value>
            <value>1/2&quot; FIG12 Bronze Y Type BSP+ 0.8 SS</value>
            <value>20021</value>
            <value>5</value>
            <value>54.051</value>
            <value>130.2</value>
            <value>130.2</value>
            <value>2000</value>
            <value>2011-11-17</value>
            <value>4</value>
            <value>2011-11-17</value>
            <value>4</value>
            <value xs:nil="true" />
            <value> </value>
            <value>SO</value>
            <value>1</value>
            <value>Y</value>
            <value>N</value>
            <value>N</value>
            <value>PDQ/KSP</value>
            <value>Other Customers</value>
            <value>Crumb Jonathan</value>
            <value>917</value>
            <value>Southern Division</value>
            <value>STH</value>
            <value>2011-11-17</value>
            <value>70</value>
            <value>60206</value>
            <value xs:nil="true" />
            <value>1900-01-01</value>
            <value>1900-01-01</value>
            <value>1900-01-01</value>
            <value>1900-01-01</value>
            <value xs:nil="true" />
        </row>

I have made a XSD file that looks like this

我创建了一个类似于这样的XSD文件

<?xml version="1.0"?>
<schema xmlns:xs="http://www.w3.org/2001/XMLSchema-instance" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://developer.cognos.com/schemas/xmldata/1/" xmlns="http://www.w3.org/2001/XMLSchema">
  <element name="dataset">
    <complexType>
      <sequence>
        <element minOccurs="0" name="data">
          <complexType>
            <sequence>
              <element minOccurs="0" maxOccurs="1" name="row">
                <complexType>
                  <sequence>
                      <element name="ORDERNUM" type="string" />
                      <element name="BusinessStream" type="string" />
                      <element name="ORDERNO" type="decimal"/>
                      <element name="ORDERTYPE" type="string"/>
                      <element name="ITEM_NO" type="decimal" />
                      <element name="ITEM_NO2" type="string" />
                      <element name="PG" type="int" />
                      <element name="SG" type="int" />
                      <element name="LINEDESC" type="string" />
                      <element name="CUSTNO" type="decimal" />
                      <element name="ORDERQNTY" type="decimal" />
                      <element name="COST_VALUE" type="decimal" />
                      <element name="EXTD_LIST" type="decimal" />
                      <element name="EXTD_VALUE" type="decimal"/>
                      <element name="LINE_NO" type="decimal" />
                      <element name="TDATE" type="date"/>
                      <element name="TQUARTER" type="decimal" />
                      <element name="INVOICEDAT" type="date"/>
                      <element name="IQUARTER" type="decimal" />
                      <element name="ORIGNUM" type="decimal" />
                      <element name="ORIGTYPE" type="string" />
                      <element name="TYPECALC" type="string"/>
                      <element name="SEQ" type="int" />
                      <element name="INC_IN_COU" type="string"/>
                      <element name="COSTMOD" type="string" />
                      <element name="GROSSMOD" type="string" />
                      <element name="CHFLOOR" type="string" />
                      <element name="GroupCustomerDescription" type="string" />
                      <element name="SalesAreaDescription" type="string" />
                      <element name="SalesArea" type="string" />
                      <element name="SegmentRegionDescription" type="string" />
                      <element name="SegmentRegion" type="string" />
                      <element name="ScheduledDespatchDate" type="date"/>
                      <element name="StatusSid" type="int" />
                      <element name="ShiptoCustomerNumber" type="decimal" />
                      <element name="CONT_TYPE" type="string" />
                      <element name="EST_START" type="date"/>
                      <element name="EST_COMPLTN" type="date"/>
                      <element name="ACT_START" type="date"/>
                      <element name="ACT_COMPLTN" type="date"/>
                      <element name="Project_Status" type="string" />
                  </sequence>
                </complexType>
              </element>
            </sequence>
          </complexType>
        </element>
      </sequence>
    </complexType>
  </element>
</schema>

The problem is when I run the task I get the correct number of rows but all the values are null. I’m quite new to XML so I suspect it is something really silly, can anyone help?

问题是，当我运行这个任务时，我得到了正确的行数，但是所有的值都是空的。我对XML很陌生，所以我怀疑这是很愚蠢的事情，谁能帮忙吗?

2 个解决方案

#1

This may not be the answer to your problem. I used trial and error to find only the cause of the problem. One of the possible solution seems to be that you need to reconstruct your XML file with the element names as defined in the XSD file.

这可能不是你问题的答案。我反复试验，只找到问题的原因。一种可能的解决方案似乎是，您需要使用XSD文件中定义的元素名称来重构XML文件。

I took your XML file and XSD file to create a new SSIS package. I used XML Source within Data Flow Task to read the files.

我使用您的XML文件和XSD文件创建了一个新的SSIS包。我在数据流任务中使用XML Source读取文件。

When I executed the package, I got the below results. All the values were NULL as you had described in your question.

当我执行这个包时，我得到了下面的结果。正如您在问题中所描述的，所有值都为空。

After looking at the XSD file, I felt that the names you have defined in the elements in XSD files like ORDERNUM and BusinessStream should have corresponding nodes in the XML file but they were missing. So, I changed the first element that had the nodes value to ORDERNUM and BusinessStream.

查看了XSD文件之后，我觉得您在ORDERNUM和BusinessStream等XSD文件中的元素中定义的名称应该在XML文件中具有相应的节点，但是它们没有。因此，我修改了第一个元素，它将节点值设置为ORDERNUM和BusinessStream。

Re-executed the package and this time the values were shown correctly. Note that I changed the values only in the first row and left the second row unchanged. That's why the values are still NULL.

重新执行包，这次值显示正确。注意，我只更改了第一行中的值，并保持了第二行不变。这就是为什么这些值仍然是空的。

Hope that helps.

希望有帮助。

#2

The second of the two articles below shows how to use an XLST file in SSIS to transform the XML source file into something you can use for your described purpose. I provide two XSLT file contents at the bottom.

下面两篇文章的第二部分展示了如何在SSIS中使用XLST文件将XML源文件转换为您可以用于描述的目的的文件。我在底部提供两个XSLT文件内容。

http://sqlserverpedia.com/blog/sql-server-bloggers/loading-xml-using-ssis/

http://sqlserverpedia.com/blog/sql-server-bloggers/loading-xml-using-ssis/

http://blog.hoegaerden.be/2011/04/20/loading-complex-xml-using-ssis/

http://blog.hoegaerden.be/2011/04/20/loading-complex-xml-using-ssis/

Repairs to listed data sample:

对所列数据样本的修复:

1) Remove the space in what appears to be a comment at the beginning: '...xmldata/1/ xmldata.xsd...' to 'xmldata/1/xmldata.xsd'

1)在开始的时候，删除似乎是评论的空间:……xmldata / 1 / xmldata.xsd……”到“xmldata / 1 / xmldata.xsd '

2) Add </data></dataset> to the very end of the data.

2)在数据的最后添加。

You can test it here (Hmmmm, it doesn't seem to work there!):

你可以在这里测试一下(嗯，似乎在那里不可行!)

http://www.w3schools.com/xsl/tryxslt.asp?xmlfile=cdcatalog&xsltfile=cdcatalog

http://www.w3schools.com/xsl/tryxslt.asp?xmlfile=cdcatalog&xsltfile=cdcatalog

Or add this as the second line of XML to test in IE by opening the XML file:

或将其作为IE中测试的第二行XML，打开XML文件:

<?xml-stylesheet type="text/xsl" href="Cognos.xsl"?>

Look over the xslt listings carefully and note the differences in the 'output' element. Note that I needed to define in my XSLT file the namespace that was used in the XML file's 'dataset' element, and give it a name which I can use to prefix references to the nodes defined within 'dataset'. Assume case sensitivity matters everywhere because it probably does. Do some reading on XSLT elements at:

仔细检查xslt清单并注意“输出”元素中的差异。注意，我需要在XSLT文件中定义XML文件的“dataset”元素中使用的名称空间，并给它一个名称，用于对“dataset”中定义的节点的引用进行前缀。假设大小写敏感在任何地方都很重要，因为它可能会。请阅读以下XSLT元素:

http://www.w3schools.com/xsl/

http://www.w3schools.com/xsl/

Cognos.xsl to list contents as 'pipe' (vertical bar) delimited file. There will be an empty column at the end - due to the trailing delimiter - which you will need to ignore in your code that uses the file.

Cognos。将内容列表作为“管道”(竖线)分隔文件。最后将有一个空列—由于末尾的分隔符—您需要在使用该文件的代码中忽略它。

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:cog="http://developer.cognos.com/schemas/xmldata/1/">
 <xsl:output method="text" encoding="UTF-8" indent="no" 
  omit-xml-declaration="yes"
/>

  <xsl:template match="/">
    <xsl:apply-templates select="cog:dataset/cog:metadata/cog:item">
    </xsl:apply-templates>
    <xsl:text>&#13;&#10;</xsl:text>
    <xsl:apply-templates select="cog:dataset/cog:data/cog:row">
    </xsl:apply-templates>
  </xsl:template>

  <xsl:template match="cog:dataset/cog:metadata/cog:item">
      <xsl:value-of select="@name"/>
    <xsl:text>|</xsl:text>
  </xsl:template>

  <xsl:template match="cog:dataset/cog:data/cog:row">
    <xsl:text>&#13;&#10;</xsl:text>
      <xsl:apply-templates select="./cog:value">
      </xsl:apply-templates>
  </xsl:template>

  <xsl:template match="cog:dataset/cog:data/cog:row/cog:value">
      <xsl:value-of select="."/>
    <xsl:text>|</xsl:text>
  </xsl:template>

</xsl:stylesheet>

Cognos.xsl to list contents as HTML table:

Cognos。将内容列表为HTML表的xsl:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:cog="http://developer.cognos.com/schemas/xmldata/1/">
 <xsl:output method="html" encoding="UTF-8" indent="yes" 
/>

  <xsl:template match="/">
    <html><body>
    <table border="1"><tr>
    <xsl:apply-templates select="cog:dataset/cog:metadata/cog:item">
    </xsl:apply-templates>
    </tr>
    <xsl:text>&#13;&#10;</xsl:text>
    <xsl:apply-templates select="cog:dataset/cog:data/cog:row">
    </xsl:apply-templates>
    </table>
    </body></html>
  </xsl:template>

  <xsl:template match="cog:dataset/cog:metadata/cog:item">
    <td>
    <xsl:value-of select="@name"/>
    </td>
  </xsl:template>

  <xsl:template match="cog:dataset/cog:data/cog:row">
    <tr>
    <xsl:apply-templates select="./cog:value">
    </xsl:apply-templates>
    </tr>
  </xsl:template>

  <xsl:template match="cog:dataset/cog:data/cog:row/cog:value">
    <td>
    <xsl:choose>
      <xsl:when test=". !='' and . !=' '">
        <xsl:value-of select="." />
      </xsl:when>
      <xsl:when test=". =' '">
        <xsl:text>space</xsl:text>
      </xsl:when>
      <xsl:otherwise>
        <xsl:text>null</xsl:text>
      </xsl:otherwise>
    </xsl:choose>
    </td>
  </xsl:template>

</xsl:stylesheet>

And, yes, you are all welcome for this concise collection of requirements, fixes, links, and techniques which I have spent nearly 14 hours researching. Maybe I will turn this in to an article.

是的，我花了将近14个小时的时间来研究这些需求、修复、链接和技术。也许我要把它变成一篇文章。