Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Some XML search approaches

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
 

Print | posted on Wednesday, March 26, 2008 11:17 AM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Feedback

Gravatar

# re: Some XML search approaches

Nice
8/9/2010 10:01 AM | dineshrajan
Gravatar

# re: Some XML search approaches

Thank you. Your examples were very helpful. Much respect!
2/7/2011 11:57 AM | Tony Waite
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET