How do I remove redundant namespace in nested query when using FOR XML PATH

Question

UPDATE: I've discovered there is a Microsoft Connect item raised for this issue here

When using FOR XML PATH and WITH XMLNAMESPACES to declare a default namespace, I will get the namespace declaration duplicated in any top level nodes for nested queries that use FOR XML, I've stumbled across a few solutions on-line, but I'm not totally convinced...

Here's an Complete Example

/*
drop table t1
drop table t2
*/
create table t1 ( c1 int, c2 varchar(50))
create table t2 ( c1 int, c2 int, c3 varchar(50))
insert t1 values 
(1, 'Mouse'),
(2, 'Chicken'),
(3, 'Snake');
insert t2 values
(1, 1, 'Front Right'),
(2, 1, 'Front Left'),
(3, 1, 'Back Right'),
(4, 1, 'Back Left'),
(5, 2, 'Right'),
(6, 2, 'Left')



;with XmlNamespaces( default 'uri:animal')
select 
    a.c2 as "@species"
    , (select l.c3 as "text()" 
       from t2 l where l.c2 = a.c1 
       for xml path('leg'), type) as "legs"
from t1 a
for xml path('animal'), root('zoo')

What's the best solution?

Thresathresh

Can you show us your FOR XML PATH query, and the resulting XML with the extra namespaces?? It helps to see these kind of things on screen to diagnose / suggest workarounds... – Unbent

I've added a complete working example. – Thresathresh

Answer 1

If I have understood correctly, you are referring to the behavior that you might see in a query like this:

DECLARE @Order TABLE (
  OrderID INT, 
  OrderDate DATETIME)

DECLARE @OrderDetail TABLE (
  OrderID INT, 
  ItemID VARCHAR(1), 
  ItemName VARCHAR(50), 
  Qty INT)

INSERT @Order 
VALUES 
(1, '2010-01-01'),
(2, '2010-01-02')

INSERT @OrderDetail 
VALUES 
(1, 'A', 'Drink',  5),
(1, 'B', 'Cup',    2),
(2, 'A', 'Drink',  2),
(2, 'C', 'Straw',  1),
(2, 'D', 'Napkin', 1)

;WITH XMLNAMESPACES('http://test.com/order' AS od) 
SELECT
  OrderID AS "@OrderID",
  (SELECT 
     ItemID AS "@od:ItemID", 
     ItemName AS "data()" 
   FROM @OrderDetail 
   WHERE OrderID = o.OrderID 
   FOR XML PATH ('od.Item'), TYPE)
FROM @Order o 
FOR XML PATH ('od.Order'), TYPE, ROOT('xml')

Which gives the following results:

<xml xmlns:od="http://test.com/order">
  <od.Order OrderID="1">
    <od.Item xmlns:od="http://test.com/order" od:ItemID="A">Drink</od.Item>
    <od.Item xmlns:od="http://test.com/order" od:ItemID="B">Cup</od.Item>
  </od.Order>
  <od.Order OrderID="2">
    <od.Item xmlns:od="http://test.com/order" od:ItemID="A">Drink</od.Item>
    <od.Item xmlns:od="http://test.com/order" od:ItemID="C">Straw</od.Item>
    <od.Item xmlns:od="http://test.com/order" od:ItemID="D">Napkin</od.Item>
  </od.Order>
</xml>

As you said, the namespace is repeated in the results of the subqueries.

This behavior is a feature according to a conversation on devnetnewsgroup (website now defunct) although there is the option to vote on changing it.

My proposed solution is to revert back to FOR XML EXPLICIT:

SELECT
  1 AS Tag,
  NULL AS Parent,
  'http://test.com/order' AS [xml!1!xmlns:od],
  NULL AS [od:Order!2],
  NULL AS [od:Order!2!OrderID],
  NULL AS [od:Item!3],
  NULL AS [od:Item!3!ItemID]
UNION ALL
SELECT 
  2 AS Tag,
  1 AS Parent,
  'http://test.com/order' AS [xml!1!xmlns:od],
  NULL AS [od:Order!2],
  OrderID AS [od:Order!2!OrderID],
  NULL AS [od:Item!3],
  NULL [od:Item!3!ItemID]
FROM @Order 
UNION ALL
SELECT
  3 AS Tag,
  2 AS Parent,
  'http://test.com/order' AS [xml!1!xmlns:od],
  NULL AS [od:Order!2],
  o.OrderID AS [od:Order!2!OrderID],
  d.ItemName AS [od:Item!3],
  d.ItemID AS [od:Item!3!ItemID]
FROM @Order o INNER JOIN @OrderDetail d ON o.OrderID = d.OrderID
ORDER BY [od:Order!2!OrderID], [od:Item!3!ItemID]
FOR XML EXPLICIT

And see these results:

<xml xmlns:od="http://test.com/order">
  <od:Order OrderID="1">
    <od:Item ItemID="A">Drink</od:Item>
    <od:Item ItemID="B">Cup</od:Item>
  </od:Order>
  <od:Order OrderID="2">
    <od:Item ItemID="A">Drink</od:Item>
    <od:Item ItemID="C">Straw</od:Item>
    <od:Item ItemID="D">Napkin</od:Item>
  </od:Order>
</xml>

Peking

+1 Cheers for your answer, any thoughts on how this compares to the alternative? (see my answer to my question) – Thresathresh

Answer 2

After hours of desperation and hundreds of trials & errors, I've come up with the solution below.

I had the same issue, when I wanted just one xmlns attribute, on the root node only. But I also had a very difficult query with lot's of subqueries and FOR XML EXPLICIT method alone was just too cumbersome. So yes, I wanted the convenience of FOR XML PATH in the subqueries and also to set my own xmlns.

I kindly borrowed the code of 8kb's answer, because it was so nice. I tweaked it a bit for better understanding. Here is the code:

DECLARE @Order TABLE (OrderID INT, OrderDate DATETIME)    
DECLARE @OrderDetail TABLE (OrderID INT, ItemID VARCHAR(1), Name VARCHAR(50), Qty INT)    
INSERT @Order VALUES (1, '2010-01-01'), (2, '2010-01-02')    
INSERT @OrderDetail VALUES (1, 'A', 'Drink',  5),
                           (1, 'B', 'Cup',    2),
                           (2, 'A', 'Drink',  2),
                           (2, 'C', 'Straw',  1),
                           (2, 'D', 'Napkin', 1)

-- Your ordinary FOR XML PATH query
DECLARE @xml XML = (SELECT OrderID AS "@OrderID",
                        (SELECT ItemID AS "@ItemID", 
                                Name AS "data()" 
                         FROM @OrderDetail 
                         WHERE OrderID = o.OrderID 
                         FOR XML PATH ('Item'), TYPE)
                    FROM @Order o 
                    FOR XML PATH ('Order'), ROOT('dummyTag'), TYPE)

-- Magic happens here!       
SELECT 1 AS Tag
      ,NULL AS Parent
      ,@xml AS [xml!1!!xmltext]
      ,'http://test.com/order' AS [xml!1!xmlns]
FOR XML EXPLICIT

Result:

<xml xmlns="http://test.com/order">
  <Order OrderID="1">
    <Item ItemID="A">Drink</Item>
    <Item ItemID="B">Cup</Item>
  </Order>
  <Order OrderID="2">
    <Item ItemID="A">Drink</Item>
    <Item ItemID="C">Straw</Item>
    <Item ItemID="D">Napkin</Item>
  </Order>
</xml>

If you selected @xml alone, you would see that it contains root node dummyTag. We don't need it, so we remove it by using directive xmltext in FOR XML EXPLICIT query:

,@xml AS [xml!1!!xmltext]

Although the explanation in MSDN sounds more sophisticated, but practically it tells the parser to select the contents of XML root node.

Not sure how fast the query is, yet currently I am relaxing and drinking Scotch like a gent while peacefully looking at the code...

Subsumption

Works like a charm, if namespace does not work, namespace will be added... – Bernadettebernadina

Answer 3

An alternative solution I've seen is to add the XMLNAMESPACES declaration after building the xml into a temporary variable:

declare @xml as xml;
select @xml = (
select 
    a.c2 as "@species"
    , (select l.c3 as "text()" 
       from t2 l where l.c2 = a.c1 
       for xml path('leg'), type) as "legs"
from t1 a
for xml path('animal'))

;with XmlNamespaces( 'uri:animal' as an)
select @xml for xml path('') , root('zoo');

Thresathresh

