Generate XML with no default namespace in child nodes

Question

I am trying to generate an XML file using T-SQL with a default namespace in the root node but no namespaces defined in the child nodes.

DECLARE @xmlData XML
DECLARE @xmlInner XML

SELECT @xmlInner =
(
SELECT * FROM dbo.GH_DATA_BS_EVLTN_MNTH_ANL [r]
FOR XML PATH('r'), TYPE
)

;WITH XMLNAMESPACES (DEFAULT 'http://www.testnamespace.com')
SELECT @xmlData = 

(
SELECT '2012-10-25T14:13:00Z' as "@DataFeedDate",@xmlInner
FOR XML PATH('root')
)

SELECT @xmlData

The script above generates the following XML file

<root xmlns="http://www.testnamespace.com" DataFeedDate="2012-10-25T14:13:00Z">
  <r xmlns="">
    <RPRT_DT_CD>2012-10-25T14:15:00-05:00</RPRT_DT_CD>
    <RPRT_MO_CD>2013-01</RPRT_MO_CD>
    <RPRT_EV_CD>1</RPRT_EV_CD>
  </r>
</root>

The issue is that tag includes the blank namespace xmlns="" but the intended result is to get simply with no namespace defined.

Answer 1

The only way around it is to change the :

;WITH XMLNAMESPACES (DEFAULT 'http://www.testnamespace.com')

To

;WITH XMLNAMESPACES ('http://www.testnamespace.com' as anySuffix)

Answer 2

You can use UDF to do this. Example is below:

ALTER FUNCTION [dbo].[udf_get_child_section] (
    @serviceHeaderId INT
 )
RETURNS XML



BEGIN

    DECLARE @result XML;

    SELECT @result = 
    (
        SELECT 1 AS 'ChildElement'
        FOR XML PATH('Child')
    )

    RETURN @result

END


GO

DECLARE @Ids TABLE
( 
    ID int 
)

INSERT INTO @Ids
SELECT 1 AS ID 
UNION ALL
SELECT 2 AS ID

;WITH XMLNAMESPACES (DEFAULT 'http://www...com/content')
SELECT 
    [dbo].[udf_get_child_section](ID)
FROM 
    @Ids
FOR XML PATH('Parent')

Result:

<Parent xmlns="http://www...com/content">
  <Child xmlns="">
    <ChildElement>1</ChildElement>
  </Child>
</Parent>
<Parent xmlns="http://www...com/content">
  <Child xmlns="">
    <ChildElement>1</ChildElement>
  </Child>
</Parent>