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