問題描述

我使用 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 刪除底層空節(jié)點:

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"中仍然存在一些級別的空節(jié)點.我可以為每個級別一遍又一遍地重復相同的修改命令，直到只顯示航班"，但這樣做會刪除眼鏡類型"空占位符:

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

有什么方法可以刪除所有空節(jié)點，直到倒數第二個空節(jié)點，而不管一個組包含多少層?理想的結果是這樣的:

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.

非常感謝任何幫助！

謝謝，標記

推薦答案

您可以將您的空"版本定義為不包含任何后代屬性或文本節(jié)點，而不是不包含任何后代節(jié)點.然后通過僅選擇其子項的后代來保留 的子項:

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())]

這篇關于刪除 SQL Server 中不同級別的空 XML 標記的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！