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

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.

  • Good C#.NET Solution to manage frequent database polling
  • lu. instead of dbo.?
  • How to get max number in a column?
  • Calculate week of month starting Monday
  • Why is selecting from stored procedure not supported in relational databases?
  • How to find the name of stored procedure, based on table name search, using SQL Server 2008?
  • <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">
        <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?

    One Solution collect form web for “How do I suppress empty namespaces with FOR XML in Sql Server”

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.