Delete empty XML nodes using T-SQL FOR XML PATH

for-xml-path, sql-server, t-sql, xml

Solution

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')

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')

Problem

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> ``` 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. 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] ``` I can think of two ways to construct this XML with `FOR XML PATH`. 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) 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> ``` 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. How do I construct my `FOR XML PATH` query in such a way that the `InnerContainerElement` is left out whenever it's empty?

Original source