How to avoid namespace in child nodes using FOR XML PATH?

Question

I want to create an sitemap XML file (including images) directly from a database without another process (e.g. conversion or other trick).

My request:

;WITH XMLNAMESPACES( DEFAULT 'http://www.sitemaps.org/schemas/sitemap/0.9', 'http://www.google.com/schemas/sitemap-image/1.1' as [image] ) SELECT (SELECT 'mysite' as [loc], (select 'anotherloc' as [image:loc] for XML path('image:image'), type ) for xml path('url'), type ) for xml path('urlset'), type 

Return:

 <urlset xmlns:image="http://www.google.com/schemas/sitemap-image/1.1" xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"> <url xmlns:image="http://www.google.com/schemas/sitemap-image/1.1" xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"> <loc>mysite</loc> <image:image xmlns:image="http://www.google.com/schemas/sitemap-image/1.1" xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"> <image:loc>anotherloc</image:loc> </image:image> </url> </urlset> 

But I need this output without re-declaring the namespace:

 <urlset xmlns:image="http://www.google.com/schemas/sitemap-image/1.1" xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"> <url> <loc>mysite</loc> <image:image> <image:loc>anotherloc</image:loc> </image:image> </url> </urlset> 

Answer 1

I am sure that you understand that additional namespace declarations do not change the meaning of the XML document, therefore, if the result is consumed using an XML-compatible tool, they should not matter. However, I know that there are some tools that do not have the correct XML namespace, and in a large XML instance, redundant repeated namespace declarations can greatly inflate the size of the result, which can cause its own problems.

In general, one can’t get around the fact that each SELECT...FOR XML within the WITH XMLNAMESPACES prefix generates namespace declarations on an external XML element in its result set in all XML-supported versions of SQL Server prior to SQL Server 2012.

In your specific example, you can approach the desired XML by separating SELECT rather than overlaying them, and using the ROOT syntax for the wrapping root element, this way:

 DECLARE @inner XML; WITH XMLNAMESPACES('http://www.google.com/schemas/sitemap-image/1.1' as [image]) SELECT @inner = ( SELECT 'anotherloc' AS [image:loc] FOR XML PATH('image:image'), TYPE ) ;WITH XMLNAMESPACES( DEFAULT 'http://www.sitemaps.org/schemas/sitemap/0.9' ) SELECT 'mysite' AS [loc], @inner FOR XML PATH('url'), ROOT('urlset'), TYPE 

Result:

 <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"> <url> <loc>mysite</loc> <image:image xmlns:image="http://www.google.com/schemas/sitemap-image/1.1" xmlns=""> <image:loc>anotherloc</image:loc> </image:image> </url> </urlset> 

But this approach does not provide a completely general solution to the problem.

Answer 2

You can use UDF. Example:

 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> 

Answer 3

It may be too late to answer, but it is a quick fix.

  'DECLARE @PageNumber Int = 1;
 DECLARE @siteMapXml XML;
 ; WITH XMLNAMESPACES (
     'http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd' as "schemaLocation", 
     'http://www.w3.org/2001/XMLSchema-instance' as xsi,
     'http://www.google.com/schemas/sitemap-image/1.1' as [image],
     DEFAULT 'http://www.sitemaps.org/schemas/sitemap/0.9'
     )
         SELECT @siteMapXml = (

                                     SELECT   
                                        Slug loc,
                                        convert (varchar (300), [Image]) as [image: image / image: loc] 
                                        ,
                                        Convert (char (10), UpdatedOnUtc, 126) as lastmod,
                                        'hourly' as changefreq,
                                        '0.5' as priority
                                     FROM Products (NOLOCK) 
                                     WHERE Pagenumber = @PageNumber

 FOR XML PATH ('url'), ROOT ('urlset'))
 SELECT @siteMapXml = REPLACE (CAST (@siteMapXml AS NVARCHAR (MAX)), 'xmlns: schemaLocation =', 'xsi: schemaLocation =')
 SELECT @siteMapXml '