解析带嵌套命名空间前缀的XML并插入SQL Server表报错求助

需要从给定的SOAP XML中提取ID和Access节点的值，插入SQL Server表中。测试用的OPENXML查询报错：

XML parsing error: Reference to undeclared namespace prefix: 'a'.

XML样本：

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Body>
    <GetAccessForUser xmlns="http://www.abctesting.com/app/api/v1">
      <GetAccessForUserResult xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
        <a:KeyValue>
          <a:Key xmlns:b="http://schemas.datacontract.org/2004/07/API.Model">
            <b:ID>111222</b:ID>
          </a:Key>
          <a:Value i:type="b:SecurityAccessDescriptorWithDirectRoles" xmlns:b="http://schemas.datacontract.org/2004/07/API.Model">
            <b:Access>Allow</b:Access>
          </a:Value>
        </a:KeyValue>
        <a:KeyValue>
          <a:Key xmlns:b="http://schemas.datacontract.org/2004/07/API.Model">
            <b:ID>333444</b:ID>
          </a:Key>
          <a:Value i:type="b:SecurityAccessDescriptorWithDirectRoles" xmlns:b="http://schemas.datacontract.org/2004/07/API.Model">
            <b:Access>Allow</b:Access>
          </a:Value>
        </a:KeyValue>
      </GetAccessForUserResult>
    </GetAccessForUser>
  </s:Body>
</s:Envelope>

原测试SQL：

DECLARE @idoc INT
DECLARE @XML NVARCHAR(MAX) 

SET @XML = N'<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
      <s:Body>
        <GetAccessForUser xmlns="http://www.abctesting.com/app/api/v1">
          <GetAccessForUserResult xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <a:KeyValue>
              <a:Key xmlns:b="http://schemas.datacontract.org/2004/07/API.Model">
                <b:ID>111222</b:ID>
              </a:Key>
              <a:Value i:type="b:SecurityAccessDescriptorWithDirectRoles" xmlns:b="http://schemas.datacontract.org/2004/07/API.Model">
                <b:Access>Allow</b:Access>
              </a:Value>
            </a:KeyValue>
            <a:KeyValue>
              <a:Key xmlns:b="http://schemas.datacontract.org/2004/07/API.Model">
                <b:ID>333444</b:ID>
              </a:Key>
              <a:Value i:type="b:SecurityAccessDescriptorWithDirectRoles" xmlns:b="http://schemas.datacontract.org/2004/07/API.Model">
                <b:Access>Allow</b:Access>
              </a:Value>
            </a:KeyValue>
          </GetAccessForUserResult>
        </GetAccessForUser>
      </s:Body>
    </s:Envelope>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @XML;

SELECT *
FROM OPENXML(@idoc, N'//s:Envelope/s:Body/GetAccessForUser/GetAccessForUserResult/a:KeyValue', 2)  
WITH ( 
        [IDNumber] [nvarchar](20) '/a:Key/b:ID', 
        [UserAccount] [nvarchar](50) 'David',
        [Permission] [nvarchar](10) '/a:Value/b:Access' 
    )
EXEC sp_xml_removedocument @idoc;

错误原因是sp_xml_preparedocument需要显式声明所有XPath中用到的命名空间前缀。需在调用该存储过程时，通过第三个参数传递命名空间映射字符串，将XML里的s、a、b前缀和对应的命名空间URI绑定。

修正后的SQL代码：

DECLARE @idoc INT
DECLARE @XML NVARCHAR(MAX) 
-- 定义命名空间映射，包含所有用到的前缀
DECLARE @ns NVARCHAR(MAX) = N'
<root xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"
      xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays"
      xmlns:b="http://schemas.datacontract.org/2004/07/API.Model"
      xmlns:default="http://www.abctesting.com/app/api/v1"/>
'

SET @XML = N'<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
      <s:Body>
        <GetAccessForUser xmlns="http://www.abctesting.com/app/api/v1">
          <GetAccessForUserResult xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <a:KeyValue>
              <a:Key xmlns:b="http://schemas.datacontract.org/2004/07/API.Model">
                <b:ID>111222</b:ID>
              </a:Key>
              <a:Value i:type="b:SecurityAccessDescriptorWithDirectRoles" xmlns:b="http://schemas.datacontract.org/2004/07/API.Model">
                <b:Access>Allow</b:Access>
              </a:Value>
            </a:KeyValue>
            <a:KeyValue>
              <a:Key xmlns:b="http://schemas.datacontract.org/2004/07/API.Model">
                <b:ID>333444</b:ID>
              </a:Key>
              <a:Value i:type="b:SecurityAccessDescriptorWithDirectRoles" xmlns:b="http://schemas.datacontract.org/2004/07/API.Model">
                <b:Access>Allow</b:Access>
              </a:Value>
            </a:KeyValue>
          </GetAccessForUserResult>
        </GetAccessForUser>
      </s:Body>
    </s:Envelope>'

-- 传递命名空间映射给sp_xml_preparedocument
EXEC sp_xml_preparedocument @idoc OUTPUT, @XML, @ns;

SELECT *
FROM OPENXML(@idoc, N'//s:Envelope/s:Body/default:GetAccessForUser/default:GetAccessForUserResult/a:KeyValue', 2)  
WITH ( 
        [IDNumber] [nvarchar](20) 'a:Key/b:ID', 
        [UserAccount] [nvarchar](50) '''David''', -- 常量值需用单引号转义包裹
        [Permission] [nvarchar](10) 'a:Value/b:Access' 
    )
EXEC sp_xml_removedocument @idoc;

关键说明 #

1. 命名空间声明：通过@ns变量定义所有用到的前缀，包括默认命名空间（GetAccessForUser节点属于默认命名空间，用default前缀绑定）。
2. XPath修正：默认命名空间下的节点必须用绑定的前缀（default:）访问，否则无法匹配节点。
3. 常量字段处理：UserAccount的常量值需要用双单引号包裹（'''David'''），避免被当作XPath表达式解析。

若要执行插入操作，只需将SELECT *替换为INSERT INTO 目标表名(IDNumber, UserAccount, Permission) SELECT ...即可。

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