Select from XML data in SS2012 1

[ElEye]

I'm missing something simple, I'm sure. I can't seem to find a way to do a simple SELECT statement to get this:

fn1 fn2
129 DA
129 LV
129 NY
291 NJ
302 AL
302 FA
302 GD


from this:

<result>
<record>
<field name="fn1" id="fn1" value="129"/>
<field name="fn2" id="fn2" value="DA"/>
</record>
<record>
<field name="fn1" id="fn1" value="129"/>
<field name="fn2" id="fn2" value="LV"/>
</record>
<record>
<field name="fn1" id="fn1" value="129"/>
<field name="fn2" id="fn2" value="NY"/>
</record>
<record>
<field name="fn1" id="fn1" value="291"/>
<field name="fn2" id="fn2" value="NJ"/>
</record>
<record>
<field name="fn1" id="fn1" value="302"/>
<field name="fn2" id="fn2" value="AL"/>
</record>
<record>
<field name="fn1" id="fn1" value="302"/>
<field name="fn2" id="fn2" value="FA"/>
</record>
<record>
<field name="fn1" id="fn1" value="302"/>
<field name="fn2" id="fn2" value="GD"/>
</record>
</result>



My thanks in advance.

Dave
[

http://flashspot.com

]

 

[gmmastros]

Here's one method:

declare @XML as xml

set @xml = '
<result>
	<record>
		<field name="fn1" id="fn1" value="129"/>
		<field name="fn2" id="fn2" value="DA"/>
	</record>
	<record>
		<field name="fn1" id="fn1" value="129"/>
		<field name="fn2" id="fn2" value="LV"/>
	</record>
	<record>
		<field name="fn1" id="fn1" value="129"/>
		<field name="fn2" id="fn2" value="NY"/>
	</record>
	<record>
		<field name="fn1" id="fn1" value="291"/>
		<field name="fn2" id="fn2" value="NJ"/>
	</record>
	<record>
		<field name="fn1" id="fn1" value="302"/>
		<field name="fn2" id="fn2" value="AL"/>
	</record>
	<record>
		<field name="fn1" id="fn1" value="302"/>
		<field name="fn2" id="fn2" value="FA"/>
	</record>
	<record>
		<field name="fn1" id="fn1" value="302"/>
		<field name="fn2" id="fn2" value="GD"/>
	</record>
</result>
'

Select Data.field.value('(field[1])/@value', 'varchar(20)') As fn1,
       Data.field.value('(field[2]/@value)', 'varchar(20)') As fn1
From   @XML.nodes('result/record') As Data(field)

-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

 

[ElEye]

Beautiful! Thank you very much, George!

The "field[x]" is not an option I tried so I kept getting the correct number of records back, but all with null field values.

So thank you very much!

Dave
[

http://flashspot.com

]

 

[gmmastros]

Actually... this might be a little better.

Select Data.field.value('field[!][@name="fn1"][/!][1]/@value', 'varchar(20)') As fn1,
       Data.field.value('(field[!][@name="fn2"][/!][1]/@value)', 'varchar(20)') As fn1
From   @XML.nodes('result/record') As Data(field)

I think this is better because it doesn't rely on the positioning of the elements. This way, the fn1 value will always be in the fn1 column and the fn2 value will always be in the fn2 column.



-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

 

[ElEye]

And thank you again, George! In this case I'm safe with INT data type on fn1 and that works. I sure hope this helps someone else too.

Dave
[

http://flashspot.com

]