October 8, 2009 at 3:02 pm
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
October 8, 2009 at 3:41 pm
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)
October 8, 2009 at 3:58 pm
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