t-sql: dynamically filter XML on multiple conditions?

Question

I'm trying to find a way to do a accept/reject on an XML string, by joining it to a table of conditions. I have one "filter" working now, but want to write it so that it can filter 2 or more.

Here's code that matches one of the two. If either matches, it will filter the string. What I want to do is make it so it has to match BOTH, while still leaving the option for single-condition

CREATE TABLE #filter (exclusion_type CHAR(1), excluded_value varchar(10))
INSERT INTO #filter VALUES ('B','boy')
INSERT INTO #filter VALUES ('C','cat')

DECLARE @data XML
SELECT @data = '<A><B>boy</B><C>cat</C></A>'
SELECT * FROM (SELECT CONVERT(VARCHAR(128),node.query('fn:local-name(.)')) AS NodeName, CONVERT(VARCHAR(MAX),node.query('./text()')) AS NodeValue
FROM @data.nodes(N'//*') T(node))xml_shred

IF NOT EXISTS 
(SELECT * FROM (SELECT CONVERT(VARCHAR(128),node.query('fn:local-name(.)')) AS NodeName, CONVERT(VARCHAR(MAX),node.query('./text()')) AS NodeValue
FROM @data.nodes(N'//*') T(node)) xml_shred
INNER JOIN #filter
ON   (nodename = exclusion_type AND nodevalue LIKE excluded_value)
)
select 'record would be inserted '
ELSE select 'record was filtered'

Here's how I currently have it to filter both. Ugly and non-expandable.

IF NOT EXISTS 
(SELECT * FROM (SELECT CONVERT(VARCHAR(128),node.query('fn:local-name(.)')) AS NodeName, CONVERT(VARCHAR(MAX),node.query('./text()')) AS NodeValue
FROM @data.nodes(N'//*') T(node)) xml_shred
INNER JOIN #filter
ON   (nodename = exclusion_type AND nodevalue LIKE excluded_value)
)
--combination filters don't easily work within that xml_shred
and not(
        @data.value('(/A/B)[1]', 'varchar(128)') = 'boy'
        AND 
        @data.value('(/A/C)[1]', 'varchar(128)')='cat'
        )

select 'record would be inserted '
ELSE select 'record was filtered'

My only other ideas:

some sort of GUID that would link records in the #filter table together, and then inner join on a GROUP BY of #filtertable, grouping by the GUID and using the SUM to match the number of records.
use semicolons to split the #filter rows, then use a CTE or something to fake a hierarchy and work from there.

Code changes made by Mikael's suggestion

CREATE TABLE #filter
    (
      exclusion_set SMALLINT,
      exclusion_type CHAR(1) ,
      excluded_value VARCHAR(10)
    )
INSERT  INTO #filter
VALUES  (1, 'B', 'boy')
INSERT  INTO #filter
VALUES  (1, 'C', 'cat')
INSERT  INTO #filter
VALUES  (2, 'D', 'dog' )

DECLARE @data XML
SELECT  @data = '<A><B>boy</B><C>cat</C></A>'
IF NOT EXISTS(
SELECT * FROM 
(
select COUNT(*) AS match_count, exclusion_set
              from #filter as F
              where exists (
                           select *
                           from (
                                select X.N.value('local-name(.)', 'varchar(128)') as     NodeName,
                                       X.N.value('./text()[1]', 'varchar(max)') as     NodeValue
                                from @data.nodes('//*') as X(N)
                                ) T
                           where T.NodeName = F.exclusion_type and
                                 T.NodeValue like F.excluded_value 
                           )
GROUP BY exclusion_set
) matches_per_set
INNER JOIN 
(SELECT COUNT(*) AS total_count, exclusion_set FROM #filter GROUP BY exclusion_set)     grouped_set
ON match_count = total_count
AND grouped_set.exclusion_set = matches_per_set.exclusion_set
)

Mikael Eriksson · Accepted Answer · 2012-09-07 06:05:22Z

3

if not exists (
              select *
              from #filter as F
              where exists (
                           select *
                           from (
                                select X.N.value('local-name(.)', 'varchar(128)') as NodeName,
                                       X.N.value('./text()[1]', 'varchar(max)') as NodeValue
                                from @data.nodes('//*') as X(N)
                                ) T
                           where T.NodeName = F.exclusion_type and
                                 T.NodeValue like F.excluded_value 
                           )
              having count(*) = (select count(*) from #filter)
              )
  select 'record would be inserted '
else
  select 'record was filtered'

answered Sep 7, 2012 at 6:05

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mbourgon Over a year ago

Right, but that only works if there's only ever matching records. The second I add a different set of conditions (by adding rows to #filter), it fails. But that code does tie in with my GUID idea...

mbourgon Over a year ago

Is there much difference in performance between the node.query that I'm doing and the X.N.value you're doing? I don't know XML querying enough to know. Guess I can do an A/B against mine.

Mikael Eriksson Over a year ago

How can you tell one set of filters from another set of filters?

mbourgon Over a year ago

The exclusion_set value. Using a smallint instead of a GUID for performance. I'm going to make it simple and say that it has to match all records in a "set". (and if you asked before my post with the new code - I did need a way but was unsure about the best way to do so; the GUID, aka exclusion_set, is my solution)

mbourgon Over a year ago

Oh, and I just tried your shred vs mine - slightly different behavior (A = null in yours, A= blank in mine), but yours is 38% query cost whereas mine is 62% - so that about a 25% performance boost.

mbourgon · Accepted Answer · 2012-09-17 17:52:06Z

Since I apparently get dinged if I don't mark something as the answer, I'm including mine from above. Many thanks for the help to Mikael Eriksson. His XML shred is faster than mine, and by adding the "exclusion_set" field (char(2) to make it obvious that it wasn't an IDENTITY or primary key), I can do multiple checks. If all conditions in a set match, then the record is filtered.

CREATE TABLE #filter
    (
      exclusion_set CHAR(2),
      exclusion_type CHAR(1) ,
      excluded_value VARCHAR(10)
    )
INSERT  INTO #filter
VALUES  ('aa', 'B', 'boy')
INSERT  INTO #filter
VALUES  ('aa', 'C', 'cat')
INSERT  INTO #filter
VALUES  ('ab', 'D', 'dog' )

DECLARE @data XML
SELECT  @data = '<A><B>boy</B><C>cat</C></A>'
IF NOT EXISTS(
SELECT * FROM 
(
select COUNT(*) AS match_count, exclusion_set
              from #filter as F
              where exists (
                           select *
                           from (
                                select X.N.value('local-name(.)', 'varchar(128)') as     NodeName,
                                       X.N.value('./text()[1]', 'varchar(max)') as     NodeValue
                                from @data.nodes('//*') as X(N)
                                ) T
                           where T.NodeName = F.exclusion_type and
                                 T.NodeValue like F.excluded_value 
                           )
GROUP BY exclusion_set
) matches_per_set
INNER JOIN 
(SELECT COUNT(*) AS total_count, exclusion_set FROM #filter GROUP BY exclusion_set)     grouped_set
ON match_count = total_count
AND grouped_set.exclusion_set = matches_per_set.exclusion_set


 )
select 'record would be inserted '
else
  select 'record was filtered'

Collectives™ on Stack Overflow

t-sql: dynamically filter XML on multiple conditions?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related