Peter Larsson Blog

Patron Saint of Lost Yaks

Some XML search approaches

by Peter Larsson in  , ,

I just played around with some different techniques to fetch relevant data from XML content.

 

DECLARE       @XMLString XML,
       @Search VARCHAR(50)
 
SELECT @XMLString = '
                           <Customers>
                                  <Customer>
                                         <FirstName>Kevin</FirstName>
                                         <LastName>Goff</LastName>
                                         <City type="aca">Camp Hill</City>
                                  </Customer>
                                  <Customer>
                                         <FirstName>Steve</FirstName>
                                         <LastName>Goff</LastName>
                                         <City type="acb">Philadelphia</City>
                                  </Customer>
                           </Customers>',
              @Search = 'Camp Hill'
 
-- Get all customers living in Camp Hill
SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,
       cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,
       cust.coldef.value('City[1]','VARCHAR(20)') AS City,
       cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM   @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE cust.coldef.exist('City/text()[.= sql:variable("@Search")]') = 1
 
-- Get all customers living in a City containing the text "adel"
SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,
       cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,
       cust.coldef.value('City[1]','VARCHAR(20)') AS City,
       cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM   @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE cust.coldef.exist('City [contains(.,"adel")]') = 1
 
-- Get all customers living in a City of type "acb"
SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,
       cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,
       cust.coldef.value('City[1]','VARCHAR(20)') AS City,
       cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM   @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE cust.coldef.exist('City[@type="acb"]') = 1
 
-- Get all customers living in a City with a Type containing an "c"
SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,
       cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,
       cust.coldef.value('City[1]','VARCHAR(20)') AS City,
       cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM   @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE cust.coldef.exist('City [contains(@type, "c")]') = 1
 

Legacy Comments
dineshrajan
2010-08-09		 re: Some XML search approaches
Nice
Tony Waite
2011-02-07		 re: Some XML search approaches
Thank you. Your examples were very helpful. Much respect!