FOR XML PATH and xsi:nil attributes

Question

Good morning all,

I have a large query utilising FOR XML PATH to output a .xml file.I have the main select which basically just represents the root ie

select *
from tbl
for xml path ('root'),elements xsinil

I then have subsequent nested selects within this main select i.e.

select 
    (
        select null [level1],
               '2'  [level2]
        from tbl
        for xml path('nested1'),type
    ),
    (
        select null [level1],
               '2'  [level2]
        from tbl
        for xml path('nested2'),type
    )
for xml path('root'),elements xsinil

However, the element xsinil argument placed on the for xml path does not have any affect on the contained subqueries i.e. the Level1 element is just a closed tag. I need this to display as xsi:nil="true".

I can achieve this through adding elements xsinil argument to the for xml path statement e.g.

for xml path('nested1'),type,elements xsinil

The problem with this is that the namespace declaration gets repeated at the subquery level.

I can find plenty of examples of using elements xsinil but none where it is to apply to a subquery without a repeated namesapce declaration.

To confirm, I am looking for the following output:

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <nested1>
    <level1 xsi:nil="true">
    <level2>2</level2>
  </nested1>
  <nested2>
    <level1 xsi:nil="true">
    <level2>2</level2>
  </nested2>
</root>

Hope you can help!

Answer 1

I don't think it is possible to prevent this behavior with subqueries using for xml path. There is a similar issue reported here. https://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements

You can get the output you want if you use for xml explicit instead.

declare @T table(Level1 int, Level2 int)
insert into @T values(null, 2)

select 1    as Tag,
       null as Parent,
       null as [root!1],
       null as [nested1!2!level1!ELEMENTXSINIL],
       null as [nested1!2!level2!ELEMENTXSINIL],
       null as [nested2!3!level1!ELEMENTXSINIL],
       null as [nested2!3!level2!ELEMENTXSINIL]
union all
select 2    as Tag,
       1    as Parent,
       null,
       Level1,
       Level2,
       null,
       null
from @T       
union all
select 3    as Tag,
       1    as Parent,
       null,
       null,
       null,
       Level1,
       Level2
from @T       
for xml explicit       

Result:

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <nested1>
    <level1 xsi:nil="true" />
    <level2>2</level2>
  </nested1>
  <nested2>
    <level1 xsi:nil="true" />
    <level2>2</level2>
  </nested2>
</root>