Parsing XML data and insert into table with nested namespace prefix

I have the following XML sample data. I would like to get the ID and Access node value and insert into a SQL Server table.

<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>

I wrote the SQL query shown here - for testing, I only select the result, I'm not inserting it yet.

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;

For some reason, I keep getting this error:

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

I couldn't figure out if I should adding XMLNAMESPACE to define different prefix or indicate root node.

Any help would be really appreciated.

Solution

Forget about all that old, legacy "OpenXML" stuff - it's legacy, it's buggy, it's leaking memory..... use the built-in XQuery support instead! And you'll need to define all relevant XML namespaces for your document.

Try something like this:

DECLARE @InputXML XML = '..(your XML here) ....';

WITH XMLNAMESPACES (DEFAULT 'http://www.abctesting.com/app/api/v1',
                    'http://schemas.microsoft.com/2003/10/Serialization/Arrays' AS a,
                    'http://schemas.datacontract.org/2004/07/API.Model' AS b,
                    'http://schemas.xmlsoap.org/soap/envelope/' AS s)
    SELECT 
        -- from the nodes selected by the XQuery expression in the "FROM" clause
        -- extract the "Key:ID" and "Value:Access" values 
        xmlval.value('(a:Key/b:ID/text())[1]', 'int') as ID,
        xmlval.value('(a:Value/b:Access/text())[1]', 'varchar(50)') AS Access
    FROM
        -- get the nodes down to the "KeyValue" node as the "root" for the other values
        @InputXML.nodes('/s:Envelope/s:Body/GetAccessForUser/GetAccessForUserResult/a:KeyValue') AS kv(xmlval)

This should result in an output something like this:

ID     | Access
-------+-------
111222 | Allow
333444 | Allow