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

Question

I want to create a sitemap xml file (including images) directly from the database without another process (like transformation or another trick).

My query is:

;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

Returns:

<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 repeated namespace declaration:

<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'm sure you realise that the additional otiose namespace declarations don't change the meaning of the XML document, so if the result is going to be consumed by an XML-conformant tool, they shouldn't matter. Nevertheless I know there are some tools out there which don't do XML Namespaces correctly, and in a large XML instance superfluous repeated namespace declarations can bloat the size of the result significantly, which may cause its own problems.

In general there is no getting around the fact that each SELECT...FOR XML statement within the scope of a WITH XMLNAMESPACES prefix will generate namespace declarations on the outermost XML element(s) in its result set, in all XML-supporting versions of SQL Server up to SQL Server 2012.

In your specific example, you can get fairly close to the desired XML by separating the SELECTs rather than nesting them, and using the ROOT syntax for the enveloping root element, thus:

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 

The result being:

<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 doesn't provide a completely general solution to the problem.