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.

<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?

Answers


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.


Need Your Help

Quicksorting trouble

java quicksort

I am working on a quicksort but for some reason i end up with the same issues.

What tools would you used to write a modular database in Python?

python database design modularity

I am building a modular application in python. The plugins are not known until runtime.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.