You will not like this probably...
I did not find a generic solution for this... It is no problem to create this with repeated namespaces. This is not wrong, but annoying:
declare @xml as xml;
WITH XMLNAMESPACES('uri:animal' as an)
select @xml = (
select
a.c2 as "@an:species"
, (
select l.c3 as "text()"
from t2 l where l.c2 = a.c1
for xml path('an:leg'), type
) as "an:legs"
from t1 a
for xml path('an:animal'),root('zoo'));
One solution: build this via string-concatenation
But this is super-ugly...
an (almost) working solution...
The following solution uses FLWOR to re-create the XML after its creation, but this is not generic. It is necessary to add one element of this namespaces to the <zoo>, otherwise the namespace is created on a deeper level repeatedly. I added the attribut an:Dummy.
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');
GO
--the function
CREATE FUNCTION dbo.TestFunc()
RETURNS XML
AS
BEGIN
declare @xml as xml;
WITH XMLNAMESPACES('uri:animal' as an)
select @xml = (
select
a.c2 as "@an:species"
, (
select l.c3 as "text()"
from t2 l where l.c2 = a.c1
for xml path('an:leg'), type
) as "an:legs"
from t1 a
for xml path('an:animal'));
set @xml=@xml.query('declare namespace an="uri:animal";
<zoo an:Dummy="dummy">
{
for $a in /an:animal
return <an:animal an:species="{$a/@an:species}"><an:legs>
{
for $l in $a/an:legs/an:leg
return <an:leg>{$l/text()}</an:leg>
}
</an:legs></an:animal>
}
</zoo>');
return @xml;
END
GO
--the call
SELECT dbo.TestFunc();
GO
--clean up
drop function dbo.TestFunc;
drop table t1
drop table t2
The result
<zoo xmlns:an="uri:animal" an:Dummy="dummy">
<an:animal an:species="Mouse">
<an:legs>
<an:leg>Front Right</an:leg>
<an:leg>Front Left</an:leg>
<an:leg>Back Right</an:leg>
<an:leg>Back Left</an:leg>
</an:legs>
</an:animal>
<an:animal an:species="Chicken">
<an:legs>
<an:leg>Right</an:leg>
<an:leg>Left</an:leg>
</an:legs>
</an:animal>
<an:animal an:species="Snake">
<an:legs />
</an:animal>
</zoo>
Previous answer
Okay, I think I got this completely wrong in my first attempt. The following is an example, where a function returns the XML without added namespaces.
I use one of the later answers which builds the inner XML in advance without a namespace and creates the full XML as a second call with a namespace. Please check this out:
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');
GO
--the function
CREATE FUNCTION dbo.TestFunc()
RETURNS XML
AS
BEGIN
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'));
declare @resultXML XML;
;with XmlNamespaces( 'uri:animal' as an)
select @ResultXML= (SELECT @xml for xml path('') , root('zoo'),TYPE);
return @resultXML;
END
GO
--the call
SELECT dbo.TestFunc();
GO
--clean up
drop function dbo.TestFunc;
drop table t1
drop table t2
The result
<zoo xmlns:an="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>
