This blog post is continued here http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx
/*******************************************************************************
Prepare script with testcase
*******************************************************************************/
DECLARE @XMLString XML,
@ElementEqual VARCHAR(50),
@ElementLike VARCHAR(50),
@AttributeEqual VARCHAR(50),
@AttributeLike 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>',
@ElementEqual = 'Camp Hill',
@ElementLike = 'adel',
@AttributeEqual = 'acb',
@AttributeLike = 'c'
/*******************************************************************************
Search for City-nodes
*******************************************************************************/
-- Get all City where element is equal to "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()[. = "Camp Hill"]') = 1
-- Get all City where element is equal to @ElementEqual
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("@ElementEqual")]') = 1
-- Get all City where element like "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 City where element like @ElementLike
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(. , sql:variable("@ElementLike"))]') = 1
/*******************************************************************************
Search for Type-attribute in City-nodes
*******************************************************************************/
-- Get all City where attribute is equal to "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 City where attribute is equal to @AttributeEqual
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 = sql:variable("@AttributeEqual")]') = 1
--Get all City where attribute like "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
--
Get all
City where attribute like @AttributeLike
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, sql:variable("@AttributeLike"))]') = 1