Parse XML via SQL query 1

[ElEye]

I'm stymied how to parse some XML.

Specifically, I seek to parse the following XML:

DECLARE @XMLText xml = '<result>
<record>
<field name="usid" id="usid" value="9876" />
<field name="namel" id="namel" value="Jon" />
<field name="namef" id="namef" value="Anderson" />
</record>
<record>
<field name="usid" id="usid" value="4567" />
<field name="namel" id="namel" value="Steve" />
<field name="namef" id="namef" value="Howe" />
</record>
<record>
<field name="usid" id="usid" value="5775" />
<field name="namel" id="namel" value="Chris" />
<field name="namef" id="namef" value="Squire" />
</record>
</result>'


Into this result:

9876, Jon, Anderson
4567, Steve, Howe
5775, Chris, Squire


Dave
[

http://flashspot.com

]

 

[gmmastros]

Sorry for the late reply.

DECLARE @XMLText xml 

Set @XMLText = '<result>
<record>
<field name="usid" id="usid" value="9876" />
<field name="namel" id="namel" value="Jon" />
<field name="namef" id="namef" value="Anderson" />
</record>
<record>
<field name="usid" id="usid" value="4567" />
<field name="namel" id="namel" value="Steve" />
<field name="namef" id="namef" value="Howe" />
</record>
<record>
<field name="usid" id="usid" value="5775" />
<field name="namel" id="namel" value="Chris" />
<field name="namef" id="namef" value="Squire" />
</record>
</result>'

Select  X.Y.value('(field[@name="usid"]/@value)[1]', 'varchar(100)') As UserId,
        X.Y.value('(field[@name="namel"]/@value)[1]', 'varchar(100)') As LastName,
        X.Y.value('(field[@name="namef"]/@value)[1]', 'varchar(100)') As FirstName
From	@XMLText.nodes('result/record') As X(Y)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom