如何用SQL查询过滤XML中Junk值为true的节点？

我需要用SQL查询过滤XML中Junk值为true的节点，效果等价于常规SQL语句select * from main where junk=true。

待查询的XML内容 #

<Main>
  <Box>
    <Name>Box1</Name>
    <Junk>false</Junk>
  </Box>
  <Box>
    <Name>Box2</Name>
    <Junk>true</Junk>
  </Box>
  <Box>
    <Name>Box3</Name>
    <Junk>false</Junk>
  </Box>
  <Box>
    <Name>Box4</Name>
    <Junk>false</Junk>
  </Box>
  <Box>
    <Name>Box5</Name>
    <Junk>false</Junk>
  </Box>
  <Box>
    <Name>Box6</Name>
    <Junk>true</Junk>
  </Box>
  <Box>
    <Name>Box7</Name>
    <Junk>true</Junk>
  </Box>
  <Box>
    <Name>Box8</Name>
    <Junk>false</Junk>
  </Box>
</Main>

尝试的错误代码 #

declare @main xml;
declare @junk xml;

set @main = '';

set @junk = (select  @main WHERE @main.value('(/Main/Junk)[1]', 'nvarchar(10)')  = 'true');

问题分析 #

这段代码存在两个核心问题：

• @main.value('(/Main/Junk)[1]', 'nvarchar(10)')仅提取XML中第一个Junk节点的值，无法遍历所有Box下的Junk进行判断
• 直接返回整个@main变量，没有对Box节点做筛选逻辑

解决方案 #

方案1：返回包含符合条件Box的Main节点 #

生成新的Main节点，仅保留Junk值为true的Box节点，匹配第一种预期结果：

declare @main xml;
-- 赋值目标XML内容
set @main = N'
<Main>
  <Box>
    <Name>Box1</Name>
    <Junk>false</Junk>
  </Box>
  <Box>
    <Name>Box2</Name>
    <Junk>true</Junk>
  </Box>
  <Box>
    <Name>Box3</Name>
    <Junk>false</Junk>
  </Box>
  <Box>
    <Name>Box4</Name>
    <Junk>false</Junk>
  </Box>
  <Box>
    <Name>Box5</Name>
    <Junk>false</Junk>
  </Box>
  <Box>
    <Name>Box6</Name>
    <Junk>true</Junk>
  </Box>
  <Box>
    <Name>Box7</Name>
    <Junk>true</Junk>
  </Box>
  <Box>
    <Name>Box8</Name>
    <Junk>false</Junk>
  </Box>
</Main>';

select 
  @main.query('
    <Main>
      {
        /Main/Box[Junk/text()="true"]
      }
    </Main>
  ') as FilteredXml;

方案2：直接返回符合条件的Box节点 #

直接输出所有Junk值为true的Box节点，匹配第二种预期结果：

declare @main xml;
-- 赋值目标XML内容
set @main = N'
<Main>
  <Box>
    <Name>Box1</Name>
    <Junk>false</Junk>
  </Box>
  <Box>
    <Name>Box2</Name>
    <Junk>true</Junk>
  </Box>
  <Box>
    <Name>Box3</Name>
    <Junk>false</Junk>
  </Box>
  <Box>
    <Name>Box4</Name>
    <Junk>false</Junk>
  </Box>
  <Box>
    <Name>Box5</Name>
    <Junk>false</Junk>
  </Box>
  <Box>
    <Name>Box6</Name>
    <Junk>true</Junk>
  </Box>
  <Box>
    <Name>Box7</Name>
    <Junk>true</Junk>
  </Box>
  <Box>
    <Name>Box8</Name>
    <Junk>false</Junk>
  </Box>
</Main>';

select 
  Box.query('.') as FilteredBoxes
from @main.nodes('/Main/Box[Junk/text()="true"]') as T(Box);

内容的提问来源于stack exchange，提问作者bdfios