CODEWITHSUNDEEP
sql-serverxmlsoap
Elarys
Elarys

Reputation: 669

Parsing SOAP XML in SQL Server

I'm trying to get the 'Response' text from a SOAP response in SQL Server but I cannot use soap:Envelope because of Parsing error's from SQL Server.

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

My XML Response is as follows and is contained in an nvarchar called @xmlOut:

<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>
  <Method1Response xmlns="http://tempuri.org/">
   <Method1Result>&lt;Interface&gt;&lt;Col1&gt;#result#&lt;/Col1&gt;&lt;Col2&gt;info&lt;/Col2&gt;&lt;Col3&gt;Record is invalid.&lt;/Col3&gt;&lt;Col4&gt;&lt;/Col4&gt;&lt;/Interface&gt;</Method1Result>
  </Method1Response>
 </soap:Body>
</soap:Envelope>

I am trying to get Method1Result into an nvarchar but I am really struggling with reading this XML.

declare @xDoc as xml
set @xDoc = cast(@xmlOut as xml)

declare @hdoc int
exec sp_xml_preparedocument @hdoc OUTPUT, @xDoc

select *
from 
(   select *
    from openxml(@hdoc, '/soap:Envelope/soap:Body/MethodResponse', 1) 
    with (MethodResult nvarchar(max)) 
) as x

exec sp_xml_removedocument @hdoc

This is how I normally read my XML variables in SQL, but as soon as I try to read soap:Envelope I get this error :

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

Upvotes: 2

Views: 9874

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

If you use SQL Server 2005 or later you can do this instead.

;with xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as [soap],
                    default 'http://tempuri.org/')
select T.N.value('.', 'nvarchar(max)')
from @xDoc.nodes('/soap:Envelope/soap:Body/Method1Response/Method1Result') as T(N)

Or a bit simpler/faster if you only expect value of Method1Result in the XML.

;with xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as [soap],
                    default 'http://tempuri.org/')
select @xDoc.value('(/soap:Envelope/soap:Body/Method1Response/Method1Result)[1]', 'nvarchar(max)')

Upvotes: 1

Related Questions