How to get the name of XML elements

Question

Post reply

How to get the name of XML elements

luisr_cavazos

Grasshopper

Points: 16
More actions
December 29, 2010 at 4:14 pm

#95679

Hi All
I have a table with a xml data type, and I need to get each element name of the xml. I don't always know what XML elements will be present, but I need to place all of them in a SQL Server 2008 database.
Every row of the table may have different xml structure.
For example, i have the following xml:
<root>
<id>2983</id>
<Area>Urgencias</Area>
<Puesto>Enfermera General</Puesto>
<Motivo>Reposicion de personal</Motivo>
<Genero>F</Genero>
</root>
and I would like to place them into a table like this:
Element Value
---------- --------------------
id 2983
Area Urgencias
Puesto Enfermera General
Motivo Reposicion de personal
Genero F
I´ll apreciate your help
Happy new year

Viewing 3 posts - 1 through 3 (of 3 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Something like this?

DECLARE @xml XML

SET @xml=

'<root>

<id>2983</id>

<Area>Urgencias</Area>

<Puesto>Enfermera General</Puesto>

<Motivo>Reposicion de personal</Motivo>

<Genero>F</Genero>

</root>'

SELECT

T.c.value('localname[1]', 'varchar(100)') AS element, -- node name extracted from data column (holding NTFRS elements in xml format)

T.c.value('value[1]', 'varchar(100)') AS val -- node value extracted from data column

FROM

(SELECT

S.c.query('

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

return <node>

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

<value>{ $node }</value>

</node>') AS nodes

FROM @xml.nodes('.') S(c)

) subqry --returns content of intermdiate table with each xml node and value separated, but still in xml format

CROSS APPLY subqry.nodes.nodes('/node') AS T(c) -- anchor for extracting the values of the xml data

WHERE T.c.value('localname[1]', 'varchar(100)') <>'root'

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]

luisr_cavazos Grasshopper Points: 16 More actions · Answer 2

It works good, that´s exactly what I need!, Thanks a lot Lutz