How do I remove redundant namespace in nested quer

Question

There is a solution for remove namespace here!, but I need it works in SQL function, I'm getting this error:

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.

Can somebody help me?

Thanks Xtyan

From comment: This is the needed XML-output

<zoo xmlns:an="uri:animal">
  <an:animal 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 species="Chicken">
    <an:legs>
      <an:leg>Right</an:leg>
      <an:leg>Left</an:leg>
    </an:legs>
  </an:animal>
  <an:animal species="Snake" />
</zoo>

Answer 1

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>