For 1m rows, your solution runs twice as fast. =( Although it does have an xmlns="" in every "species" row. Does that matter? The interesting thing about the FOR XML EXPLICIT method is that it allows you to do multiple namespaces. I'm not sure how you would do that with an alternate solution (although if you don't have the need to, it probably doesn't matter). Link to the performance testing here if you're curious: tinyurl.com/3yejtyv – Peking

I've change the result to remove the blank namespace but ultimately either way I guess this approach generates nice looking xml but it isn't really valid. so i've accepted 8kb's answer as the best approach. – Thresathresh

DEFAULT namespace is adding empty xmlns in the first child node <leveranse xmlns=""> – Bernadettebernadina

Answer 4

The problem here is compounded by the fact that you cannot directly declare the namespaces manually when using XML PATH. SQL Server will disallow any attribute names beginning with 'xmlns' and any tag names with colons in them.

Rather than having to resort to using the relatively unfriendly XML EXPLICIT, I got around the problem by first generating XML with 'cloaked' namespace definitions and references, then doing string replaces as follows ...

DECLARE @Order TABLE (
  OrderID INT, 
  OrderDate DATETIME)

DECLARE @OrderDetail TABLE (
  OrderID INT, 
  ItemID VARCHAR(1), 
  ItemName VARCHAR(50), 
  Qty INT)

INSERT @Order 
VALUES 
(1, '2010-01-01'),
(2, '2010-01-02')

INSERT @OrderDetail 
VALUES 
(1, 'A', 'Drink',  5),
(1, 'B', 'Cup',    2),
(2, 'A', 'Drink',  2),
(2, 'C', 'Straw',  1),
(2, 'D', 'Napkin', 1)

declare @xml xml

set @xml = (SELECT
  'http://test.com/order' as "@xxmlns..od",  -- 'Cloaked' namespace def
  (SELECT OrderID AS "@OrderID", 
    (SELECT 
      ItemID AS "@od..ItemID", 
      ItemName AS "data()" 
     FROM @OrderDetail 
     WHERE OrderID = o.OrderID 
     FOR XML PATH ('od..Item'), TYPE)
   FROM @Order o
   FOR XML PATH ('od..Order'), TYPE)
  FOR XML PATH('xml'))

set @xml = cast(replace(replace(cast(@xml as nvarchar(max)), 'xxmlns', 'xmlns'),'..',':') as xml)

select @xml

A few things to point out:

1. I'm using 'xxmlns' as my cloaked version of 'xmlns' and '..' to stand in for ':'. This might not work for you if you're likely to have '..' as part of text values - you can substitute this with something else as long as you pick something that makes a valid XML identifier.

2. Since we want the xmlns definition at the top level, we cannot use the 'ROOT' option to XML PATH - instead I needed to add an another outer level to the subselect structure to achieve this.

Shrew

This definitely seems to be a well thought out response to the question. However, the question itself was asked 6 years ago, and the answer provided here should be taken by new readers as a recent solution to a potentially long standing issue. – Glycogen

@Glycogen - yes, absolutely - I certainly don't expect the OP to be interested in my answer after all this time! - it was definitely aimed at other readers searching for solutions to this issue (as I was), which as far as I can gather will still be present in SQL Server 2016. – Shrew

I've forgotten what XML is now, Sorry! (+1 anyway for a quality answer!) – Thresathresh

String manipulation of XML like this is unsafe. What if you had the string xxmlns or .. inside a text node of one of your values? For example, what if you were running this in code which processed an XML export of this very stackoverflow answer? You do at least mention that as a caveat, but I think, even so, this answer should not be considered valid due to this issue. – Wonderment

This, for me, is the best solution out there, in absence of a NONAMESPACE option for FOR XML PATH. While it doesn't help the readability, especially when, in my case, there are multiple xmlns declarations and an xsi:schemalocation that can consist of more than one xsd, it's the only one that a) works for my use case, and, b) remains mostly true to the original FOR XML PATH queries I started with – Coal

Answer 5

I'm bit confusing about all these explanation while declaring a "xmlns:animals" manually is doing the job : Here an example i wrote to generate Open graph meta data

DECLARE @l_xml as XML;
SELECT @l_xml = 
(
SELECT 'http://ogp.me/ns# fb: http://ogp.me/ns/fb# scanilike: http://ogp.me/ns/fb/scanilike#' as 'xmlns:og',
    (SELECT
        (SELECT 'og:title' as 'property', title as 'content' for xml raw('meta'), TYPE),
        (SELECT 'og:type' as 'property', OpenGraphWebMetadataTypes.name as 'content' for xml raw('meta'), TYPE),
        (SELECT 'og:image' as 'property', image as 'content' for xml raw('meta'), TYPE),
        (SELECT 'og:url' as 'property', url as 'content' for xml raw('meta'), TYPE),
        (SELECT 'og:description' as 'property', description as 'content' for xml raw('meta'), TYPE),
        (SELECT 'og:site_name' as 'property', siteName as 'content' for xml raw('meta'), TYPE),
        (SELECT 'og:appId' as 'property', appId as 'content' for xml raw('meta'), TYPE)
     FROM OpenGraphWebMetaDatas INNER JOIN OpenGraphWebMetadataTypes ON OpenGraphWebMetaDatas.type = OpenGraphWebMetadataTypes.id WHERE THING_KEY = @p_index 
     for xml path('header'), TYPE),
     (SELECT '' as 'body' for xml path(''), TYPE)
     for xml raw('html'), TYPE
)

