在SQL Sever中提取包含多个命名空间和不同属性的soap xml标记值

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soap:Body>
    <ns:Response xmlns:ns="http://example.com/response">
      <ns:Status>Success</ns:Status>
      <ns:Data>
        <ns:Item id="1">Item 1</ns:Item>
        <ns:Item id="2">Item 2</ns:Item>
      </ns:Data>
    </ns:Response>
  </soap:Body>
</soap:Envelope>

DECLARE @xml XML = N'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soap:Body>
    <ns:Response xmlns:ns="http://example.com/response">
      <ns:Status>Success</ns:Status>
      <ns:Data>
        <ns:Item id="1">Item 1</ns:Item>
        <ns:Item id="2">Item 2</ns:Item>
      </ns:Data>
    </ns:Response>
  </soap:Body>
</soap:Envelope>';

-- 提取Status值
SELECT @xml.value('(/soap:Envelope/soap:Body/ns:Response/ns:Status)[1]', 'NVARCHAR(50)') AS Status;

-- 提取Item值
SELECT 
    T.Item.value('@id', 'INT') AS ItemID,
    T.Item.value('.', 'NVARCHAR(50)') AS ItemValue
FROM 
    @xml.nodes('/soap:Envelope/soap:Body/ns:Response/ns:Data/ns:Item') AS T(Item);