Geek Answers Handbook

For XML attributes PATH and xsi: nil

Good morning,

I have a big query using FOR XML PATH to output an XML file. I have a basic choice that basically just represents the root, i.e.

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

Then I have the following nested selections in this main selection ie

 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 argument of the xsinil element placed in the for xml path does not affect the contained subqueries, that is, the Level1 element is only a private tag. I need this to display as xsi: nil = "true".

I can achieve this by adding xsinil arguments to the xsinil argument, for example:

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

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

I can find many examples of using xsinil elements, but not where it should apply to a subquery without re-declaring namesapce.

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!

+4
source share
1 answer

I do not think that this behavior can be prevented with subqueries using for xml path . There is a similar problem here. https://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements

You can get the desired result 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>
+3
source

More articles:


All Articles