Post reply

How to get the name of XML elements

  • December 29, 2010 at 5:14 pm

    #1267471

    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]

  • December 30, 2010 at 2:23 pm

    #1267952

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

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

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