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

update: i've discovered there microsoft connect item raised issue here

when using for xml path , with xmlnamespaces declare default namespace, namespace decleration duplicated in top level nodes nested queries use xml, i've stumbled across few solutions on-line, i'm not totally convinced...

here's 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 "@species"     , (select l.c3 "text()"         t2 l l.c2 = a.c1         xml path('leg'), type) "legs" t1 xml path('animal'), root('zoo') 

what's best solution?

if have understood correctly, referring behavior might see in query 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' od)  select   orderid "@orderid",   (select       itemid "@od:itemid",       itemname "data()"     @orderdetail     orderid = o.orderid     xml path ('od.item'), type) @order o  xml path ('od.order'), type, root('xml') 

which gives 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 said, namespace repeated in results of subqueries.

this behavior feature according conversation on devnetnewsgroup (website defunct) although there option vote on changing it.

my proposed solution revert for xml explicit:

select   1 tag,   null parent,   'http://test.com/order' [xml!1!xmlns:od],   null [od:order!2],   null [od:order!2!orderid],   null [od:item!3],   null [od:item!3!itemid] union select    2 tag,   1 parent,   'http://test.com/order' [xml!1!xmlns:od],   null [od:order!2],   orderid [od:order!2!orderid],   null [od:item!3],   null [od:item!3!itemid] @order  union select   3 tag,   2 parent,   'http://test.com/order' [xml!1!xmlns:od],   null [od:order!2],   o.orderid [od:order!2!orderid],   d.itemname [od:item!3],   d.itemid [od:item!3!itemid] @order o inner join @orderdetail d on o.orderid = d.orderid order [od:order!2!orderid], [od:item!3!itemid] 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>