如何使用SSIS获取XML文件并将其值插入数据库表（SQL Server 2005）

I have XML (see below).

我有XML（见下文）。

I need to insert the records in this XML file into a SQL Server 2005 table using SSIS.

我需要使用SSIS将此XML文件中的记录插入到SQL Server 2005表中。

I'm stuck with the xsd file description. Within Visual Studio when I click the Generate XSD button I get the message "Unable to infer the XSD from the XML file. The XML contains multiple namespaces."

我坚持使用xsd文件描述。在Visual Studio中，当我单击Generate XSD按钮时，我收到消息“无法从XML文件推断出XSD。XML包含多个名称空间。”

Does anyone know of a good resource or tutorial that can help me soft this problem?

有谁知道一个好的资源或教程，可以帮助我解决这个问题？

Please note that I need to perform this task in SSIS.

请注意，我需要在SSIS中执行此任务。

Thanks

谢谢

<?xml version="1.0" encoding="utf-8" ?> 
<Envelope>
    <Body>
        <Env>
            <ACMEHdr:ACMEResponseHdr xmlns:ACMEHdr="ACMEResponseHdr">
                <ACMEHdr:ProcessorName>ACME PREPAID SOLUTIONS</ACMEHdr:ProcessorName>
                <ACMEHdr:FeedName>ACMEPMTRSP</ACMEHdr:FeedName>
                <ACMEHdr:FileDate>08122010</ACMEHdr:FileDate>
                <ACMEHdr:WorkOfDate>08112010</ACMEHdr:WorkOfDate>
                <ACMEHdr:FileSeqNumber>000024</ACMEHdr:FileSeqNumber>
            </ACMEHdr:ACMEResponseHdr>
            <Msg:Message xmlns:Msg="Message">
                <Pmt:PaymentResponse xmlns:Pmt="PaymentResponse">
                    <Pmt:TransactionResponse xmlns:TransResp="TransactionResponse">
                        <TransResp:trnDate>0711201002:10:01.123456</TransResp:trnDate>
                        <TransResp:intTransactionRefId>131BE5E1-701A-42FA-AF8C-D2D38FDCC2EA</TransResp:intTransactionRefId>
                        <TransResp:transAmt>11.88</TransResp:transAmt>
                        <TransResp:strDebitCreditCode>DR</TransResp:strDebitCreditCode>
                        <TransResp:transCurrencyCd>840</TransResp:transCurrencyCd>
                        <TransResp:userID></TransResp:userID>
                        <TransResp:transAmtUSD>11.88</TransResp:transAmtUSD>
                        <TransResp:orderNbr>AM219003F700</TransResp:orderNbr>
                        <TransResp:ACHTransactionID></TransResp:ACHTransactionID>
                        <TransResp:fundSourceType>02</TransResp:fundSourceType>
                        <TransResp:sessionId>MondaySep12201022134314153720392</TransResp:sessionId>
                        <TransResp:requestToken>ACHMEN414d51204c50505741373838202020202421824b12246620</TransResp:requestToken>
                        <TransResp:authorizationCode>A98765</TransResp:authorizationCode>
                        <TransResp:reasonCode></TransResp:reasonCode>
                        <TransResp:reasonCodeDesc></TransResp:reasonCodeDesc>
                        <TransResp:addressMatch>Y</TransResp:addressMatch>
                        <TransResp:postalCdMatchInd>Y</TransResp:postalCdMatchInd>
                        <TransResp:SENbr>295176482889</TransResp:SENbr>
                        <TransResp:SEName>American Express PASS Card Fee</TransResp:SEName>
                        <TransResp:transResponse>A</TransResp:transResponse>
                    </Pmt:TransactionResponse>
                    <Pmt:TransactionResponse xmlns:TransResp="TransactionResponse">
                        <TransResp:trnDate>0711201002:15:01.123456</TransResp:trnDate>
                        <TransResp:intTransactionRefId>46233C40-3C33-4914-B447-B3E60BB04148</TransResp:intTransactionRefId>
                        <TransResp:transAmt>11.88</TransResp:transAmt>
                        <TransResp:strDebitCreditCode>DR</TransResp:strDebitCreditCode>
                        <TransResp:transCurrencyCd>840</TransResp:transCurrencyCd>
                        <TransResp:userID></TransResp:userID>
                        <TransResp:transAmtUSD>11.88</TransResp:transAmtUSD>
                        <TransResp:orderNbr>AM009003F701</TransResp:orderNbr>
                        <TransResp:ACHTransactionID>ACH1234567</TransResp:ACHTransactionID>
                        <TransResp:fundSourceType>05</TransResp:fundSourceType>
                        <TransResp:sessionId>MondayAug12201022134214123456789</TransResp:sessionId>
                        <TransResp:requestToken></TransResp:requestToken>
                        <TransResp:authorizationCode></TransResp:authorizationCode>
                        <TransResp:reasonCode></TransResp:reasonCode>
                        <TransResp:reasonCodeDesc></TransResp:reasonCodeDesc>
                        <TransResp:addressMatch></TransResp:addressMatch>
                        <TransResp:postalCdMatchInd></TransResp:postalCdMatchInd>
                        <TransResp:SENbr></TransResp:SENbr>
                        <TransResp:SEName></TransResp:SEName>
                        <TransResp:transResponse>A</TransResp:transResponse>
                    </Pmt:TransactionResponse>
                </Pmt:PaymentResponse>
            </Msg:Message>
            <ACMEFtr:ACMEResponseFooter xmlns:ACMEFtr="ACMEResponseFooter">
                <ACMEFtr:countDetail>2</ACMEFtr:countDetail>
                <ACMEFtr:countCreditDetail>0</ACMEFtr:countCreditDetail>
                <ACMEFtr:countDebitDetail>2</ACMEFtr:countDebitDetail>
                <ACMEFtr:hashTotalAmt>23.76</ACMEFtr:hashTotalAmt>
                <ACMEFtr:hashTotalCreditAmt>0.00</ACMEFtr:hashTotalCreditAmt>
                <ACMEFtr:hashTotalDebitAmt>23.76</ACMEFtr:hashTotalDebitAmt>
            </ACMEFtr:ACMEResponseFooter>
        </Env>
    </Body>
