t-sql：在多个条件下动态过滤XML？

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.

我试图找到一种方法来对XML字符串进行接受/拒绝,方法是将其连接到条件表。我现在有一个“过滤器”工作,但想写它以便它可以过滤2个或更多。

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.

某种GUID将#filter表中的记录链接在一起,然后在#filtertable的GROUP BY上进行内部连接,按GUID分组并使用SUM匹配记录数。

• use semicolons to split the #filter rows, then use a CTE or something to fake a hierarchy and work from there.

使用分号分割#filter行,然后使用CTE或其他东西伪造层次结构并从那里开始工作。

---

Code changes made by Mikael's suggestion

代码更改由Mikael提出

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
)

2 个解决方案

#1

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'

#2

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.

因为如果我没有将某些东西标记为答案,我显然会感到害怕,我会从上面包括我的。非常感谢Mikael Eriksson的帮助。他的XML碎片比我的快,并且通过添加“exclusion_set”字段(char(2)使其明显不是IDENTITY或主键),我可以进行多次检查。如果集合中的所有条件都匹配,则过滤记录。

---

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'