How can I use a WITH XMLNAMESPACES clause with correlated queries?
You're not alone.
I would suggest upvoting that improvement.
There is a work around listed at the connect site above:
APPEND/EDIT:
The second link I posted above is now dead.
Here is a different link:
Vilius Surblys
Technical architect for a multinational organisation with over 25 years of programming experience in languages as diverse as VB.Net, assembler, C++, Perl and C#
Updated on August 22, 2022Comments
-
Vilius Surblys almost 2 years
I'm trying to use the
FOR XML
feature of SQL Server to generate some XML, with some specific namespaces.My target XML should look something like:
<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>
The problem I have, is that when I use the
WITH XMLNAMESPACES
clause, is the namespace declaration appears on lots of the child nodes, which the third party consumer of this XML would "prefer not to get", i.e. I'm doing something like: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')
Which gives:
<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>
Is it possible to tweak the query to get the namespaces correct, or will I have to load this into "something else" to remove the redundant namespace declarations?