</Envelope>

4 个解决方案

#1

SSIS does not handle multiple namespaces in the XML source file. Your file has namespaces that include ACMEHdr, Msg, Pmt, TransResp, etc. You can find examples where you see the format <Namespace:Element>. The first step to avoid multiple namespaces is to transform your source file to a format that doesn’t refer to the namespaces.

SSIS不处理XML源文件中的多个名称空间。您的文件包含名称空间，包括ACMEHdr，Msg，Pmt，TransResp等。您可以找到格式为的示例。避免多个名称空间的第一步是将源文件转换为不引用名称空间的格式。：element>

SSIS has an XML task that can do the transformation. Add the XML task to an SSIS Control Flow and edit it. Change the OperationType property value to XSLT, the SourceType to File connection and the Source to your source file that has the problem.

SSIS有一个可以进行转换的XML任务。将XML任务添加到SSIS控制流并进行编辑。将OperationType属性值更改为XSLT，将SourceType更改为File连接，将Source更改为存在问题的源文件。

Set the SaveOperationResult property to True and expand the OperationResult branch. Set DestinationType to File Connection and the Destination to a new XML file.

将SaveOperationResult属性设置为True并展开OperationResult分支。将DestinationType设置为File Connection，将Destination设置为新的XML文件。

Add the following to a new file and save it with an xslt file extension.

将以下内容添加到新文件并使用xslt文件扩展名保存。

<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="1.0"         xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" indent="no" />
  <xsl:template match="/|comment()|processing-instruction()">
    <xsl:copy>
      <xsl:apply-templates />
    </xsl:copy>
  </xsl:template>
  <xsl:template match="*">
    <xsl:element name="{local-name()}">
      <xsl:apply-templates select="@*|node()" />
    </xsl:element>
  </xsl:template>
  <xsl:template match="@*">
    <xsl:attribute name="{local-name()}">
      <xsl:value-of select="." />
    </xsl:attribute>
  </xsl:template>
</xsl:stylesheet>

This code comes from the following article, which also gives more information about cleaning up a source XML file: http://www.drdobbs.com/windows/219700581

此代码来自以下文章，该文章还提供了有关清理源XML文件的更多信息：http：//www.drdobbs.com/windows/219700581

Back in the XML task, set the SecondOperandType to File connection and the Second Operand to your new XSLT file.

回到XML任务，将SecondOperandType设置为File连接，将Second Operand设置为新的XSLT文件。

