Suppressing namespace attributes in nested select statements when using FOR XML workaround

I was helping out on the forums the other day with a user who had stumbled across the quirk of SQL putting in an excessive amount of namespace declarations when using nested select statements as part of a FOR XML statement. There is a connect item already open for this, but at the end of the day, the output from SQL is correct, it just bloats the xml. Attached to the connect item is a link to a workaround but it doesn’t work for me as it only solves the problem if none of the xml nodes or attributes actually reference the namespaces.

The workaround that I came up with on the forum is one that I have used once or twice in the past to overcome this quirk. It basically consists of a SQL statement that first generates the XML without any namespaces and then by using a flwor statement within XQuery, I remodel that XML to include the namespaces. The result is a perfectly formed XML structure that doesn’t have namespace declarations all over the place.

This was the example query provided in the forum thread: (This all works fine on AdventureWorks2008R2)

WITH XMLNAMESPACES ('uri1' AS ns1, DEFAULT 'uri2')

SELECT ProductModelID AS "@ns1:ProductModelID"

, Name AS "@ns1:ProductModelName"

, (

SELECT ProductID AS "data()"

FROM Production.Product

WHERE Production.Product.ProductModelID = Production.ProductModel.ProductModelID

FOR XML PATH ('')

) AS "@ns1:ProductIDs"

, (

SELECT ProductID AS "@ns1:ProductID"

,Name AS "@ns1:ProductName"

FROM Production.Product

WHERE Production.Product.ProductModelID = Production.ProductModel.ProductModelID

FOR XML PATH , TYPE

) AS "ns1:ProductNames"

FROM Production.ProductModel

WHERE ProductModelID= 7 OR ProductModelID=9

FOR XML PATH('ProductModelData'), ROOT('root');

Running this will produce the following XML:

<ns1:ProductNames>

</ns1:ProductNames>

</ProductModelData>

<ns1:ProductNames>

</ns1:ProductNames>

</ProductModelData>

</root>

Lots of bloat with the namespace declarations duplicated in every <row> and these are just tiny namespace names. Imagine the bloat when using lengthy FQDN type namespaces.

Now here is the query that I came up with to produce the same xml output above, but minus the bloat. What it does is that it first generates the xml in near enough the structure that I want, but then rebuilds it using XQuery to add the namespaces.

WITH xmlnamespaces (DEFAULT 'uri2')

SELECT XmlStructure.query('declare default element namespace "uri2";

for $x in /root return

{

for $y in $x/ProductModelData return

{attribute ns1:ProductModelID {$y/ProductModelID}}

{attribute ns1:ProductModelName {$y/ProductModelName}}

{attribute ns1:ProductIDs {$y/ProductIDs}}

<ns1:ProductNames>

{for $z in $y/ProductNames/row return

<row>

{attribute ns1:ProductID {$z/ProductID}}

{attribute ns1:ProductName {$z/ProductName}}

</row>

}

</ns1:ProductNames>

</ProductModelData>

}

</root>')

FROM

(

SELECT

(

SELECT ProductModelID AS "ProductModelID",

Name AS "ProductModelName",

(

SELECT ProductID AS "data()"

FROM Production.Product

WHERE Production.Product.ProductModelID =

Production.ProductModel.ProductModelID

FOR XML PATH ('')

) AS "ProductIDs",

(

SELECT ProductID AS "ProductID",

Name AS "ProductName"

FROM Production.Product

WHERE Production.Product.ProductModelID =

Production.ProductModel.ProductModelID

FOR XML PATH, type

) AS "ProductNames"

FROM Production.ProductModel

WHERE ProductModelID= 7 OR ProductModelID=9

FOR XML PATH('ProductModelData'), root('root'), TYPE

) XmlStructure

) x

This query produces this XML output:

<ns1:ProductNames>

</ns1:ProductNames>

</ProductModelData>

<ns1:ProductNames>

</ns1:ProductNames>

</ProductModelData>

</root>

I’m not suggesting that this is an ideal workaround as it is having to essentially build the XML twice, but it does remove all the excess bloat and for simple repetitive structures of XML that have long namespace names, this can massively reduce the size of the XML.

I did put both versions through a performance test and the first version with the namespace bloat on my rig averaged around 5ms to run and the second XQuery version averaged at around 10ms. With these two example versions with very short namespaces the first version generated an xml structure 2662 characters long and the second generated an xml structure 2062 characters, so about 20% difference in size.

Depending on the size of the XML structure being created, it could bring the size down greatly, so it could be worth the small extra overhead (10ms over 5ms) of using this approach. But as always with SQL server, it depends.

Enjoy!

Suppressing namespace attributes in nested select statements when using FOR XML workaround

Rate

Share

Share

Rate

Suppressing namespace attributes in nested select statements when using FOR XML workaround

Rate

Share

Share

Rate

Related content

Book Review: Big Red - Voyage of a Trident Submarine

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Inserting Markup into a String with SQL

Networking - Part 4

Speaking at Community Events - More Thoughts