Read parameters for SQL store procedure from XML variable

Target audience: SQL programmers
Used tools: SQL,XPath
What’s the purpose: read parameters for SQL query from XML variable

Well – the problem is to count all records from a SQL table which satisfy a condition in WHERE clause – like this

WHERE table.field > = minVal AND table.field<=maxVal

minVal and maxVal are parameters. This is very easy for ‘resolving’ :

SELECT COUNT(table.field1) WHERE table.field >= minVal AND table.field<=maxVal

We have to choose values for minVal and maxVal from a table2 :

NAME	minVal	maxVal
name1	1000	1999
name2	2200	2999
name3	3000	3699

Then replace minVal and maxVal with their values in SELECT query and run this SELECT N – times where n is number of records in table2.
But such way of ‘resolving‘ is too boring. And here arise the problem: how to read the values of minVal and maxVal in a query from XML in a store procedure.
And here is a ‘resolving’:

-- XML instance
DECLARE @x1 XML
SELECT  @x1 =
 '<OPS>
    <nameOPS>name1</nameOPS>
    <MINpCode>1000</MINpCode>
    <MAXpCode>1999</MAXpCode>
  </OPS>
  <OPS>
    <nameOPS>name2</nameOPS>
    <MINpCode>2000</MINpCode>
    <MAXpCode>2299</MAXpCode>
  </OPS>'
DECLARE
    @cnt INT,@cnt2 INT,@nOPS CHAR(30),@minV INT,@maxV INT,  
    @totCnt INT,@brZap INT,@child XML
-- counter variables
SELECT @cnt = 1, @cnt2 = 1
SELECT @totCnt = @x1.value('count(/OPS)','INT')
PRINT 'OPS                              From   To     Count'
PRINT '----------------------------------------------------'
-- loop
WHILE @cnt <= @totCnt BEGIN
    SELECT @child = @x1.query('/OPS[position()=sql:variable("@cnt")]')
    
    SELECT @nOPS=code.item.value('(nameOPS)[1]', 'VARCHAR(30)')
    FROM @child.nodes('/OPS') AS code(item)
        
    SELECT @minV=code.item.value('(MINpCode)[1]', 'VARCHAR(4)')
    FROM @child.nodes('/OPS') AS code(item)
    
    SELECT @maxV=code.item.value('(MAXpCode)[1]', 'VARCHAR(4)')
    FROM @child.nodes('/OPS') AS code(item)
    
    SELECT @brZap=COUNT(nuk) FROM person
    WHERE cust_id>=@minV AND cust_id<=@maxV
        
    PRINT @nOPS+' '+ CAST(@minV AS CHAR(4))+' '+ CAST(@maxV AS CHAR(4))+' '+CAST(@brZap AS CHAR(8))
    SELECT @cnt = @cnt + 1
END

The result is:

OPS	From	To	Count
name1	1000	1999	157
name2	2000	2299	23

Here nuk is table.field1 – field1 is the name of the column on which the values are to be counted and cust_id is table.field .
NOTE /for explanation/ : we use XPath to fetch SQL XML value – you can read more about this in

http://stackoverflow.com/questions/1890923/xpath-to-fetch-sql-xml-value