Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

suppress namespace attributes in nested SELECT FOR XML statements

When using the 'WITH XMLNAMESPACES' Construct, the namespace declarations show up in each nested query of a 'SELECT FOR XML' statement.
While it does produce a valid XML document, the results are not exactly pretty.

Example query with a subselect:
-------------------------------------------------------------------------
WITH XMLNAMESPACES('http://test.com/customer'; AS rt, 'http://test.com/customer/'; AS rtc)
SELECT Customer_id AS "@Customer_id"
, (SELECT sub.Name AS "rt:Name"
FROM Customer AS sub
WHERE sub.Customer_id = Customer.Customer_id
FOR XML PATH(''), type)
FROM Customer
FOR XML PATH('rt:Customer')
-------------------------------------------------------------------------
(the sub-query is meant as an illustration)
This query produces:
-------------------------------------------------------------------------
<rt:Customer xmlns:rtc="http://test.com/customer/types&quot; xmlns:rt="http://test.com/customer&quot; Customer_id="1">
<rt:Name xmlns:rtc=xmlns:rtc="http://test.com/customer/types&quot; xmlns:rt="http://test.com/customer">Test Customer A</rt:Name>
</rt:Customer>
-------------------------------------------------------------------------

The 'redefinition' of the 'rtc' and 'rt' namespace-prefixes is 'overkill' in the produced result, and makes the result hard-to-read as well.

The following result would be much better readable and also valid:
-------------------------------------------------------------------------

<rt:Customer xmlns:rtc="http://test.com/customer/types&quot; xmlns:rt="http://test.com/customer&quot; Customer_id="1">
<rt:Name>Test Customer A</rt:Name>
</rt:Customer>
-------------------------------------------------------------------------

4 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Upvotes: 178

<=-=Apr 13 2007 1:31PM=-=>

Dear Michael

Thanks for the feedback. We will be looking into your suggestion for a future release of SQL Server.

Best regards
Michael

<=-=Jun 4 2007 1:43PM=-=>

This would be very useful for our products as well.

<=-=Aug 26 2008 8:55AM=-=>

I just encountered this weird behavior. Although it is valid XML, it makes the resulting document extremely large as well as inconsistent. E.g., the intermediate elements created by the “AS” clause of the subquery don’t have the namespace declaration. I vote for a fix.

<=-=Sep 17 2008 8:20AM=-=>

I also think this should be changed. It makes the XML very difficult to read!

<=-=Jan 29 2009 12:09AM=-=>

The current function realy bloats the returned XML, we’ve used the “old-school” for xml expicit but the sql to construct that is realy messy.
We’ve used the xml functionality in SQL server for a long time and this is our no 1 request in this area.
The size of the XML files that needs to be transported through the network gets huge due to this extra xml, when we tried to change to the new for xml path style

<=-=Jun 4 2009 8:50PM=-=>

If Microsoft would make this change, that would be great! This is a problem for us, we can produce perfect, complex XML, exactly according to the requirement – until we add namespaces. Then because the namespace references are added all over the place our data received rejects the XML as invalid. (Well, they say they will, we have not actually tried). Please do make this change (or provide an extra FOR XML… type option that says ‘No namespace references on this path please.’)

<=-=Jul 24 2009 9:32AM=-=>

In order to clean-up the xml produced (and thus reduc its weight on a Web Service call), I will be obliged to pass throught an XSLT transform … a real hell!

Please provide us with clean XML !

<=-=Oct 12 2009 4:42PM=-=>

I’m processing and producing very large XML files. The extra namespaces add even more weight in the XML files produced. I would love to see this fixed. Thanks!

<=-=Jan 14 2011 8:33AM=-=>

This needs sorted ASAP please. Are you guys still looking at it (I don’t think so)? SQL Server is such a nice product and so is it’s XQuery support. But this is becoming pain in the neck. I have XML with 1000s of records. Having unwanted is unacceptable as I need to process 1000s of such files every few seconds. The work around fails to validate because of first child’s . Please reply atleast :(.

<=-=Jan 14 2011 3:55PM=-=>

H all… we are indeed still looking at it. We are currently identifying some connect items for which we may have some resources to work on and this is one that we consider. However, at this stage I cannot make any promises.

Please note that the result is semantically not incorrect but I understand that the many XML declarations can bloat the size of the XML.

Just to be clear, we are looking into removing the namespace declarations in nested FOR XML output if the namespace is declared in outer elements. We cannot remove the namespace declarations at the top-level elements.

Best regards
Michael

<=-=Jan 14 2011 3:55PM=-=>

H all… we are indeed still looking at it. We are currently identifying some connect items for which we may have some resources to work on and this is one that we consider. However, at this stage I cannot make any promises.

Please note that the result is semantically not incorrect but I understand that the many XML declarations can bloat the size of the XML.

Just to be clear, we are looking into removing the namespace declarations in nested FOR XML output if the namespace is declared in outer elements. We cannot remove the namespace declarations at the top-level elements.

Best regards
Michael

<=-=Jul 27 2011 3:56PM=-=>

+1 on requesting that you make this change to avoid unnecessary duplication of xmlns in child nodes.

<=-=Mar 7 2012 3:56PM=-=>

Our concern is not so much ‘prettiness’ as it is the ability to meet the specifications and constraints of third parties.
They expect the root level namespace to control the entire document.
From a design standpoint, that should be the default.
If a change is required in a subsection then a namesapce for that piece would make sense.

<=-=Jan 23 2013 3:53PM=-=>

Hi, It’s nearly a year since the last comment so I thought I’d check if this made it into SQL 2012 or if it will be in an upcoming service pack?

Thanks

<=-=Aug 28 2013 10:34PM=-=>

I have checked 2012 version and still no change :( We are sad!

<=-=Jan 21 2016 2:47AM=-=>

nothing new yet? Please fix that Microsoft, a lot of Interfaces validations fails because of this ‘overkill’ (for example SAP)

<=-=Jul 19 2016 6:13PM=-=>

I’m using SQL Server 2016 RTM release, and sadly it’s still the same problem. Microsoft, you haven’t responded since 2011…please say something.

<=-=Sep 13 2016 4:55AM=-=>

Any official explanation? The issue persists till today!

<=-=Aug 20 2017 12:06PM=-=>

Happy birthday to this issue! I’m celebrating 10(!!!!) years of coming here just to find – nothing. Hey SQL-Server-Team, is there anybody out there? Anybode listening? Time to check this….

1 comment

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base