删除 SQL Server 中不同级别的空 XML 标记

2021-10-02 00:00:00 xml sql xquery sql-server xml.modify

我使用 FOR XML EXPLICIT 来联合将一些 SQL 表信息转换为 XML 文件.一切正常，但我在结果中有很多不同级别的空标签.我想删除所有空标签，但保留每个组的顶级.这是我的意思的一个例子:

I am using FOR XML EXPLICIT to union to convert some SQL table info into an XML file. It's all working, but I have loads of empty tags in the results, at all sorts of different levels. I would like to remove all the empty tags, but keep the top level for each group. Here's an example of the sort of thing I mean:

使用这个无意义的 XML 示例，我可以使用 .modify xquery 删除底层空节点:

Using this example bit of nonsense XML, I can remove the bottom level empty nodes with a .modify xquery:

DECLARE @XML XML = 
'<Whatever>
  <GlassesTypes>
      <GlassesType />
  </GlassesTypes>
  <ExpressionOfJoy>
      <FellOver>Y</FellOver>
  </ExpressionOfJoy>
  <Flights>
    <Flight>
      <Bookings>
        <Booking>
          <Segments>
            <Segment />
          </Segments>
        </Booking>
      </Bookings>
    </Flight>
  </Flights>
</Whatever>'

SELECT @XML as Before

SET  @Xml.modify('delete //*[not(node())]');

SELECT @XML AS After

这完全符合我对GlassesTypes"的要求，但Flights"中仍然存在一些级别的空节点.我可以为每个级别一遍又一遍地重复相同的修改命令，直到只显示航班"，但这样做会删除眼镜类型"空占位符:

This has done exactly what I want with 'GlassesTypes', but there are still levels of empty nodes in 'Flights'. I could repeat the same modify command over and over for each level to get up to the point where only 'Flights' is displayed, but doing so will delete the 'GlassesTypes' empty placeholder:

DECLARE @XML XML = 
'<Whatever>
  <GlassesTypes>
      <GlassesType />
  </GlassesTypes>
  <ExpressionOfJoy>
      <FellOver>Y</FellOver>
  </ExpressionOfJoy>
  <Flights>
    <Flight>
      <Bookings>
        <Booking>
          <Segments>
            <Segment />
          </Segments>
        </Booking>
      </Bookings>
    </Flight>
  </Flights>
</Whatever>'

SELECT @XML as Before

SET  @Xml.modify('delete //*[not(node())]');
SET  @Xml.modify('delete //*[not(node())]');
SET  @Xml.modify('delete //*[not(node())]');
SET  @Xml.modify('delete //*[not(node())]');
SET  @Xml.modify('delete //*[not(node())]');

SELECT @XML AS After

有什么方法可以删除所有空节点，直到倒数第二个空节点，而不管一个组包含多少层?理想的结果是这样的:

Is there any way in which I can delete all empty nodes, until the penultimate empty node, regardless of how many levels a group contains? The ideal result would be this:

<Whatever>
  <GlassesTypes />
  <ExpressionOfJoy>
      <FellOver>Y</FellOver>
  </ExpressionOfJoy>
  <Flights />
</Whatever>

在这个例子中，只有Whatever"标签，但在真实数据中，可能有几个重复的，都包含不同级别的信息，包含在一个根标签或等效标签中.

In this example, there is only 'Whatever' tag, but in the real data, there might be several repeated, all with different levels of information, encompassed by a root tag or equivalent.

非常感谢任何帮助！

谢谢，标记

推荐答案

您可以将您的空"版本定义为不包含任何后代属性或文本节点，而不是不包含任何后代节点.然后通过仅选择其子项的后代来保留 的子项:

You can define your version of "empty" as not containing any descendant attribute or text nodes, instead of as not containing any descendant nodes. Then retain children of <Whatever> by only selecting descendants of its children:

/Whatever/*//*[empty(.//text() | .//attribute())]

相关文章