Query XML Data with T-SQL



CREATE TABLE dbo.T( X int, Y int)

INSERT INTO T VALUES (1, 20)
INSERT INTO T VALUES (3, 40)
INSERT INTO T VALUES (2, 30)
GO
  

declare @xml_table as table (col_xml xml );
insert into @xml_table(col_xml) values( (SELECT X, Y FROM T  
ORDER BY X  
--ORDER BY X DESC
FOR XML AUTO, Elements, Root('Root') ))
 
SELECT nums.num.value('(./X)[1]', 'int') AS X
,nums.num.value('(./Y)[1]','int') AS Y
FROM   @xml_table nt
CROSS apply nt.col_xml.nodes('/Root/T') AS nums(num) 
--Order By X DESC


drop table t

There is another easy to understand sample from Jon Tavernier:
http://blog.jontav.com/post/6811942997/querying-xml-in-sql-server

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s