使用SSIS加载带多命名空间的XML至SQL Server遇问题

嘿，我来帮你搞定这个SSIS加载带多命名空间XML的问题！你之前用XML Task转XSLT没生效，大概率是没正确处理那些ns5、ns3前缀，咱们一步步来解决：

首先先把你的XML贴出来方便参考：

<THExport>
  <Txn>
    <ns5:ID>1160013</ns5:ID>
    <ns5:LinkID>1130037</ns5:LinkID>
    <ns5:BucketType>1</ns5:BucketType>
    <ns5:Date>20151125105715</ns5:Date>
    <ns5:ExtRef>15</ns5:ExtRef>
    <ns5:Type>18</ns5:Type>
    <ns5:Mode>2</ns5:Mode>
    <ns5:VoidStatus>0</ns5:VoidStatus>
    <ns5:FailedStatus>0</ns5:FailedStatus>
    <ns5:Source>11</ns5:Source>
    <ns5:PurchAmt>888.88</ns5:PurchAmt>
    <ns5:DiscAmt>0.0</ns5:DiscAmt>
    <ns5:RdmAmt>10.0</ns5:RdmAmt>
    <ns5:AdjAmt>878.88</ns5:AdjAmt>
    <ns5:AccountID>/XID/2000000000/200000000050</ns5:AccountID>
    <ns5:ProductID>/EID/3000000002/411420******0050</ns5:ProductID>
    <ns5:MerchantID>000000000011111</ns5:MerchantID>
    <ns5:MerchantName>Kinokuniya Orchard</ns5:MerchantName>
    <ns5:DeviceID>00001111</ns5:DeviceID>
    <ns5:Operation>
      <ns5:Type>2</ns5:Type>
      <ns5:Entity>
        <ns3:Type>PL</ns3:Type>
        <ns3:ID>262</ns3:ID>
        <ns3:Number>262</ns3:Number>
        <ns3:Name>MonPL_ARJ</ns3:Name>
      </ns5:Entity>
      <ns5:AuxEntity>
        <ns5:Type>OF</ns5:Type>
        <ns5:ID>125</ns5:ID>
        <ns5:Name>MonPOSItemOffer_ARJ</ns5:Name>
        <ns5:Channel>POS</ns5:Channel>
        <ns5:Nature>I</ns5:Nature>
        <ns5:Quantity>1.0</ns5:Quantity>
      </ns5:AuxEntity>
      <ns5:Amount>-5.0</ns5:Amount>
      <ns5:ExpiryDate>20161124</ns5:ExpiryDate>
    </ns5:Operation>
    <ns5:Operation>
      <ns5:Type>2</ns5:Type>
      <ns5:Entity>
        <ns3:Type>PL</ns3:Type>
        <ns3:ID>262</ns3:ID>
        <ns3:Number>262</ns3:Number>
        <ns3:Name>MonPL_ARJ</ns3:Name>
      </ns5:Entity>
      * Offer details for point redemption from the first pool expiry slot
      <ns5:AuxEntity>
        <ns5:Type>OF</ns5:Type>
        <ns5:ID>125</ns5:ID>
        <ns5:Name>MonPOSItemOffer_ARJ</ns5:Name>
        <ns5:Channel>POS</ns5:Channel>
        <ns5:Nature>I</ns5:Nature>
        <ns5:Quantity>0.0</ns5:Quantity>
      </ns5:AuxEntity>
      <ns5:Amount>-5.0</ns5:Amount>
      <ns5:ExpiryDate>20161125</ns5:ExpiryDate>
    </ns5:Operation>
  </Txn>
</THExport>

方案一：修正XSLT转换（解决你之前XML Task无效的问题） #

之前XSLT没生效，核心原因是没处理这些未声明的命名空间前缀。给你一个能自动移除所有前缀的XSLT，用XML Task执行这个转换后，XML就变成无前缀的标准格式，加载起来毫无压力：

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" indent="yes"/>
    
    <!-- 匹配所有节点，只保留本地名称（去掉前缀） -->
    <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:template match="text() | comment() | processing-instruction()">
        <xsl:copy/>
    </xsl:template>
