How to use parameter in XQuery?

Question

I'm trying to loop the XML value using @count variable, but I'm not able to understand how to use the parameter

create function sbill(@s xml) 
returns varchar(max)
as begin

DECLARE @SBILLNO VARCHAR(MAX),@count int
set @count =5
while @count <> 0
Begin
   select @SBILLNO= @s.value('(/Transaction/BodyData/TransBody/BillwiseData/BillAdjustment/sBillNo)[@count]','varchar(max)')
   set @count=@count-1
end
returns @SBILLNO
end

Answer 1

Try sql:variable https://learn.microsoft.com/en-us/sql/xquery/xquery-extension-functions-sql-variable?view=sql-server-2017

Demo

declare @x xml =
'<a>
  <b>123</b>
  <b>456</b>
</a>'
declare @count int = 2;

select t.n.value('(/a/b)[sql:variable("@count")][1]','int')
from @x.nodes('.') t(n);

Answer 2

Your own code looks, as if you want to get multiple repeated elements out of an XML. Therefore you are using a WHILE loop in order to read the first, the second, the nth value one after the other. Correct so far?

Assumably you are looking for this actually:

--create a mockup-table with two rows
DECLARE @tbl TABLE(ID INT IDENTITY, SomeXml XML);
INSERT INTO @tbl VALUES
 ('<a>
      <b>b11</b>
      <b>b12</b>
      <b>b13</b>
      <b>b14</b>
      <b>b15</b>
   </a>>')
,('<a>
      <b>b21</b>
      <b>b22</b>
      <b>b23</b>
    </a>');

--The query

SELECT b.value('text()[1]','varchar(10)')
FROM @tbl t
CROSS APPLY t.SomeXml.nodes('/a/b') A(b);

The .nodes() method will return each <b> in a single row as derived table (the table's name is A, the column's name is b. You can set both names as you like it. The returned column is of type XML and it is a relative fragment. The .value() method is used on this column to retrieve the content.

Some hints how to avoid loops

Anyway you should avoid loops whenever you can avoid them. Your own approach could be solved in a set-based manner with a tally-on-the-fly:

DECLARE @count INT=5;

WITH Tally(Nmbr) AS (SELECT TOP(@count) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values)
SELECT t.ID
      ,Nmbr
      ,t.SomeXml.value('(/a/b[sql:column("Nmbr")])[1]','varchar(10)') AS Numbered_B
FROM @tbl t
CROSS JOIN Tally;

This will use a list of 1 to 5 in order to read the XML elements by their position using sql:column(). The second row will return two NULL-values, as there is no forth or fifth element.

We could even use a dynamically created numbers list

But you can even create the tally-on-the-fly depending on the actual count of <b> elements like here:

SELECT t.ID
      ,Nmbr
      ,t.SomeXml.value('(/a/b[sql:column("Nmbr")])[1]','varchar(10)') AS Numbered_B
FROM @tbl t
CROSS APPLY (SELECT TOP(t.SomeXml.value('count(/a/b)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values) A(Nmbr);

This works like the query before, but will use a list of 1 to n, where n is found dynamically.

Answer 3

Few methods:
1. Use sql:variable():

select @SBILLNO= 
@s.value('(/Transaction/BodyData/TransBody/BillwiseData/BillAdjustment/sBillNo)[sql:variable("@count")]','varchar(max)')

2. For more complicated queries you may need to build dynamic XQuery using dynamic SQL.