Iterate Xml Column elements and comapre against Xml Variable

Question

Hi, I'm not sure how to word this correctly but I'm very (very) new to XML in Sql Server.

I have an XML Column defined in a table and I want to retrieve the id of the record if the data in the Xml Column matches elements in a predefined list.

The data looks a bit like this:

<Parameters>
    <Parameter>
        <Name>Param1</Name>
        <Value>Value1</Value>
    </Parameter>
    <Parameter>
        <Name>Param2</Name>
        <Value>Value2</Value>
    </Parameter>
</Parameter>

What I would like to do is check whether a similar list of parameters and values matches that of the Xml column. I can see you cannot do Xml comparison in Sql Server.

I can do it for a single parameter:

select * from table where
parameters.value('(/Parameters/Parameter/Name)[1]', 'varchar(50)') = 'Param1'
and 
parameters.value('(/Parameters/Parameter/Value)[1]', 'varchar(50)') = 'Value1'

But I want something that will cope with any number of parameters.

Answer 1

You can project your XML into columns using the .nodes() operator and then compare the projected columns. This is usually done with a CROSS APPLY, like this (typing from memory):

SELECT x.value('(Name)[1]', 'varchar(50)') as Name
   , x.value('(Value)[1]', 'varchar(50)') as Value
   from Table
   CROSS APPLY parameters.nodes('/Parameters/Parameter') AS t(x);

You can use this SELECT for instance in a CTE:

WITH shredded_xml AS (
SELECT Table.ID
   , x.value('(Name)[1]', 'varchar(50)') as Name
   , x.value('(Value)[1]', 'varchar(50)') as Value
   from Table
   CROSS APPLY parameters.nodes('/Parameters/Parameter') AS t(x))
SELECT * 
   FROM shredded_xml 
   WHERE Name = 'Param1'
    AND Value = 'Value1';

Answer 2

I myself am fairly new to SQL XML, so there's probably a better way than this, but it seems elegant enough:

-- Set up some sample data

CREATE TABLE Data (
    Id int
    , Attributes xml
)

-- Number 1 is red and small
INSERT Data
VALUES ( 1, '
<Parameters>
    <Parameter>
     <Name>Color</Name>
     <Value>Red</Value>
    </Parameter>
    <Parameter>
     <Name>Size</Name>
     <Value>Small</Value>
    </Parameter>
</Parameters>' )

-- Number 2 is blue and large
INSERT Data
VALUES ( 2, '
<Parameters>
    <Parameter>
     <Name>Color</Name>
     <Value>Blue</Value>
    </Parameter>
    <Parameter>
     <Name>Size</Name>
     <Value>Large</Value>
    </Parameter>
</Parameters>' )

-- Number 3 is Large
INSERT Data
VALUES ( 3, '
<Parameters>
    <Parameter>
     <Name>Size</Name>
     <Value>Large</Value>
    </Parameter>
</Parameters>' )



-- Search for large ones
DECLARE @searchCriteriaXml xml
SET @searchCriteriaXml = '<Parameters>
    <Parameter>
     <Name>Size</Name>
     <Value>Large</Value>
    </Parameter>
</Parameters>'

/*
-- Or for large blue ones:
SET @searchCriteriaXml = '<Parameters>
    <Parameter>
     <Name>Size</Name>
     <Value>Large</Value>
    </Parameter>
    <Parameter>
     <Name>Color</Name>
     <Value>Blue</Value>
    </Parameter>
</Parameters>'
*/

-- *************************************
-- Here begins the search process

-- Shred the search criteria into a rowset
DECLARE @searchCriteria TABLE (
    Name nvarchar(100)
    , Value nvarchar(100)
)

INSERT INTO
    @searchCriteria
SELECT DISTINCT
    P.value('Name[1]', 'nvarchar(100)')
    , P.value('Value[1]', 'nvarchar(100)')
FROM
    @searchCriteriaXml.nodes('/Parameters/Parameter') SC(P)

-- Debug:
-- SELECT * FROM @searchCriteria

-- To find matching items, we want to shred each
-- item's xml, INNER JOIN against the search criteria,
-- and return those Ids that matched exactly as many rows 
-- as there are in the criteria

SELECT
    Id
FROM
    (
    SELECT
     Data.Id
     , P.value('Name[1]', 'nvarchar(100)') ParameterName
     , P.value('Value[1]', 'nvarchar(100)') ParameterValue
    FROM
     Data
     CROSS APPLY Attributes.nodes('/Parameters/Parameter') D(P)
    ) D -- the shredded data
    INNER JOIN @searchCriteria SC 
     ON D.ParameterName = SC.Name
     AND D.ParameterValue = SC.Value
GROUP BY Id
HAVING COUNT(*) = (SELECT COUNT(*) FROM @searchCriteria)



DROP TABLE Data

In fact I suppose, thinking about it, there's no special reason to explicitly pull the search criteria into that table variable - we would just as well only shred it in the join operation itself:

SELECT
    Id
FROM
    (
    SELECT
     Data.Id
     , P.value('Name[1]', 'nvarchar(100)') ParameterName
     , P.value('Value[1]', 'nvarchar(100)') ParameterValue
    FROM
     Data
     CROSS APPLY Attributes.nodes('/Parameters/Parameter') D(P)
    ) D -- the shredded data
    INNER JOIN 
    (
    SELECT DISTINCT
     P.value('Name[1]', 'nvarchar(100)') Name
     , P.value('Value[1]', 'nvarchar(100)') Value
    FROM
     @searchCriteriaXml.nodes('/Parameters/Parameter') SC(P)
    ) SC -- the shredded search criteria
     ON D.ParameterName = SC.Name
     AND D.ParameterValue = SC.Value
GROUP BY Id
HAVING COUNT(*) = @searchCriteriaXml.value('count(/Parameters/Parameter)', 'int')

although off the top of my head I can't think of a nice way to count distinct parameters right at the end there. You might be able to trust your search criteria sufficiently to think this unnecessary.