November 1, 2010 at 8:51 am
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
November 1, 2010 at 9:06 am
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"
November 1, 2010 at 9:23 am
Thank you oh so much.
Bill
November 1, 2010 at 9:46 am
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)
November 2, 2010 at 7:15 am
Thank you, I 'filed' both methods.
I appreciate the help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply