When using FOR XML AUTO in SQL Server: how to remove empty elements from nested elements when LEFT OUTER JOIN'ing

Question

[edit]: added RAW test and best code example

I need to create XML for an obsolete application running under SQL Server 2005, and I hope to use the function FOR XML.

However, I cannot get rid of empty elements when the outer join returns null.

I have created some test data below.

As a result, I get (AUTO):

<n id="1" Name="test1">
  <s SubName="sub1" />
  <s SubName="sub2" />
</n>
<n id="2" Name="test2">
  <s />
</n>

As a result, I get (RAW):

<row id="1" Name="test1" SubName="sub1" />
<row id="1" Name="test1" SubName="sub2" />
<row id="2" Name="test2" />

As a result, I want:

<n id="1" Name="test1">
  <s SubName="sub1" />
  <s SubName="sub2" />
</n>
<n id="2" Name="test2" />

Test code:

IF OBJECT_ID('tempdb..#name') IS NOT NULL BEGIN DROP TABLE #name END
SELECT * INTO #name FROM (
SELECT 1 id, 'test1' Name UNION ALL
SELECT 2 id, 'test2' Name )t

IF OBJECT_ID('tempdb..#sub') IS NOT NULL BEGIN DROP TABLE #sub END
SELECT * INTO #sub FROM (
SELECT 1 id, 'sub1' SubName UNION ALL
SELECT 1 id, 'sub2' SubName )t

SELECT n.id
     , n.Name
     , s.SubName 
FROM #name n
LEFT OUTER JOIN #sub s ON s.id = n.id
FOR XML AUTO

SELECT n.id
     , n.Name
     , s.SubName
FROM #name n
LEFT OUTER JOIN #sub s ON s.id = n.id
FOR XML RAW

Answer 1

It seems that the subqueries are combined with PATH or RAW - this is a simple answer. Both can generate the desired result:

PATH:

SELECT n.id "@id"
     , n.Name "@Name"
     , (SELECT s.SubName "@SubName"
        FROM #sub s WHERE s.id = n.id
        FOR XML PATH('s'), TYPE
        ) 
FROM #name n
FOR XML PATH('n')

RAW:

SELECT n.id 
     , n.Name 
     , (SELECT s.SubName 
        FROM #sub s WHERE s.id = n.id
        FOR XML RAW('s'), TYPE
        ) 
FROM #name n
FOR XML RAW('n')

Answer 2

@Kesse, check XML RAW

SELECT n.id
     , n.Name
     , s.SubName FROM #name n
LEFT OUTER JOIN #sub s ON s.id = n.id
FOR XML RAW