Home Dashboard Directory Help
Search

suppress namespace attributes in nested SELECT FOR XML statements by mmhaven67


Status: 

Active


111
0
Sign in
to vote
Type: Suggestion
ID: 265956
Opened: 3/29/2007 6:27:35 AM
Access Restriction: Public
2
Workaround(s)
view

Description

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" xmlns:rt="http://test.com/customer" Customer_id="1">
<rt:Name xmlns:rtc=xmlns:rtc="http://test.com/customer/types" 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" xmlns:rt="http://test.com/customer" Customer_id="1">
<rt:Name>Test Customer A</rt:Name>
</rt:Customer>
-------------------------------------------------------------------------
Details
Sign in to post a comment.
Posted by GregorM on 8/28/2013 at 10:34 PM
I have checked 2012 version and still no change :( We are sad!
Posted by SC_SQLDev on 1/23/2013 at 3:53 PM
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
Posted by Big Bay Boater on 3/7/2012 at 3:56 PM
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.
Posted by David Rueter on 7/27/2011 at 3:56 PM
+1 on requesting that you make this change to avoid unnecessary duplication of xmlns in child nodes.
Posted by Microsoft on 1/14/2011 at 3:55 PM
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
Posted by Microsoft on 1/14/2011 at 3:55 PM
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
Posted by ptyrodyx on 1/14/2011 at 8:33 AM
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 <xmlns> 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 <xmlns="">. Please reply atleast :(.
Posted by Sam Dela Cruz on 10/12/2009 at 4:42 PM
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!
Posted by lolemfr on 7/24/2009 at 9:32 AM
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 !
Posted by Glenn Groves on 6/4/2009 at 8:50 PM
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.')
Posted by Michael G on 1/29/2009 at 12:09 AM
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
Posted by malkie on 9/17/2008 at 8:20 AM
I also think this should be changed. It makes the XML very difficult to read!
Posted by JSG080 on 8/26/2008 at 8:55 AM
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.
Posted by Tobbox on 6/4/2007 at 1:43 PM
This would be very useful for our products as well.
Posted by Microsoft on 4/13/2007 at 1:31 PM
Dear Michael

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

Best regards
Michael
Sign in to post a workaround.
Posted by Arthur Olcot on 9/25/2012 at 1:25 AM
This may not be an option in every scenario, but for smaller xml structures with long namespaces maybe going over slow/expensive connections, this is an approach that I have taken in the past:

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

Posted by Thomas_Cournane on 10/11/2010 at 5:16 PM
http://www.nesqlserv.com/blog/2009/09/removing-redundant-namespaces-from-for.html (option 2) worked for me, kind of. It declares the xml namespace in the root fine but also an empty namespace declaration in the first child, but nowhere else. Currently it is failing validation for that reason