XML Conversion using Cross Apply

Question

Post reply

XML Conversion using Cross Apply

Simon G

Ten Centuries

Points: 1285
More actions
August 4, 2010 at 3:12 am

#224977

Hi all,
I need a nudge in the right direction here. I have a SOAP response containing a load of regions and I want to convert it to table format using t-sql. Can you help?
<?xml version="1.0" encoding="UTF-8" ?>
- <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
- <soap:Body>
- <showRegionTreeResponse xmlns="http://www.mywebsite.com">
- <s-gensym3>
<warnings xsi:nil="true" />
<errors xsi:nil="true" />
- <regionTree>
- <country soapenc:arrayType="xsd:anyType[69]" xsi:type="soapenc:Array">
- <item>
- <region soapenc:arrayType="xsd:anyType[152]" xsi:type="soapenc:Array">
- <item>
<isMetaRegion xsi:type="xsd:int">0</isMetaRegion>
<name xsi:type="xsd:string">Cambridgeshire</name>
<id xsi:type="xsd:int">17</id>
</item>
- <item>
<isMetaRegion xsi:type="xsd:int">0</isMetaRegion>
<name xsi:type="xsd:string">Suffolk</name>
<id xsi:type="xsd:int">117</id>
</item>
</region>
<name xsi:type="xsd:string">United Kingdom</name>
<id xsi:type="xsd:int">219</id>
</item>
</country>
</regionTree>
</s-gensym3>
</showRegionTreeResponse>
</soap:Body>
</soap:Envelope>
I want to extract all the region name and id's.
//showRegionTreeResponse/s-gensym3/regionTree/country/item/region/item
I have some sql using cross apply but I'm getting nowt back. I think it must be something to do with the namespace but I'm not sure.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

What have you tried so far?

Please post your current solution.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Simon G Ten Centuries Points: 1285 More actions · Answer 2

SELECT node.l.value('id[1]','NVARCHAR(10)'),

node.l.value('name[1]','NVARCHAR(255)')

FROM @tab CROSS APPLY xml_feed.nodes('//*/region/item') node(l)

WHERE NOT node.l.value('name[1]','NVARCHAR(255)') IS NULL;

LutzM SSC Guru Points: 107049 More actions · Answer 3

You need to add the namespace declaration, especially the DEFAULT declaration:

;WITH XMLNAMESPACES

('http://www.w3.org/2001/XMLSchema-instance' as xsi, -- not required for this specific query

'http://schemas.xmlsoap.org/soap/encoding/' as soapenc, -- not required for this specific query

'http://www.w3.org/2001/XMLSchema' as xsd, -- not required for this specific query

'http://schemas.xmlsoap.org/soap/envelope/' as [soap], -- not required for this specific query

DEFAULT 'http://www.mywebsite.com' -- required

)

SELECT id, node.l.value('id[1]','NVARCHAR(10)'),

node.l.value('name[1]','NVARCHAR(255)')

FROM @tab CROSS APPLY xml_feed.nodes('//region/item') node(l)

WHERE node.l.value('name[1]','NVARCHAR(255)') IS NOT NULL;

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Simon G Ten Centuries Points: 1285 More actions · Answer 4

Cheers Lutz,

I knew it was the namespace thing. Just gotta get my head around the markup. Cheers for your help!

Very much appreciated.