使用T-SQL FOR XML PATH删除空的XML节点 [英] Delete empty XML nodes using T-SQL FOR XML PATH

问题描述

我正在使用FOR XML PATH从SQL Server 2008R2的表中构造XML. XML必须构造如下:

I'm using FOR XML PATH to construct XML out of a table in SQL Server 2008R2. The XML has to be constructed as follows:

<Root>
    <OuterElement>
        <NumberNode>1</NumberNode>
        <FormattedNumberNode>0001</KFormattedNumberNode>
        <InnerContainerElement>
            <InnerNodeOne>0240</InnerNodeOne>
            <InnerNodeStartDate>201201</InnerNodeStartDate>
        </InnerContainerElement>
    </OuterElement>
</Root>

根据模式文件，InnerContainerElement是可选的，而InnerNodeOne是必需的.模式文件不是由我设置的，非常复杂，相互引用并且没有显式的XSD命名空间，因此我无法轻松地将它们加载到数据库中.

According to the schema files, the InnerContainerElement is optional, while the InnerNodeOne is required. The schema files aren't set up by me, are quite complex, referring each other and not having explicit XSD-namespaces, so I can't easily load them into the database.

必须从一个表中创建XML，该表使用以下查询来填充:

The XML has to be created from a table, which is filled using the following query:

SELECT
    1 AS NumberNode
    , '0001' AS [FormattedNumberNode]
    , '0240' AS [InnerNodeOne]
    , '201201' AS [InnerNodeStartDate]
INTO #temporaryXMLStore
UNION
SELECT
    2 AS NumberNode
    , '0001' AS [FormattedNumberNode]
    , NULL AS [InnerNodeOne]
    , NULL AS [InnerNodeStartDate]

我可以想到两种使用FOR XML PATH构造XML的方法.

I can think of two ways to construct this XML with FOR XML PATH.

1)使用"InnerContainerElement"作为XML子查询的命名结果:

1) Using 'InnerContainerElement' as named result from an XML subquery:

SELECT
    NumberNode
    , [FormattedNumberNode]
    , (
        SELECT
            [InnerNodeOne]
            , [InnerNodeStartDate]
        FOR XML PATH(''), TYPE
    ) AS [InnerContainerElement]
FROM #temporaryXMLStore
FOR XML PATH('OuterElement'), ROOT('Root') TYPE

2)使用'InnerContainerElement'作为XML子查询的输出元素，但不命名它:

2) Using 'InnerContainerElement' as an output element from an XML subquery, but without naming it:

SELECT
    NumberNode
    , [FormattedNumberNode]
    , (
        SELECT
            [InnerNodeOne]
            , [InnerNodeStartDate]
        FOR XML PATH('InnerContainerElement'), TYPE
    )
FROM #temporaryXMLStore
FOR XML PATH('OuterElement'), ROOT('Root'), TYPE

但是，它们都没有给出期望的结果:在两种情况下，结果都看起来像

However, none of them gives the desired result: in both cases, the result looks like

<Root>
  <OuterElement>
    <NumberNode>1</NumberNode>
    <FormattedNumberNode>0001</FormattedNumberNode>
    <InnerContainerElement>
      <InnerNodeOne>0240</InnerNodeOne>
      <InnerNodeStartDate>201201</InnerNodeStartDate>
    </InnerContainerElement>
  </OuterElement>
  <OuterElement>
    <NumberNode>2</NumberNode>
    <FormattedNumberNode>0001</FormattedNumberNode>
    <InnerContainerElement></InnerContainerElement>
    <!-- Or, when using the second codeblock: <InnerContainerElement /> -->
  </OuterElement>
</Root>

只要InnerContainerElement为空，它仍然显示为空元素.根据模式，这是无效的:只要元素InnerContainerElement位于XML中，就也需要InnerNodeOne.

Whenever InnerContainerElement is empty, it is still displayed as an empty element. This is invalid according to the schema: whenever the element InnerContainerElement is in the XML, InnerNodeOne is required too.

我该如何构造FOR XML PATH查询，以使InnerContainerElement在空的时候都被排除在外?

How do I construct my FOR XML PATH query in such a way that the InnerContainerElement is left out whenever it's empty?

推荐答案

对于没有内容的情况，您需要确保InnerContainerElement的行为零.

You need to make sure that the InnerContainerElement has zero rows for the case when there is no content.

select T.NumberNode,
       T.FormattedNumberNode,
       (
         select T.InnerNodeOne,
                T.InnerNodeStartDate
         where T.InnerNodeOne is not null or
               T.InnerNodeStartDate is not null
         for xml path('InnerContainerElement'), type
       )
from #temporaryXMLStore as T
for xml path('OuterElement'), root('Root')

或者您可以将元素InnerContainerElement指定为列别名的一部分.

Or you could specify the element InnerContainerElement as a part of a column alias.

select T.NumberNode,
       T.FormattedNumberNode,
       T.InnerNodeOne as 'InnerContainerElement/InnerNodeOne',
       T.InnerNodeStartDate as 'InnerContainerElement/InnerNodeStartDate'
from #temporaryXMLStore as T
for xml path('OuterElement'), root('Root')

这篇关于使用T-SQL FOR XML PATH删除空的XML节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！