title: Selecting columns as XML with namespace
body: <p>I need to select some columns from a table as XML with namespaces included in them along with other columns as is. For example, I have a following table layout:</p>
  
  <pre><code>ID  C1  X1C1  X1C2  X2C3
  1   A   1     2     3
  </code></pre>
  
  <p>What the query should return is:</p>
  
  <pre><code>ID  C1  XmlData
  1   A   &lt;xmldata1&gt;
  2   A   &lt;xmldata2&gt;
  </code></pre>
  
  <p>Where <code>&lt;xmldata1&gt;</code> would be:</p>
  
  <pre><code>&lt;Root xmlns:xsd="w3.org/2001/XMLSchema" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:mst="microsoft.com/wsdl/types/"&gt;
    &lt;Child attrib="C1"&gt;
      &lt;ChildValue xsi:type="xsd:integer"&gt;1&lt;/ChildNode&gt;
    &lt;/Child&gt;
    &lt;Child attrib="C2"&gt;
      &lt;ChildNode xsi:type="xsd:integer"&gt;2&lt;/ChildNode&gt;
    &lt;/Child&gt;
  &lt;/Root&gt;
  </code></pre>
  
  <p>and <code>&lt;xmldata2&gt;</code> would be:</p>
  
  <pre><code>&lt;Rootxmlns:xsd="w3.org/2001/XMLSchema" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:mst="microsoft.com/wsdl/types/"&gt;
    &lt;Child attrib="C3"&gt;
      &lt;ChildNode xsi:type="xsd:integer"&gt;3&lt;/ChildNode&gt;
    &lt;/Child&gt;
  &lt;/Root&gt;
  </code></pre>
  
  <p>I have a good reference how to build the xml from <a href="https://stackoverflow.com/questions/10357203/selecting-few-columns-of-a-result-set-as-xml">this SO question</a> but I'm not able to put in the namespaces. If this is possible how to do it?</p>
  
  <p>Edit:
  I've used following query attempting to get the required result:
      select 1 ID, 'A' C1, 1 X1C1, 2 X1C2, 3 X2C3
      into #t</p>
  
  <pre><code>;with xmlnamespaces('w3.org/2001/XMLSchema' as xsd, 'w3.org/2001/XMLSchema-instance' as xsi, 'microsoft.com/wsdl/types/' as mst)
  select ID, C1, (select (SELECT 'C1' "@attrib", 'xsd:integer' "ChildValue/@xsi:type",t.X1C1 as 'ChildValue' FOR XML PATH('Child'), type),(SELECT 'C2' "@name", 'xsd:integer' "ChildValue/@xsi:type", t.X1C2 as 'ChildValue' FOR XML PATH('Child'), type) FOR XML PATH('Root'), type) as property_data 
  FROM #t t
  
  drop table #t
  </code></pre>
  
  <p>Here is the output of its xml part:</p>
  
  <pre><code>&lt;Root xmlns:mst="microsoft.com/wsdl/types/" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:xsd="w3.org/2001/XMLSchema"&gt;
  &lt;Child xmlns:mst="microsoft.com/wsdl/types/" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:xsd="w3.org/2001/XMLSchema" attrib="C1"&gt;
      &lt;ChildValue xsi:type="xsd:integer"&gt;1&lt;/ChildValue&gt;
    &lt;/Child&gt;
    &lt;Child xmlns:mst="microsoft.com/wsdl/types/" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:xsd="w3.org/2001/XMLSchema" name="C2"&gt;
      &lt;ChildValue xsi:type="xsd:integer"&gt;2&lt;/ChildValue&gt;
    &lt;/Child&gt;
  &lt;/Root&gt;
  </code></pre>
  
  <p>I can't get rid of the namespaces in the <code>Child</code> node.</p>