Parsing XML into a table

Question

Post reply

Parsing XML into a table

Bill Maners

Ten Centuries

Points: 1340
More actions
November 1, 2010 at 8:51 am

#231066

I need to run a sql statement to produce
NAMEVALUE
addr120 Morris Ave
addr2NULL or ‘’
addr3NULL or ‘’
cityWahroonga
stateNew South Wales
zip2076
from this...
<?xml version="1.0" encoding="UTF-8"?>
<xml>
<target>
<tag_pairs>
<tag_pair><name>addr1</name><values><value>20 Morris Ave</value></values></tag_pair>
<tag_pair><name>addr2</name><values><value></value></values></tag_pair>
<tag_pair><name>addr3</name><values><value></value></values></tag_pair>
<tag_pair><name>city</name><values><value>Wahroonga</value></values></tag_pair>
<tag_pair><name>state</name><values><value>New South Wales</value></values></tag_pair>
<tag_pair><name>zip</name><values><value>2076</value></values></tag_pair>
</tag_pairs>
</target>
</xml>
My elementary efforts using the .nodes() and corss apply have been fruitless.
Thanks the help.
Bill

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

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

DECLARE@data XML ='<?xml version="1.0" encoding="UTF-8"?>

<xml>

<target>

<tag_pairs>

<tag_pair>

<name>addr1</name>

<values>

<value>20 Morris Ave</value>

</values>

</tag_pair>

<tag_pair><name>addr2</name><values><value></value></values></tag_pair>

<tag_pair><name>addr3</name><values><value></value></values></tag_pair>

<tag_pair><name>city</name><values><value>Wahroonga</value></values></tag_pair>

<tag_pair><name>state</name><values><value>New South Wales</value></values></tag_pair>

<tag_pair><name>zip</name><values><value>2076</value></values></tag_pair>

</tag_pairs>

</target>

</xml>'

SELECTn.value('../name[1]', 'VARCHAR(MAX)') AS Name,

n.value('value[1]', 'VARCHAR(MAX)') AS Value

FROM@data.nodes('/xml/target/tag_pairs/tag_pair/values') AS v(n)

N 56°04'39.16"
E 12°55'05.25"

Bill Maners Ten Centuries Points: 1340 More actions · Answer 2

Bill Maners

Ten Centuries

Points: 1340

November 1, 2010 at 9:23 am

#1244882

Thank you oh so much.

Bill

LutzM SSC Guru Points: 107049 More actions · Answer 3

Here's a slightly different approach using CROSS APPLY.

It usually is faster than the method Peso posted.

But you should try both and decide by yourself 😀

SELECT n.value('name[1]', 'VARCHAR(MAX)') AS Name,

c.value('value[1]', 'VARCHAR(MAX)') AS Value

FROM @data.nodes('/xml/target/tag_pairs/tag_pair') AS v(n)

CROSS APPLY

v.n.nodes('values') as T(c)

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]

Bill Maners Ten Centuries Points: 1340 More actions · Answer 4

Thank you, I 'filed' both methods.

I appreciate the help.