Post reply

Xml attributes to Columns

  • October 8, 2009 at 3:02 pm

    #136481

    I am trying to figure out how I can get _KEY to be the columns and _VALUE to be the row. I am assuming I can use CROSS APPLY but I am not sure on the syntax.

    Tim

    Declare @Xml xml

    SET @Xml = '<MYDATA>

    <FIELD _KEY="Name" _VALUE="Mike Jones" />

    <FIELD _KEY="Age" _VALUE="36" />

    <FIELD _KEY="Address" _VALUE="1020 Crystie Lane" />

    </MYDATA>'

    SELECT c.value('@_VALUE[1]', 'VARCHAR(100)')

    FROM @Xml.nodes('MYDATA/FIELD') T(C)

    Desired Results:

    Name Age Address

    ------------ -------- -------------------

    Mike Jones 36 1020 Crystie Lane

  • October 8, 2009 at 3:41 pm

    #1063780

    SELECT

    c.value('FIELD[@_KEY="Name"][1]/@_VALUE', 'VARCHAR(100)') as Name,

    c.value('FIELD[@_KEY="Age"][1]/@_VALUE', 'VARCHAR(100)') as Age,

    c.value('FIELD[@_KEY="Address"][1]/@_VALUE', 'VARCHAR(100)') as Address

    FROM @Xml.nodes('MYDATA') T(C)

    Tal Ben Yosef
    www.TalBenYosef.com
    (visit my LinkedIn profile)

  • October 8, 2009 at 3:58 pm

    #1063785

    This is what I needed. I appreciate your help.

    Thank you,

    Tim

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

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