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

Printed 2014/04/18 12:21AM

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

By ArthurOlcot, 2012/08/28

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:

<root xmlns="uri2" xmlns:ns1="uri1">

  <ProductModelData ns1:ProductModelID="7" ns1:ProductModelName="HL Touring Frame" ns1:ProductIDs="885 887 888 889 890 891 892 893">

    <ns1:ProductNames>

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="885" ns1:ProductName="HL Touring Frame - Yellow, 60" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="887" ns1:ProductName="HL Touring Frame - Yellow, 46" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="888" ns1:ProductName="HL Touring Frame - Yellow, 50" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="889" ns1:ProductName="HL Touring Frame - Yellow, 54" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="890" ns1:ProductName="HL Touring Frame - Blue, 46" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="891" ns1:ProductName="HL Touring Frame - Blue, 50" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="892" ns1:ProductName="HL Touring Frame - Blue, 54" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="893" ns1:ProductName="HL Touring Frame - Blue, 60" />

    </ns1:ProductNames>

  </ProductModelData>

  <ProductModelData ns1:ProductModelID="9" ns1:ProductModelName="LL Road Frame" ns1:ProductIDs="722 723 724 725 726 727 728 729 730 736 737 738">

    <ns1:ProductNames>

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="722" ns1:ProductName="LL Road Frame - Black, 58" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="723" ns1:ProductName="LL Road Frame - Black, 60" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="724" ns1:ProductName="LL Road Frame - Black, 62" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="725" ns1:ProductName="LL Road Frame - Red, 44" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="726" ns1:ProductName="LL Road Frame - Red, 48" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="727" ns1:ProductName="LL Road Frame - Red, 52" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="728" ns1:ProductName="LL Road Frame - Red, 58" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="729" ns1:ProductName="LL Road Frame - Red, 60" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="730" ns1:ProductName="LL Road Frame - Red, 62" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="736" ns1:ProductName="LL Road Frame - Black, 44" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="737" ns1:ProductName="LL Road Frame - Black, 48" />

      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="738" ns1:ProductName="LL Road Frame - Black, 52" />

    </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

      <root xmlns="uri2" xmlns:ns1="uri1">

         {

         for $y in $x/ProductModelData return

            <ProductModelData>

               {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:

<root xmlns="uri2" xmlns:ns1="uri1">

  <ProductModelData ns1:ProductModelID="7" ns1:ProductModelName="HL Touring Frame" ns1:ProductIDs="885 887 888 889 890 891 892 893">

    <ns1:ProductNames>

      <row ns1:ProductID="885" ns1:ProductName="HL Touring Frame - Yellow, 60" />

      <row ns1:ProductID="887" ns1:ProductName="HL Touring Frame - Yellow, 46" />

      <row ns1:ProductID="888" ns1:ProductName="HL Touring Frame - Yellow, 50" />

      <row ns1:ProductID="889" ns1:ProductName="HL Touring Frame - Yellow, 54" />

      <row ns1:ProductID="890" ns1:ProductName="HL Touring Frame - Blue, 46" />

      <row ns1:ProductID="891" ns1:ProductName="HL Touring Frame - Blue, 50" />

      <row ns1:ProductID="892" ns1:ProductName="HL Touring Frame - Blue, 54" />

      <row ns1:ProductID="893" ns1:ProductName="HL Touring Frame - Blue, 60" />

    </ns1:ProductNames>

  </ProductModelData>

  <ProductModelData ns1:ProductModelID="9" ns1:ProductModelName="LL Road Frame" ns1:ProductIDs="722 723 724 725 726 727 728 729 730 736 737 738">

    <ns1:ProductNames>

      <row ns1:ProductID="722" ns1:ProductName="LL Road Frame - Black, 58" />

      <row ns1:ProductID="723" ns1:ProductName="LL Road Frame - Black, 60" />

      <row ns1:ProductID="724" ns1:ProductName="LL Road Frame - Black, 62" />

      <row ns1:ProductID="725" ns1:ProductName="LL Road Frame - Red, 44" />

      <row ns1:ProductID="726" ns1:ProductName="LL Road Frame - Red, 48" />

      <row ns1:ProductID="727" ns1:ProductName="LL Road Frame - Red, 52" />

      <row ns1:ProductID="728" ns1:ProductName="LL Road Frame - Red, 58" />

      <row ns1:ProductID="729" ns1:ProductName="LL Road Frame - Red, 60" />

      <row ns1:ProductID="730" ns1:ProductName="LL Road Frame - Red, 62" />

      <row ns1:ProductID="736" ns1:ProductName="LL Road Frame - Black, 44" />

      <row ns1:ProductID="737" ns1:ProductName="LL Road Frame - Black, 48" />

      <row ns1:ProductID="738" ns1:ProductName="LL Road Frame - Black, 52" />

    </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!


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.