How do I suppress empty namespaces with FOR XML in Sql Server

Question

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.

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

The [Customer] field is an xml fragment. When I run this I get.

<Datafeed xmlns:a="http://tempuri.org/newincomingxml.xsd" xmlns:xsi="http://tempuri.org/newincomingxml.xsd" xmlns="http://tempuri.org/newincomingxml.xsd" a:Source="DSD">
  <AddressCount>0</AddressCount>
  <ConsumerCount>0</ConsumerCount>
  <EmailCount>0</EmailCount>
  <PermissionCount>0</PermissionCount>
  <Consumers xmlns:a="http://tempuri.org/newincomingxml.xsd" xmlns:xsi="http://tempuri.org/newincomingxml.xsd" xmlns="http://tempuri.org/newincomingxml.xsd"&gt;
    <Consumer>
      <ConsumerType xmlns="">Individual</ConsumerType>
      <FirstName xmlns="">STEVE</FirstName>
      <LastName xmlns="">SMITH</LastName>
    </Consumer>
  </Consumers>
</Datafeed>

If you notice the tag's children have xmlns="" in them. If we look at the fragment directly in the table it looks like this.

      <ConsumerType>Individual</ConsumerType>
      <FirstName>STEVE</FirstName>
      <LastName>SMITH</LastName>

I can remove the default namespace

DEFAULT 'http://tempuri.org/newincomingxml.xsd',

It removes the xmlns="" but we need to keep that in the file. Any ideas?

Answer 1

The result is the correct one. In the table you have elements with no namespace, so when you add them under the Consumers element with the default namespace of xmlns="http://tempuri.org/newincomingxml.xsd", the elements from the table must overwride the default namespace back to "".

That is exactly what you should see. Not having the xmlns="" would mean that the ConsumerType/FirstName/LastName elements are in the namespace "http://tempuri.org/newincomingxml.xsd", which is false.

What you probably whant is to probably move the ConsumerType/FirstName/LastName elements into the "http://tempuri.org/newincomingxml.xsd" namespace, to match the namespace of the parent Consumer element.