Thinking outside the box

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

My Links

Advertisement

News

Archives

Post Categories

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 ]

Feedback

Gravatar

# re: Updated XML search (test case with variables)

excellent post, Peter!
8/17/2009 1:51 PM | Jacob Sebastian
Gravatar

# re: Updated XML search (test case with variables)

Thank you Jacob!
4/11/2010 9:25 PM | Peso
Gravatar

# re: Updated XML search (test case with variables)

Great post. Helped me with my task.
One query though - can a query be scripted to check if the incoming XML itself exists directly.
In the above case - if XMLString iteself exists in the table.
Table
ID Details
1 '<Outer> <In> 2 </ In> </ Outer>'

If there is a request to add a new entry say
2 '<Outer> <In> 2 </ In> </ Outer>'

then an error should be thrown.
5/22/2011 11:34 AM | PM
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET