USE xmldb
GO

DROP TABLE xml_tab
GO

CREATE TABLE xml_tab(
  the_id INTEGER PRIMARY KEY IDENTITY,
  xml_col XML)
GO

INSERT xml_tab VALUES('<people><person><name>curly</name></person></people>')
INSERT xml_tab VALUES('<people><person><name>larry</name></person></people>')
INSERT xml_tab VALUES('<people><person><name>moe</name></person></people>')

INSERT xml_tab VALUES(
 '<people>
    <person><name>curly</name></person>
    <person><name>larry</name></person>
    <person><name>moe</name></person>
    <person><name>moe</name></person>
  </people>')
GO

SELECT xml_col.query('
   for $b in //person 
   where  $b/name="moe"
   return <li>{ $b/name/text() } in record number      
          {sql:column("xml_tab.the_id")}</li>
 ') 
 FROM xml_tab
GO

DECLARE @occupation VARCHAR(50)
SET @occupation = ' is a stooge'
SELECT xml_col.query('
   for $b in //person 
   where  $b/name="moe"
   return <li>{ $b/name/text() } { sql:variable("@occupation") }</li>
 ') 
 FROM xml_tab
