Delete Empty Xml Nodes Using T-sql For Xml Path

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:

Solution 1:

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 Tfor xml path('OuterElement'), root('Root')

Share