Querying XML column in SQL Server 2005

Posted by Webmaster under Sql Server on 9/6/2008 | Views : 4973 | Status : [Administrator] | Replies : 1
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

Best regards,
Webmaster
http://www.dotnetfunda.com



Responses

Posted by: Deeraj on: 11/12/2008 [Member] Starter

Up
0
Down
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.

Webmaster, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response