問(wèn)題描述

我使用 FOR XML EXPLICIT 來(lái)聯(lián)合將一些 SQL 表信息轉(zhuǎn)換為 XML 文件.一切正常，但我在結(jié)果中有很多不同級(jí)別的空標(biāo)簽.我想刪除所有空標(biāo)簽，但保留每個(gè)組的頂級(jí).這是我的意思的一個(gè)例子:

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:

使用這個(gè)無(wú)意義的 XML 示例，我可以使用 .modify xquery 刪除底層空節(jié)點(diǎn):

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

這完全符合我對(duì)GlassesTypes"的要求，但Flights"中仍然存在一些級(jí)別的空節(jié)點(diǎn).我可以為每個(gè)級(jí)別一遍又一遍地重復(fù)相同的修改命令，直到只顯示航班"，但這樣做會(huì)刪除眼鏡類(lèi)型"空占位符:

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é)點(diǎn)，直到倒數(shù)第二個(gè)空節(jié)點(diǎn)，而不管一個(gè)組包含多少層?理想的結(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>

在這個(gè)例子中，只有Whatever"標(biāo)簽，但在真實(shí)數(shù)據(jù)中，可能有幾個(gè)重復(fù)的，都包含不同級(jí)別的信息，包含在一個(gè)根標(biāo)簽或等效標(biāo)簽中.

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.

非常感謝任何幫助！

謝謝，標(biāo)記

推薦答案

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

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

這篇關(guān)于刪除 SQL Server 中不同級(jí)別的空 XML 標(biāo)記的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！