FOR XML PATH和xsi:nil属性

问题描述

早上好，

我有一个大型查询，使用FOR XML PATH输出一个.xml文件.我有一个主选择，基本上只代表了根，即

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

但是，放置在for xml路径上的元素xsinil参数对所包含的子查询没有任何影响，即Level1元素只是一个封闭标记.我需要将其显示为xsi:nil ="true".

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".

我可以通过将元素xsinil参数添加到for xml路径语句中来实现此目的.

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.

我可以找到许多使用元素xsinil的示例，但是没有一个例子可以应用到没有重复的nameapce声明的子查询中.

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>

希望您能提供帮助！

推荐答案

我认为使用for xml path的子查询无法防止这种行为.这里报告了类似的问题. https://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements

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

如果使用for xml explicit，则可以获得所需的输出.

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

结果:

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

这篇关于FOR XML PATH和xsi:nil属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！