Some times you might need to retrieve the XML element at a given position. It may be because you are running a loop over all the elements of the XML document to perform some application specific operations or you want to pass those elements to another application/stored-procedure/function etc to perform some custom processing. It could also be that you need to access each element individually and do some actions.
The following example demonstrates how to retrieve the child element of an XML document, at the specified position.
-- XML instanceDECLARE @x1 XMLSELECT @x1 = '<Employees Dept="IT"> <Employee Number="1001" Name="Jacob"/> <Employee Number="1002" Name="Bob" ReportsTo="Steve"/></Employees>'SELECT @x1.query('/Employees/Employee[1]')/*OUTPUT:<Employee Number="1001" Name="Jacob" />*/SELECT @x1.query('/Employees/Employee[2]')/*OUTPUT:<Employee Number="1002" Name="Bob" ReportsTo="Steve" />*/
The following example shows how to use a variable to specify the position of the element needed.
-- XML instanceDECLARE @x1 XMLSELECT @x1 = '<Employees Dept="IT"> <Employee Number="1001" Name="Jacob"/> <Employee Number="1002" Name="Bob" ReportsTo="Steve"/></Employees>'DECLARE @i INTSELECT @i = 2SELECT @x1.query('/Employees/Employee[sql:variable("@i")]')/*OUTPUT:<Employee Number="1002" Name="Bob" ReportsTo="Steve" />*/SELECT @x1.query('/Employees/Employee[position()=sql:variable("@i")]')/*OUTPUT:<Employee Number="1002" Name="Bob" ReportsTo="Steve" />*/
Tags: XQuery-Labs, XML, XQuery, XQuery Functions, SQL Server XQuery, XQuery in TSQL, XQuery Training, XQuery Tutorial,