• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

prevent XML namespace declarations from being added to every FOR XML section

I am using FOR XML in Microsoft SQL Server 2005 to generate XML from SQL.  I am using the PATH mode and the WITH XMLNAMESPACES clause.  The problem is that the XML namespace declarations are being added to every FOR XML section.  It does specify in this MSDN technical article that it will do that. http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx  I have pasted their example of what happens below.  See each <o:Order> line has the namespace declaration repeated.  I need to prevent this though in order for my XML file to be valid.  I only want this at the top element.  Any suggestions would be greatly appreciated.
Thank you,
Aana
<doc xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" 
     xmlns="urn:example.com/doc">
  <c:Customer ID="ALFKI">
    <c:Orders>
      <o:Order xmlns:o="urn:example.com/order"
         xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" 
         OrderID="10643" />
      <o:Order xmlns:o="urn:example.com/order" 
         xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" 
         OrderID="10692" />
      <o:Order xmlns:o="urn:example.com/order" 
         xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" 
         OrderID="10702" />
      <o:Order xmlns:o="urn:example.com/order" 
         xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" 
         OrderID="10835" />
      <o:Order xmlns:o="urn:example.com/order" 
         xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" 
         OrderID="10952" />
      <o:Order xmlns:o="urn:example.com/order" 
         xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" 
         OrderID="11011" />
    </c:Orders>
    <c:CompanyName>Alfreds Futterkiste</c:CompanyName>
    <c:ContactName 
       ContactTitle="Sales Representative">Maria Anders</c:ContactName>
    <c:Address ZIP="12209">
      <c:Street>Obere Str. 57</c:Street>
      <c:City>Berlin</c:City>
    </c:Address>
  </c:Customer>

Open in new window

0
anixon93
Asked:
anixon93
1 Solution
 
anixon93Author Commented:
Well, I figured how to get around this for my purposes.  Basically not including the namespaces in the SQL query and adding them later after my C# code processes the XML template.  
Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now