question

luiz.carvalho avatar image
luiz.carvalho asked

Querying complex XML nodes

Hello Everyone.

Need to extract data in XML nodes from a XML datatype in a table. SQLVersion 2012 SP4.

Here is the XML content. Need to query the texts MESSAGE 1, MESSAGE 2 and MESSAGE 3 below:

-------------------------------------------------------------------------

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:wss="http://www.adonix.com/WSS">;

<soapenv:Body>

<wss:modifyResponse soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">;

<modifyReturn xsi:type="wss:CAdxResultXml">

<messages xsi:type="soapenc:Array" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" soapenc:arrayType="wss:CAdxMessage[3]">

<messages href="#id0" />

<messages href="#id1" />

<messages href="#id2" />

</messages>

<resultXml xsi:type="xsd:string" xsi:nil="true" />

<status xsi:type="xsd:int">0</status>

<technicalInfos xsi:type="wss:CAdxTechnicalInfos">

<busy xsi:type="xsd:boolean">false</busy>

<changeLanguage xsi:type="xsd:boolean">false</changeLanguage>

<changeUserId xsi:type="xsd:boolean">false</changeUserId>

<flushAdx xsi:type="xsd:boolean">false</flushAdx>

<loadWebsDuration xsi:type="xsd:double">9</loadWebsDuration>

<nbDistributionCycle xsi:type="xsd:int">-1</nbDistributionCycle>

<poolDistribDuration xsi:type="xsd:double">0</poolDistribDuration>

<poolEntryIdx xsi:type="xsd:int">14152</poolEntryIdx>

<poolExecDuration xsi:type="xsd:double">1796</poolExecDuration>

<poolRequestDuration xsi:type="xsd:double">-1</poolRequestDuration>

<poolWaitDuration xsi:type="xsd:double">2</poolWaitDuration>

<processReport xsi:type="xsd:string" xsi:nil="true" />

<processReportSize xsi:type="xsd:int">-1</processReportSize>

<reloadWebs xsi:type="xsd:boolean">false</reloadWebs>

<resumitAfterDBOpen xsi:type="xsd:boolean">false</resumitAfterDBOpen>

<rowInDistribStack xsi:type="xsd:int" xsi:nil="true" />

<totalDuration xsi:type="xsd:double">1826</totalDuration>

<traceRequest xsi:type="xsd:string" />

<traceRequestSize xsi:type="xsd:int">0</traceRequestSize>

</technicalInfos>

</modifyReturn>

</wss:modifyResponse>

<multiRef id="id0" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="wss:CAdxMessage">

<type>1</type>

<message>MESSAGE 1</message>

</multiRef>

<multiRef id="id1" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="wss:CAdxMessage">

<type>3</type>

<message>MESSAGE 2</message>

</multiRef>

<multiRef id="id2" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="wss:CAdxMessage">

<type>1</type>

<message>MESSAGE 3</message>

</multiRef>

</soapenv:Body>

</soapenv:Envelope>

-------------------------------------------------------------------------

Any ideas?

xmlsql2012.net
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

Assuming the data is in a table called @YourTable and the column containing the XML is called SomeXML, then a query like this should work

select EnvelopeBody.multiref.query('.').value('/multiRef[1]/message[1]', 'varchar(max)')
from @YourTable
cross apply SomeXML.nodes('/*:Envelope/*:Body/*:multiRef') EnvelopeBody(multiref)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Great work @Kev Riley, worked perfectly as expected!

Thanks for your help!

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.