SSIS脚本任务中忽略XML命名空间写入SQL Server表遇阻求助

在SSIS（Visual Studio）脚本任务中，尝试忽略XML命名空间，将XML数据转换后写入SQL Server表。XML数据通过变量传入，代码可进入循环，但使用XDocument和XPATH均未成功获取目标数据，询问是否应尝试XML Reader，并寻求解决帮助。

原代码 #

public void Main()
{
    String xmlData = (string)Dts.Variables["User::vXmlResponse"].Value;
    XDocument xDoc = XDocument.Parse(xmlData);

    XNamespace soap = "http://schemas.xmlsoap.org/soap/envelope/";
    XNamespace abc = "https://Ies.aa.com/";

    var packages = xDoc.Descendants(soap + "Body").Descendants(abc + "Packages").Descendants(abc + "Package");

    String connectionString = "xxxxxxxxx";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        foreach (var package in packages)
        {
            String packageName = vessel.Element(abc + "PACKAGE_NM")?.Value.Trim();
            int packageImo = int.Parse(package.Element(abc + "PACKAGE_IMO")?.Value.Trim());
            DateTimeOffset gmtDateTime = DateTimeOffset.Parse(package.Element(abc + "GMT_DATE_TIME")?.Value.Trim());

            string query = "Insert command";

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@packageName", packageName);
                command.Parameters.AddWithValue("@packageIMO", packageIMO);
                command.Parameters.AddWithValue("@GMTDateTime", GMTDateTime);

                command.ExecuteNonQuery();
            }
        }
    }

    Dts.TaskResult = (int)ScriptResults.Success;
}

原始XML数据 #

<?xml version='1.0' encoding='utf-8'?> 
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
<soap:Header>
    <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" soap:mustUnderstand="1">
        <wsu:Timestamp xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" wsu:Id="Timestamp-189039"> 
            <wsu:Created>2024-08-29T18:31:59.2502</wsu:Created> 
            <wsu:Expires>2024-08-29T18:36:59.2502</wsu:Expires> 
        </wsu:Timestamp>
    </wsse:Security> 
</soap:Header> 
<soap:Body> 
<GetIntransitPackagesResponse xmlns="https://Ies.aa.com/"> 
<GetIntransitPackagesResult> 
<Packages xmlns=""> 
    <PACKAGE> 
        <PACKAGE_NM>SUB HALI</PACKAGE_NM> 
        <PACKAGE_IMO>886300</PACKAGE_IMO> 
        <GMT_DATE_TIME>2024-08-29T18:14:18-05:00</GMT_DATE_TIME>
     </PACKAGE> 
     <PACKAGE> 
         <PACKAGE_NM>KSAFKIS KLO</PACKAGE_NM> 
         <PACKAGE_IMO>112826</PACKAGE_IMO> 
         <GMT_DATE_TIME>2024-08-29T17:59:50-05:00</GMT_DATE_TIME> 
     </PACKAGE> 
 </Packages>
 </GetIntransitPackagesResult>
 </GetIntransitPackagesResponse>
 </soap:Body>
 </soap:Envelope>

问题分析与解决方案 #

核心问题 #

1. 代码拼写/变量错误：

   • Xdocument.Parse 应为 XDocument.Parse（大小写错误）
   • Descaendants 应为 Descendants（拼写错误）
   • vessel.Element 应为 package.Element（变量名误用）
   • command.parameters 应为 command.Parameters（大小写错误）
   • 变量名大小写不一致：packageIMO vs packageImo、GMTDateTime vs gmtDateTime

2. 命名空间匹配错误：
   XML中<Packages>标签设置了xmlns=""，意味着其内部的<PACKAGE>及子元素都属于无命名空间，而非你定义的abc命名空间，之前用abc + "PACKAGE_NM"查找元素会返回null。

修正后的代码 #

public void Main()
{
    string xmlData = (string)Dts.Variables["User::vXmlResponse"].Value;
    XDocument xDoc = XDocument.Parse(xmlData);

    XNamespace soap = "http://schemas.xmlsoap.org/soap/envelope/";
    XNamespace abc = "https://Ies.aa.com/";

    // 正确定位节点：soap:Body -> 命名空间下的响应节点 -> 结果节点 -> 无命名空间的Packages -> PACKAGE
    var packages = xDoc.Descendants(soap + "Body")
                       .Descendants(abc + "GetIntransitPackagesResponse")
                       .Descendants(abc + "GetIntransitPackagesResult")
                       .Descendants("Packages")
                       .Descendants("PACKAGE");

    string connectionString = "xxxxxxxxx";
    string insertQuery = @"INSERT INTO YourTableName (PackageName, PackageIMO, GMTDateTime)
                           VALUES (@packageName, @packageIMO, @GMTDateTime)";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        // 复用SqlCommand减少资源开销
        using (SqlCommand command = new SqlCommand(insertQuery, connection))
        {
            // 预定义参数类型，避免隐式转换问题
            command.Parameters.Add("@packageName", SqlDbType.NVarChar, 255);
            command.Parameters.Add("@packageIMO", SqlDbType.Int);
            command.Parameters.Add("@GMTDateTime", SqlDbType.DateTimeOffset);

            foreach (var package in packages)
            {
                // 安全获取值，避免空引用
                string packageName = package.Element("PACKAGE_NM")?.Value.Trim() ?? string.Empty;
                // 处理无效数字
                if (!int.TryParse(package.Element("PACKAGE_IMO")?.Value.Trim(), out int packageImo))
                {
                    continue; // 或根据业务逻辑处理无效数据
                }
                // 处理无效日期
                if (!DateTimeOffset.TryParse(package.Element("GMT_DATE_TIME")?.Value.Trim(), out DateTimeOffset gmtDateTime))
                {
                    continue;
                }

                // 赋值参数
                command.Parameters["@packageName"].Value = packageName;
                command.Parameters["@packageIMO"].Value = packageImo;
                command.Parameters["@GMTDateTime"].Value = gmtDateTime;

                command.ExecuteNonQuery();
            }
        }
    }

    Dts.TaskResult = (int)ScriptResults.Success;
}

额外说明 #

不需要切换到XML Reader，XDocument完全可以解决问题，关键是正确匹配命名空间和修正代码中的拼写/变量错误。另外建议：

• 加入TryParse类方法处理数据格式异常，避免任务因无效XML数据失败
• 复用SqlCommand和参数，提升批量插入的性能
• 使用强类型参数定义，避免AddWithValue可能带来的隐式转换问题

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