</xsl:stylesheet>

XML Task配置步骤：

• 选择OperationType为XSLT
• 设置SourceType为你的XML源（比如文件连接管理器）
• 设置XSLTSourceType为上述XSLT文件
• 设置DestinationType为文件或变量，用来保存转换后的XML

方案二：直接用XML Source组件加载（无需转换） #

如果不想走XSLT转换的路子，也可以直接在XML Source里配置命名空间映射：

1. 先给你的XML补全命名空间声明（原XML其实是无效的，因为前缀没绑定URI，你可以临时在根节点加，或者在XSD里定义）：
   修改根节点为：

   <THExport xmlns:ns5="http://tempuri.org/ns5" xmlns:ns3="http://tempuri.org/ns3">
   

   （URI随便填就行，只要前缀对应上，因为你的XML没实际用到URI）

2. 在SSIS的XML Source组件中：

   • 选择你的XML源
   • 点击Generate XSD自动生成架构，或者手动编辑XSD，在XSD开头声明对应的命名空间：

     <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
                xmlns:ns5="http://tempuri.org/ns5"
                xmlns:ns3="http://tempuri.org/ns3"
                targetNamespace="http://tempuri.org/ns5"
                elementFormDefault="qualified">
         <!-- 这里会自动生成对应你的XML节点结构 -->
     </xs:schema>
     

   • 切换到Namespace mappings标签，把ns5和ns3分别映射到你填的URI

这样XML Source就能正确识别带前缀的节点了。

方案三：用脚本组件解析（最灵活的方式） #

如果上述方案都不符合你的需求，用脚本组件手动解析是最灵活的选择，完全不用管命名空间的问题：

1. 在数据流任务中添加脚本组件，选择Source类型
2. 在脚本编辑器里，添加你需要输出的列（比如ID、LinkID、BucketType等）
3. 在CreateNewOutputRows方法里，用C#代码解析XML：

using System.Xml;

public override void CreateNewOutputRows()
{
    // 从变量读取XML内容，替换成你的变量名
    string xmlContent = Variables.YourXmlVariable;
    XmlDocument doc = new XmlDocument();
    doc.LoadXml(xmlContent);
    
    // 找到Txn节点，用local-name()匹配，避开命名空间前缀
    XmlNode txnNode = doc.SelectSingleNode("//Txn");
    if (txnNode != null)
    {
        Output0Buffer.AddRow();
        // 提取Txn下的字段
        Output0Buffer.ID = txnNode.SelectSingleNode("*[local-name()='ID']").InnerText;
        Output0Buffer.LinkID = txnNode.SelectSingleNode("*[local-name()='LinkID']").InnerText;
        Output0Buffer.BucketType = int.Parse(txnNode.SelectSingleNode("*[local-name()='BucketType']").InnerText);
        Output0Buffer.PurchAmt = decimal.Parse(txnNode.SelectSingleNode("*[local-name()='PurchAmt']").InnerText);
        // 其他字段以此类推
        
        // 处理多个Operation节点
        XmlNodeList operationNodes = txnNode.SelectNodes("*[local-name()='Operation']");
        foreach (XmlNode opNode in operationNodes)
        {
            // 如果需要把Operation作为单独行，可以添加第二个输出，或者合并到主行
            string opType = opNode.SelectSingleNode("*[local-name()='Type']").InnerText;
            XmlNode entityNode = opNode.SelectSingleNode("*[local-name()='Entity']");
            string plType = entityNode.SelectSingleNode("*[local-name()='Type']").InnerText;
            // 提取Operation的其他字段...
        }
    }
}

这个方法直接通过节点的本地名称匹配，完全绕开了命名空间的问题，适合复杂结构的XML。

内容的提问来源于stack exchange，提问作者ZAlawadi