Post reply

Get NEsted XML into variable

  • July 6, 2010 at 7:21 am

    #387279

    declare @xml_complete xml, @xml_child_element xml

    set @xml_complete = '<U id = "1">

    <N id = "2"/>

    </U>'

    i want to set @xml_child_element variable to be equal to the nested element '<N id = "2"/>'

    i know how to get the node values but i just need the nested xml.

    Thanks for all help.

    Jules,

    www.sql-library.com[/url]

  • July 6, 2010 at 3:21 pm

    #1189356

    How about

    SELECT @xml_child_element = @xml_complete.query('/U/N[1]')


    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]

  • July 7, 2010 at 12:36 am

    #1189440

    great that works. thanks very much.

    Just one more thing.

    Is there a way i can rename the element in t-sql.

    So update <N id = "2"/> to be <P id = "2"/>

    Thanks again,

    Jules

    www.sql-library.com[/url]

  • July 7, 2010 at 11:41 am

    #1189770

    There might be an easier way to do it but here's a solution that first extract the value and assign "P" as an table alias and "id" as the column name and change it back to the original xml format using FOR XML AUTO.

    SELECT c.value('@id[1]','varchar(30)') AS id

    FROM @xml_complete.nodes('U/N') P(c)

    FOR XML AUTO


    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]

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

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