Thinking outside the box

Updated XML search (test case with variables)

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

Print | posted on Monday, September 01, 2008 2:29 PM | Filed Under [ SQL Server 2008 SQL Server 2005 ]