When you run the XML task, it will take your original file and apply the transformation rules defined in the XSLT file. The results will be saved in your new XML file. This task only needs to be run once for the original XML file. When you look at the new file, you’ll see the same data as in the original, but without namespace references.

运行XML任务时，它将获取原始文件并应用XSLT文件中定义的转换规则。结果将保存在新的XML文件中。此任务只需要为原始XML文件运行一次。查看新文件时，您将看到与原始文件相同的数据，但没有名称空间引用。

Now you can return to your data flow and alter the XML Source to reference the new XML file. Click on the Generate XSD and you should be able to avoid your error.

现在，您可以返回到数据流并更改XML Source以引用新的XML文件。单击Generate XSD，您应该可以避免错误。

When you click on the Columns tab in your XML Source, you will probably see a warning. This is because the data types may not be fully defined (for example there’s no mention of string lengths). This shouldn’t be a problem as long as the default data type (255-character Unicode string) meets your needs.

单击XML Source中的Columns选项卡时，您可能会看到警告。这是因为数据类型可能没有完全定义（例如，没有提到字符串长度）。只要默认数据类型（255个字符的Unicode字符串）满足您的需要，这应该不是问题。

Here are two references that you can use to work with the SSIS XML Task and XML Source objects. And here are good resources for XML, XSD and XSLT.

以下是两个可用于处理SSIS XML Task和XML Source对象的引用。这里有很好的XML，XSD和XSLT资源。

#2

Have you tried loading the raw XML into a XML data type in a database table and then converting the data into database tables via stored procedures? I generally prefer this method so that I can preserve the original XML code in case something changes in the schema that I am unaware of at the time of the import such as the source system adding new nodes to the file. If you go with this approach, then you could use sp_xml_preparedocument and OPENXML to convert the data.

您是否尝试将原始XML加载到数据库表中的XML数据类型中，然后通过存储过程将数据转换为数据库表？我通常更喜欢这种方法，这样我就可以保留原始的XML代码，以防在导入时我不知道的架构发生变化，例如源系统向文件添加新节点。如果采用这种方法，那么可以使用sp_xml_preparedocument和OPENXML来转换数据。

#3

Here is what i tried and it worked for me:

这是我尝试过的，它对我有用：

I saved your xml as .xml file.
我将您的xml保存为.xml文件。
I opened it in Visual Studio 2008.
我在Visual Studio 2008中打开它。
On the Xml Editor toolbar i clicked "create schema" icon.
在Xml编辑器工具栏上，我单击了“创建模式”图标。
This generated 5 xsd files.
这生成了5个xsd文件。
I ran xsd.exe file.xsd file2.xsd fileN.xsd /classes
我运行了xsd.exe file.xsd file2.xsd fileN.xsd / classes
This generated a C# class that represents the schemas.
这生成了一个代表模式的C＃类。
From here you can use SSIS script source component to de-serialize your xml file into dataflows and use other SSIS tasks to do the inserts into SQL.
从这里，您可以使用SSIS脚本源组件将xml文件反序列化为数据流，并使用其他SSIS任务来插入SQL。

Here is the file

这是文件

#4

The support for the namespaces in SSIS has been added as of version 2012 (in the XML Task to be specific).

自版本2012起添加了对SSIS中命名空间的支持（在XML任务中具体）。

#1

Set the SaveOperationResult property to True and expand the OperationResult branch. Set DestinationType to File Connection and the Destination to a new XML file.

将SaveOperationResult属性设置为True并展开OperationResult分支。将DestinationType设置为File Connection，将Destination设置为新的XML文件。

Add the following to a new file and save it with an xslt file extension.

将以下内容添加到新文件并使用xslt文件扩展名保存。

<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="1.0"         xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" indent="no" />
  <xsl:template match="/|comment()|processing-instruction()">
    <xsl:copy>
      <xsl:apply-templates />
    </xsl:copy>
  </xsl:template>
  <xsl:template match="*">
    <xsl:element name="{local-name()}">
      <xsl:apply-templates select="@*|node()" />
    </xsl:element>
  </xsl:template>
  <xsl:template match="@*">
    <xsl:attribute name="{local-name()}">
      <xsl:value-of select="." />
    </xsl:attribute>
  </xsl:template>
</xsl:stylesheet>