Go to DotNetFunda.com
Home Articles Interview Tips Questions Jokes Resources ASP.NET AJAX Silverlight Links Blog
 Welcome, Guest!  
LoginLogin   		{ Submit content and get exposure !!! }
Submit: Article | Interview Question | Tips | Joke | Question | Link || Search  
 Skip Navigation Links Home > Questions > Querying XML column in SQL Server 2005

Microsoft Technologies Questions and Answers

Post Question | Search Questions |  Subscribe to RSS

Browse Questions for ASP.NET | ASP.NET 3.5 | C# | CSS | F# | JavaScript | Others | Sql Server | All |

Querying XML column in SQL Server 2005

Author: Webmaster Posted on: 9/6/2008 | Category: Sql Server |  Print |
I have a table which has a column of xml data type, used to store xml files. Now I want to query the table to show the records which has a value matching inside xml file. Explaination with an example would certainely help.

Thanks,
Suresh
____________________________
Posted on behalf of Skubsad

Post Answer



Interesting?  Bookmark and Share

 Answer(s)
Deeraj  
Posted on: 11/12/2008 6:13:37 AM 		Post Answer
Lets insert some sample data into a xml column of some arbitrary table XMLTable.
--------- Start of Insert into XMLTable ---------
Begin Tran
Declare @test xml
Declare @i int
Set @i=0
While (@i<10)
Begin
--Declare @i int
--Set @i=0
--Declare @test xml
set @test = '<Orders>
<Order>
<OrderId>'+ cast(@i as varchar) +'</OrderId>
<CustName>Name'+cast(@i as varchar)+'</CustName>
</Order>
</Orders>'
Select @Test
Insert into XMLTable values (@test)
Set @i=@i+1
End
Commit Tran
--------- End of Insert into XMLTable ---------
--truncate table XMLTable

--Queries the xml column and returns the value of OrderID tag alone.
SELECT xmldatacolumn.value('(/Orders/Order/OrderId)[1]',
'varchar(50)' )
FROM xmltable
/*
Sample output:
0
1
2
*/

--Selects all Ids from xmltable where OrderID tag equals to 1
Select id from xmltable where xmldatacolumn.value('(/Orders/Order/OrderId)[1]',
'varchar(50)' )='1'
/*
Sample output:
id
--
2
*/

--Queries OrderId tags along with the tag names
SELECT xmldatacolumn.query('(/Orders/Order/OrderId)')
FROM xmltable

/*
Sample output:
<OrderId>0</OrderId>
<OrderId>1</OrderId>
<OrderId>2</OrderId>
<OrderId>3</OrderId>
*/

--Queries OrderId tags along with the tag name where orderid tag value is 1
SELECT xmldatacolumn.query('(/Orders/Order/OrderId)')
FROM xmltable where xmldatacolumn.value('(/Orders/Order/OrderId)[1]',
'varchar(50)' )='1'

/*
Sample output:
<OrderId>1</OrderId>
*/

HTH
-- Dheeraj.
 



About Us | Contact Us | Privacy Policy | Terms of Use | Link Exchange | Members | Go Top
All rights reserved to DotNetFunda.com. Logos, company names used here if any are only for reference purposes and they may be respective owner's right or trademarks.
(Best viewed in IE 6.0+ or Firefox 2.0+ at 1024 * 768 or higher)