title: How can I use a WITH XMLNAMESPACES clause with correlated queries?
body: <p>I'm trying to use the <code>FOR XML</code> feature of SQL Server to generate some XML, with some specific namespaces.</p>
  
  <p>My target XML should look something like:</p>
  
  <pre><code>&lt;ns1:CustomerInvoices xmlns:ns1="urn:example.com:Invoice:01.00"&gt;
    &lt;CustomerInvoice&gt;
      &lt;Header&gt;
        &lt;OrderDate&gt;2001-11-13T00:00:00&lt;/OrderDate&gt;
        &lt;SalesOrderNumber&gt;SO44643&lt;/SalesOrderNumber&gt;
      &lt;/Header&gt;
      &lt;Lines&gt;
        &lt;LineTotal&gt;3578.270000&lt;/LineTotal&gt;
        &lt;UnitPrice&gt;3578.2700&lt;/UnitPrice&gt;
        &lt;OrderQty&gt;1&lt;/OrderQty&gt;
      &lt;/Lines&gt;
    &lt;/CustomerInvoice&gt;
    &lt;CustomerInvoice&gt;...(abbreviated)...&lt;/CustomerInvoice&gt;
  &lt;/ns1:CustomerInvoices&gt;
  </code></pre>
  
  <p>The problem I have, is that when I use the <code>WITH XMLNAMESPACES</code> clause, is the namespace declaration appears on lots of the child nodes, which the third party consumer of this XML would "prefer not to get", i.e. I'm doing something like:</p>
  
  <pre><code>Use AdventureWorks2008
  
  ;WITH XMLNAMESPACES ('urn:example.com:Invoice:01.00' as ns1)
  
  SELECT      (SELECT     hdr.OrderDate,
                          hdr.SalesOrderNumber
              FOR XML PATH (''), TYPE) AS Header,
              (SELECT     line.LineTotal,
                          line.UnitPrice,
                          line.OrderQty
  
              FROM        Sales.SalesOrderDetail  AS line
  
              WHERE       line.SalesOrderID = hdr.SalesOrderID
              FOR XML PATH (''), TYPE) AS Lines
  
  FROM        Sales.SalesOrderHeader  AS hdr
  
  WHERE       hdr.ModifiedDate = '2001-11-20'
  
  FOR XML PATH('CustomerInvoice'), ROOT('ns1:CustomerInvoices') 
  </code></pre>
  
  <p>Which gives:</p>
  
  <pre><code>&lt;ns1:CustomerInvoices xmlns:ns1="urn:example.com:Invoice:01.00"&gt;
    &lt;CustomerInvoice&gt;
      &lt;Header&gt;
        &lt;OrderDate xmlns:ns1="urn:example.com:Invoice:01.00"&gt;2001-11-13T00:00:00&lt;/OrderDate&gt;
        &lt;SalesOrderNumber xmlns:ns1="urn:example.com:Invoice:01.00"&gt;SO44643&lt;/SalesOrderNumber&gt;
      &lt;/Header&gt;
      &lt;Lines&gt;
        &lt;LineTotal xmlns:ns1="urn:example.com:Invoice:01.00"&gt;3578.270000&lt;/LineTotal&gt;
        &lt;UnitPrice xmlns:ns1="urn:example.com:Invoice:01.00"&gt;3578.2700&lt;/UnitPrice&gt;
        &lt;OrderQty xmlns:ns1="urn:example.com:Invoice:01.00"&gt;1&lt;/OrderQty&gt;
      &lt;/Lines&gt;
    &lt;/CustomerInvoice&gt;
    &lt;CustomerInvoice&gt;...(abbreviated)...&lt;/CustomerInvoice&gt;
  &lt;/ns1:CustomerInvoices&gt;
  </code></pre>
  
  <p>Is it possible to tweak the query to get the namespaces correct, or will I have to load this into "something else" to remove the redundant namespace declarations?</p>