如何使用 SSIS 包导入 XML 文件?
我正在尝试使用 SSIS 将 XML 文件导入到 SQL Server 中。 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 & Applications</value>
<value>1897977</value>
<value>SO</value>
<value>731305</value>
<value>0670800</value>
<value>67</value>
<value>3</value>
<value>3/4" 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 & Applications</value>
<value>1897977</value>
<value>SO</value>
<value>799262</value>
<value>1643100</value>
<value>164</value>
<value>60</value>
<value>1/2" 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>
我制作了一个如下所示的 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>
问题是当我运行任务时,我得到了正确的行数,但所有值均为空。我对 XML 很陌生,所以我怀疑这真的很愚蠢,有人可以帮忙吗?
I’m trying to import a XML file into SQL server using SSIS. The XML file is structured like this
<?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 & Applications</value>
<value>1897977</value>
<value>SO</value>
<value>731305</value>
<value>0670800</value>
<value>67</value>
<value>3</value>
<value>3/4" 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 & Applications</value>
<value>1897977</value>
<value>SO</value>
<value>799262</value>
<value>1643100</value>
<value>164</value>
<value>60</value>
<value>1/2" 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
<?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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这可能不是您问题的答案。我通过反复试验来
仅
找到问题的原因。可能的解决方案之一似乎是您需要使用 XSD 文件中定义的元素名称重建 XML 文件。我使用了您的 XML 文件和 XSD 文件来创建一个新的 SSIS 包。我在数据流任务中使用 XML Source 来读取文件。
当我执行该包时,我得到了以下结果。正如您在问题中所描述的,所有值都是 NULL。
查看 XSD 文件后,我觉得您在 XSD 文件中的元素中定义的名称如
ORDERNUM
和BusinessStream
在 XML 文件中应该有相应的节点,但它们丢失了。因此,我将具有节点value
的第一个元素更改为ORDERNUM
和BusinessStream
。重新执行包,这次值显示正确。请注意,我仅更改了第一行中的值,而第二行保持不变。这就是为什么这些值仍然为 NULL。
希望有所帮助。
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.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.
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
andBusinessStream
should have corresponding nodes in the XML file but they were missing. So, I changed the first element that had the nodesvalue
toORDERNUM
andBusinessStream
.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.
下面两篇文章中的第二篇介绍了如何在 SSIS 中使用 XLST 文件将 XML 源文件转换为可用于您所描述目的的文件。我提供两个
XSLT 文件内容位于底部。
http://sqlserverpedia.com/blog/sql-server-bloggers/loading-xml-using-ssis/
http://blog.hoegaerden.be/2011/04/20/loading-complex-xml-using-ssis/
修复列出的数据示例:
1)删除出现的空格在开头添加注释: '...xmldata/1/ xmldata.xsd...' 到 'xmldata/1/xmldata.xsd'
2) 添加
到数据的最末尾。
您可以在这里测试它(嗯,它似乎在那里不起作用!):
http://www.w3schools.com/xsl/tryxslt.asp?xmlfile=cdcatalog&xsltfile=cdcatalog
或者将其添加为 XML 的第二行,通过打开 XML 文件在 IE 中进行测试:
仔细查看 xslt 列表并注意“output”元素中的差异。请注意,我需要在 XSLT 文件中定义 XML 文件的“dataset”元素中使用的命名空间,并为其指定一个名称,我可以使用该名称作为对“dataset”中定义的节点的引用的前缀。假设区分大小写在任何地方都很重要,因为它可能确实如此。请阅读以下 XSLT 元素:
http://www.w3schools.com/xsl/
Cognos。 xsl 将内容列为“管道”(竖线)分隔文件。由于尾随分隔符,末尾会有一个空列,您需要在使用该文件的代码中忽略它。
Cognos.xsl 以 HTML 表格形式列出内容:
是的,欢迎大家阅读我花了近 14 个小时研究的需求、修复、链接和技术的简明集合。也许我会把它写成一篇文章。
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.
http://sqlserverpedia.com/blog/sql-server-bloggers/loading-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'
2) Add
</data></dataset>
to the very end of the data.You can test it here (Hmmmm, it doesn't seem to work there!):
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:
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:
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.xsl to list contents as HTML table:
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.