title: How to remove unnecessary blank namespace and add namespace prefix in tSQL
body: <p>When I run my Query the namespace is added with the root element but with the second element I have xmlns="" as well... and I would like to remove that... and I need to add namespace prefix to some elements.</p>
  
  <p>I provided an example:</p>
  
  <pre><code>declare @xml_h as xml
  declare @xml_d as xml
  
  select @xml_h = (
      select NewID() as [UID]
      for XML PATH('Head')
      )
  
  select @xml_d = (
      select NULL, 
      (
          select 'V00001' as [ID],
              'Some name' as [Name]
          for XML PATH('SubData'), TYPE
      )
      for XML PATH('Data')
      )
  
  ;with XmlNameSpaces (DEFAULT 'uri_1', 'uri_2' as t)
  select @xml_h, @xml_d for XML PATH(''), ROOT('ROOT')
  </code></pre>
  
  <p>The result being:</p>
  
  <pre><code>&lt;ROOT xmlns:t="uri_2" xmlns="uri_1"&gt;
      &lt;Head xmlns=""&gt;
          &lt;UID&gt;B12B8836-2061-4934-AC06-67D34579D1A6&lt;/UID&gt;
      &lt;/Head&gt;
      &lt;Data xmlns=""&gt;
          &lt;SubData&gt;
              &lt;ID&gt;V00001&lt;/ID&gt;
              &lt;Name&gt;Some name&lt;/Name&gt;
          &lt;/SubData&gt;
      &lt;/Data&gt;
  &lt;/ROOT&gt;
  </code></pre>
  
  <p>But I need this:</p>
  
  <pre><code>&lt;ROOT xmlns:t="uri_2" xmlns="uri_1"&gt;
      &lt;Head&gt;
          &lt;t:UID&gt;B12B8836-2061-4934-AC06-67D34579D1A6&lt;/t:UID&gt;
      &lt;/Head&gt;
      &lt;Data&gt;
          &lt;t:SubData&gt;
              &lt;t:ID&gt;V00001&lt;/t:ID&gt;
              &lt;t:Name&gt;Some name&lt;/t:Name&gt;
          &lt;/t:SubData&gt;
      &lt;/Data&gt;
  &lt;/ROOT&gt;
  </code></pre>
  
  <p>Is there any way to remove unnecessary blank namespace and add namespace prefix?</p>
  
  <p>thanks</p>