sql-server - 如何在相关查询中使用 WITH XMLNAMESPACES 子句？

Question

我正在尝试使用 FOR XML SQL Server 的特性来生成一些 XML，具有一些特定的命名空间。

我的目标 XML 应该类似于:

<ns1:CustomerInvoices xmlns:ns1="urn:example.com:Invoice:01.00">
  <CustomerInvoice>
    <Header>
      <OrderDate>2001-11-13T00:00:00</OrderDate>
      <SalesOrderNumber>SO44643</SalesOrderNumber>
    </Header>
    <Lines>
      <LineTotal>3578.270000</LineTotal>
      <UnitPrice>3578.2700</UnitPrice>
      <OrderQty>1</OrderQty>
    </Lines>
  </CustomerInvoice>
  <CustomerInvoice>...(abbreviated)...</CustomerInvoice>
</ns1:CustomerInvoices>

我遇到的问题是，当我使用 WITH XMLNAMESPACES 时子句，是命名空间声明出现在许多子节点上，这个 XML 的第三方消费者“不希望得到”，即我正在做类似的事情:

Use AdventureWorks2008

;WITH XMLNAMESPACES ('urn:example.com:Invoice:01.00' as ns1)

SELECT      (SELECT     hdr.OrderDate,
                        hdr.SalesOrderNumber
            FOR XML PATH (''), TYPE) AS Header,
            (SELECT     line.LineTotal,
                        line.UnitPrice,
                        line.OrderQty

            FROM        Sales.SalesOrderDetail  AS line

            WHERE       line.SalesOrderID = hdr.SalesOrderID
            FOR XML PATH (''), TYPE) AS Lines

FROM        Sales.SalesOrderHeader  AS hdr

WHERE       hdr.ModifiedDate = '2001-11-20'

FOR XML PATH('CustomerInvoice'), ROOT('ns1:CustomerInvoices') 

这使:

<ns1:CustomerInvoices xmlns:ns1="urn:example.com:Invoice:01.00">
  <CustomerInvoice>
    <Header>
      <OrderDate xmlns:ns1="urn:example.com:Invoice:01.00">2001-11-13T00:00:00</OrderDate>
      <SalesOrderNumber xmlns:ns1="urn:example.com:Invoice:01.00">SO44643</SalesOrderNumber>
    </Header>
    <Lines>
      <LineTotal xmlns:ns1="urn:example.com:Invoice:01.00">3578.270000</LineTotal>
      <UnitPrice xmlns:ns1="urn:example.com:Invoice:01.00">3578.2700</UnitPrice>
      <OrderQty xmlns:ns1="urn:example.com:Invoice:01.00">1</OrderQty>
    </Lines>
  </CustomerInvoice>
  <CustomerInvoice>...(abbreviated)...</CustomerInvoice>
</ns1:CustomerInvoices>

是否可以调整查询以获得正确的命名空间，或者我是否必须将其加载到“其他”中以删除冗余的命名空间声明？

Answer 1

你不是一个人。

http://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements

我建议赞成这种改进。

在上面的连接站点上列出了一个解决方法:

http://www.olcot.co.uk/sql-blogs/suppressing-namespace-attributes-in-nested-select-statements-when-using-for-xml-workaround

追加/编辑:

我在上面发布的第二个链接现在已经死了。

这是一个不同的链接:

http://www.sqlservercentral.com/blogs/rocks/2012/08/28/suppressing-namespace-attributes-in-nested-select-statements-when-using-for-xml-workaround/