title: How do I suppress empty namespaces with FOR XML in Sql Server
body: <p>We are encountering a strange problem with SQL Server 2005/2008 using the FOR XML with fragments of xml and namespaces. Here is the query in question.</p>
  
  <pre><code>WITH XMLNAMESPACES ( 
  DEFAULT 'http://tempuri.org/newincomingxml.xsd',
  'http://tempuri.org/newincomingxml.xsd' as [xsi],
  'http://tempuri.org/newincomingxml.xsd' as [a]
  ) 
  SELECT 
   [@a:Source], [AddressCount], [ConsumerCount], [EmailCount], [PermissionCount]
  , (
    SELECT 
     [Consumer]
    FROM tbcExportBRC_Current xmlmaster
    FOR XML PATH(''), ROOT('Consumers'), TYPE
   )
  FROM tbcExportBRCBatch_Current xmlroot
  FOR XML PATH('Datafeed'), TYPE
  </code></pre>
  
  <p>The <strong>[Customer]</strong> field is an xml fragment. When I run this I get.</p>
  
  <pre><code>&lt;Datafeed xmlns:a="http://tempuri.org/newincomingxml.xsd" xmlns:xsi="http://tempuri.org/newincomingxml.xsd" xmlns="http://tempuri.org/newincomingxml.xsd" a:Source="DSD"&gt;
    &lt;AddressCount&gt;0&lt;/AddressCount&gt;
    &lt;ConsumerCount&gt;0&lt;/ConsumerCount&gt;
    &lt;EmailCount&gt;0&lt;/EmailCount&gt;
    &lt;PermissionCount&gt;0&lt;/PermissionCount&gt;
    &lt;Consumers xmlns:a="http://tempuri.org/newincomingxml.xsd" xmlns:xsi="http://tempuri.org/newincomingxml.xsd" xmlns="http://tempuri.org/newincomingxml.xsd"&gt;
      &lt;Consumer&gt;
        &lt;ConsumerType xmlns=""&gt;Individual&lt;/ConsumerType&gt;
        &lt;FirstName xmlns=""&gt;STEVE&lt;/FirstName&gt;
        &lt;LastName xmlns=""&gt;SMITH&lt;/LastName&gt;
      &lt;/Consumer&gt;
    &lt;/Consumers&gt;
  &lt;/Datafeed&gt;
  </code></pre>
  
  <p>If you notice the  tag's children have <strong>xmlns=""</strong> in them. If we look at the fragment directly in the table it looks like this.</p>
  
  <pre><code>      &lt;ConsumerType&gt;Individual&lt;/ConsumerType&gt;
        &lt;FirstName&gt;STEVE&lt;/FirstName&gt;
        &lt;LastName&gt;SMITH&lt;/LastName&gt;
  </code></pre>
  
  <p>I can remove the default namespace</p>
  
  <pre><code>DEFAULT 'http://tempuri.org/newincomingxml.xsd',
  </code></pre>
  
  <p>It removes the xmlns="" but we need to keep that in the file. Any ideas?</p>