CODEWITHSUNDEEP
sql-serverxmlsoap
LBerg
LBerg

Reputation: 33

Querying xml in SQL Server with Soap envelope

I have found many examples for handling soap namespaces in an xml field, but none have helped me with my case:

I am trying to query a single element in the xml example below. (I truncated the example so you won't see the closing elements, but it is properly formed.)

Here is the xml:

<soapenv:Body xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope"     xmlns:wsa="http://www.w3.org/2005/08/addressing" xmlns:tns="http://www.example.org/TransactionDataSchema" xmlns:saml="urn:oasis:names:tc:SAML:2.0:assertion" xmlns:wsse11="http://docs.oasis-open.org/wss/oasis-wss-wssecurity-secext-1.1.xsd" xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<PRPA_IN201305UV02 xmlns="urn:hl7-org:v3" ITSVersion="XML_1.0">
<id extension="123abc-12a3-123a-a1bc-0a0a0a0a0a0a" root="1.23.456.7.890123.4.5678.901.2.3.4" />
<creationTime value="20150602175238" />
<interactionId extension="PRPA_IN201305UV02" root="1.23.456.7.890123.4.5" />
<processingCode code="P" />
<processingModeCode code="T" />
<acceptAckCode code="AL" />
<receiver typeCode="RCV">
  <device classCode="DEV" determinerCode="INSTANCE">
    <id root="1.23.456.7.890123.4.5678.001.1" />
    <telecom value="https://stage.mysite.org:1111/myevent" />
etc …

Here is the query I started with (Request_Body is the xml field in the table), though I believe the issue has something to do with the xml namespace - I'm just not sure what:

SELECT
Request_Body.value
('(/soapenv:Body/PRPA_IN201305UV02[@xmlns="urn:hl7-org:v3"]/receiver/device/id/telecom)[1]', 'varchar(100)' )
FROM [SW_Reporting].[dbo].[Client_Request]

Here is the error i got with my original query above:

Msg 2229, Level 16, State 1, Line 19
XQuery [SW_Reporting.dbo.Client_Request.Request_Body.value()]: The name "soapenv" does not denote a namespace.

I've tried using With xmlnamespaces and I got the select to return the whole xml using just '/' by itself. And I got everything after the second element (<PRPA_IN201305UV02), too, using a couple of namespace prefixes:

WITH xmlnamespaces (
'http://www.w3.org/2003/05/soap-envelope' as soapenv
,'urn:hl7-org:v3' as ns1)

But I can't get anything deeper than that. I am able to get a NULL value in some cases, so at least my syntax is accepted, but I know there is data to retrieve.

Here's my latest query, which isn't working either (after dumping the xml into a table variable):

declare @table table (data xml);
insert into @table values (@xml);

WITH xmlnamespaces (
'http://www.w3.org/2003/05/soap-envelope' as soapenv   
,'urn:hl7-org:v3' as ns1)
SELECT Data.query
('/soapenv:Body/ns1:PRPA_IN201305UV02/creationTime') as test
FROM @Table ;

I have queried xml before, but not with all the soap stuff. I appreciate any help/suggestions. Thanks in advance

Upvotes: 1

Views: 3360

Answers (1)

har07
har07

Reputation: 89295

What you missed is the fact that descendant elements inherit ancestor default namespace implicitly, unless otherwise specified. That means, in this particular XML, all elements without prefix are in default namespace which uri is "urn:hl7-org:v3", and you need to use corresponding prefix to reference those elements in your XPath/XQuery :

WITH xmlnamespaces (
'http://www.w3.org/2003/05/soap-envelope' as soapenv   
,'urn:hl7-org:v3' as ns1)
SELECT Data.query
('/soapenv:Body/ns1:PRPA_IN201305UV02/ns1:creationTime') as test
FROM @Table ;                       --^^^
                                    --Notice `ns1` prefix added here

Upvotes: 2

Related Questions