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
posted @ Wednesday, March 26, 2008 11:17 AM