'SOAP XML parser 0 rows/ NULL

I am trying to parse Message out of the following xml. Despite the numerous similar answers, nothing seems to work.

declare @x xml

select @x=N'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <SubmitDocumentsResponse xmlns="http://somesite/webservices/">
      <SubmitDocumentsResult>
        <Code>000</Code>
        <Message>Success.</Message>
        <ItemsCreated>
          <ServiceItemCreated>
            <Id>2816</Id>
            <Protocol>ΑΚ - ΠΠ6</Protocol>
            <SubmitDate>28/03/2022 16:05</SubmitDate>
          </ServiceItemCreated>
        </ItemsCreated>
      </SubmitDocumentsResult>
    </SubmitDocumentsResponse>
  </soap:Body>
</soap:Envelope>';
select @x

select r.value('Message[1]','nvarchar(max)') as [Parsed Result]
from @x.nodes('/*:Envelope/*:Body/*:SubmitDocumentsResponse/*:SubmitDocumentsResult') as t(r)

This returns one row with NULL value. Other things I've tried:

r.value('Message[1]','nvarchar(max)') -- same error
r.value('(Message)[1]','nvarchar(max)') -- same error
r.value('(@Message)[1]','nvarchar(max)') -- same error
r.value('Message','nvarchar(max)') -- error,requires a singleton
@x.nodes('/*:Envelope/*:Body/SubmitDocumentsResponse/SubmitDocumentsResult') -- yields 0 rows
@x.nodes('//SubmitDocumentsResult') -- yields 0 rows

This also yields 0 rows:

;WITH XMLNAMESPACES ('http://schemas.xmlsoap.org/soap/envelope/' AS soap)
select r.value('Message[1]','nvarchar(max)') as [Parsed Result]
from @x.nodes('/soap:Envelope/soap:Body/*SubmitDocumentsResponse/*:SubmitDocumentsResult') as t(r)

How do I parse the attribute? What am I missing?

sql-serverxmltsql


Solution 1:[1]

WITH XMLNAMESPACES ('http://schemas.xmlsoap.org/soap/envelope/' AS soap,
                    'http://somesite/webservices/' as ns2)
select 
   r.value('ns2:Code[1]','char(3)') as [Code],
   r.value('ns2:Message[1]','nvarchar(20)') as [Parsed Result],
   r.value('ns2:ItemsCreated[1]/ns2:ServiceItemCreated[1]/ns2:Id[1]','int') as Id
from @x.nodes('/soap:Envelope/soap:Body/ns2:SubmitDocumentsResponse/ns2:SubmitDocumentsResult') as t(r)

output:

Code Parsed Result Id
000 Success. 2816

Docs:

Solution 2:[2]

You are not using namespaces, you need to add these in to the XQuery. the easiest way to do this is with a XMLNAMESPACES block. Using * is very slow.

You can even make one of the namespaces the default:

WITH XMLNAMESPACES (
    'http://schemas.xmlsoap.org/soap/envelope/' AS soap,
    DEFAULT 'http://somesite/webservices/'
)
select 
   r.value('(Code/text())[1]','char(3)') as [Code],
   r.value('(Message/text())[1]','nvarchar(20)') as [Parsed Result],
   r.value('(ItemsCreated/ServiceItemCreated/Id/text())[1]','int') as Id
from @x.nodes('/soap:Envelope/soap:Body/SubmitDocumentsResponse/SubmitDocumentsResult') as t(r)

db<>fiddle

Note that using text() to get the inner text of a node is slightly faster.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Luuk
Solution 2 Charlieface

Related Questions