Xml attributes to Columns

  • 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:

    NameAgeAddress

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

    Mike Jones361020 Crystie Lane

  • 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)

  • 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