RETURN @l_xml 

returning the expected result

<html xmlns:og="http://ogp.me/ns# fb: http://ogp.me/ns/fb# scanilike: http://ogp.me/ns/fb/scanilike#">
<header>
<meta property="og:title" content="The First object"/>
<meta property="og:type" content="scanilike:tag"/>
<meta property="og:image" content="http://www.mygeolive.com/images/facebook/facebook-logo.jpg"/>
<meta property="og:url" content="http://www.scanilike.com/opengraph?id=1"/>
<meta property="og:description" content="This is the very first object created using the IOThing &amp; ScanILike software. We keep it in file for history purpose. "/>
<meta property="og:site_name" content="http://www.scanilike.com"/>
<meta property="og:appId" content="200270673369521"/>
</header>
<body/>
</html>

hope this will help people are searching the web for similar issue. ;-)

Bobsledding

Great! So the non-obvious trick is that the root element needs to be selected using for xml raw but the inner ones can still use for xml path. – Intercollegiate

Actually it only works if you set the non-default xmlns:og, if you want to set the default xmlns then you'll still get repeated namespace declarations. – Intercollegiate

Answer 6

It would be really nice if FOR XML PATH actually worked more cleanly. Reworking your original example with @table variables:

declare @t1 table (c1 int, c2 varchar(50));
declare @t2 table (c1 int, c2 int, c3 varchar(50));
insert @t1 values 
    (1, 'Mouse'),
    (2, 'Chicken'),
    (3, 'Snake');
insert @t2 values
    (1, 1, 'Front Right'),
    (2, 1, 'Front Left'),
    (3, 1, 'Back Right'),
    (4, 1, 'Back Left'),
    (5, 2, 'Right'),
    (6, 2, 'Left');

;with xmlnamespaces( default 'uri:animal')
select  a.c2 as "@species",
    (
        select  l.c3 as "text()"
        from    @t2 l
        where   l.c2 = a.c1
        for xml path('leg'), type
    ) as "legs"
from @t1 a
for xml path('animal'), root('zoo');

Yields the problem XML with repeated namespace declarations:

<zoo xmlns="uri:animal">
  <animal species="Mouse">
    <legs>
      <leg xmlns="uri:animal">Front Right</leg>
      <leg xmlns="uri:animal">Front Left</leg>
      <leg xmlns="uri:animal">Back Right</leg>
      <leg xmlns="uri:animal">Back Left</leg>
    </legs>
  </animal>
  <animal species="Chicken">
    <legs>
      <leg xmlns="uri:animal">Right</leg>
      <leg xmlns="uri:animal">Left</leg>
    </legs>
  </animal>
  <animal species="Snake" />
</zoo>

You can migrate elements between namespaces using XQuery with wildcard namespace matching (that is, *:elementName), as below, but it can be quite cumbersome for complex XML:

;with xmlnamespaces( default 'http://tempuri.org/this/namespace/is/meaningless' )
select (
    select  a.c2 as "@species",
        (
            select  l.c3 as "text()"
            from    @t2 l
            where   l.c2 = a.c1
            for xml path('leg'), type
        ) as "legs"
    from @t1 a
    for xml path('animal'), root('zoo'), type
).query('declare default element namespace "uri:animal";
<zoo>
{ for $a in *:zoo/*:animal return
    <animal>
    {attribute species {$a/@species}}
    { for $l in $a/*:legs return
        <legs>
        { for $m in $l/*:leg return
            <leg>{ $m/text() }</leg>
        }</legs>
    }</animal>
}</zoo>');

Which yields your desired result:

<zoo xmlns="uri:animal">
  <animal species="Mouse">
    <legs>
      <leg>Front Right</leg>
      <leg>Front Left</leg>
      <leg>Back Right</leg>
      <leg>Back Left</leg>
    </legs>
  </animal>
  <animal species="Chicken">
    <legs>
      <leg>Right</leg>
      <leg>Left</leg>
    </legs>
  </animal>
  <animal species="Snake" />
</zoo>

Boehike