Shredding XML nodes for the node names

Question

Post reply

Shredding XML nodes for the node names

sgmunson

SSC Guru

Points: 110640
More actions
October 8, 2009 at 1:50 pm

#134291

I have XML with almost no structure to it, save for the fact that there's a node name tag, value, and end-tag. All I want are the node names. I've tried several methods and nothing gets me what I'm looking for. My objective is a SELECT statement that will return the node names as a single column of values, so that I can join it together with other table or view data.
Here's what I have for xml:
<root>
<column_name>
<node_a>value1</node_a>
<node_b>value2</node_b>
</column_name>
</root>
Desired output:
COLUMN_NAME
node_a
node_b
I have tried the following:
SELECT C.value('column_name', 'varchar(30)') AS COLUMN_NAME
FROM @SRC_XML.nodes('root/column_name') AS X(C)
and many variations with no success. Anyone know exactly how I can make this work ?
Steve
(aka sgmunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Try this:

declare @XML XML;

select @XML = '<root>

<column_name>

<node_a>value1</node_a>

<node_b>value2</node_b>

</column_name>

</root>';

select x.y.value('local-name(.)', 'VARCHAR(50)'),

x.y.value('.', 'VARCHAR(50)')

from @XML.nodes('//*') x(y)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

LutzM SSC Guru Points: 107049 More actions · Answer 2

Would something like the following help?

DECLARE @xml xml

SET @xml='<root>

<column_name>

<node_a>value1</node_a>

<node_b>value2</node_b>

</column_name>

</root>'

SELECT

T.n.value('localname[1]', 'varchar(100)') AS Localname,

T.n.value('parent[1]', 'varchar(100)') AS Parent

FROM ( SELECT

x.query('

for $node in /root/descendant::node()[local-name() != ""]

return <node>

<localname>{ local-name($node) }</localname>

<parent>{ local-name($node/..) }</parent>

</node>') AS nodes

FROM @xml.nodes('/root/column_name') x(x)

) q1

CROSS APPLY q1.nodes.nodes('/node') AS T ( n )

-- EDIT: ORDER BY not required ORDER BY T.n.value('localname[1]', 'varchar(100)')

/* result

LocalnameParent

column_nameroot

node_acolumn_name

node_bcolumn_name */

Edit 2: GSquared's solution definitely is cleaner and most probably faster.

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]

sgmunson SSC Guru Points: 110640 More actions · Answer 3

GSquared,

This is perfect, thanks! While I was awaiting responses, I had found a way to use

OPENXML, and the query itself was very straightforward, but it required the use

of sp_xml_createdocument and sp_xml_removedocument, so this is much better.

When I get to work tomorrow, I'll have to post the OPENQUERY method that I had

found so that folks can compare, as I don't have it with me at the moment, and once

I got busy with the OPENXML solution and then almost immediately thereafter, yours;

I no longer had any time for the rest of the day. Thanks again!

Steve

(aka sgmunson)

:-):-):-)

GSquared (10/8/2009)
Try this:
declare @XML XML;
select @XML = '<root>
<column_name>
<node_a>value1</node_a>
<node_b>value2</node_b>
</column_name>
</root>';
select x.y.value('local-name(.)', 'VARCHAR(50)'),
x.y.value('.', 'VARCHAR(50)')
from @XML.nodes('//*') x(y)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 4

Just to update, when I got in to work this morning, I realized I had dumped that OPENXML code instead of holding on to it. Oh well. Anyway, here's the exact piece of code that I ended up using:

SELECT X.Y.value('local-name(.)', 'VARCHAR(30)') AS XML_COLUMN, X.Y.value('.', 'VARCHAR(80)') AS XML_DEFAULT_VALUE

FROM @FULL_XML.nodes('//*') X(Y)

However, because this code was the right side of a LEFT JOIN, I didn't see that the root and column_name nodes are actually output by this code, because those nodes aren't going to match up to the LEFT side of that join's join field. However, were I to need only the exact output I specified to begin with, but without the join, that's easily solved by changing the last line as follows:

FROM @FULL_XML.nodes('/root/column_name/*') X(Y)

Experimentation proved it works, at least as far as I tested anyway (I have 2008 Express at work and 2005 Developer at home, and didn't test it both ways in both places - it was a one way in one place and vice versa). Thanks again to GSquared!