Generate XML without a default namespace in child nodes

Question

I am trying to create an XML file using T-SQL with a default namespace in the root of the node, but does not contain the 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 problem is that the tag includes an empty xmlns = "" namespace, but the intended result is simply without a namespace definition.

Answer 1

The only way is to change:

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

For

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

Answer 2

You can use UDF for this. Example 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>