title: Add a namespace on an xml generated by a query
body: <p>I try to add a namespace on xml using WITH XMLNAMESPACES.</p>
  
  <p>When I execute my queries, 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...</p>
  
  <p>I provided an example:</p>
  
  <p>Queries for creating the table and the data:</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[tblTest](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nvarchar](30) NOT NULL,
   CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED 
  (
      [Id] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  SET IDENTITY_INSERT [dbo].[tblTest] ON
  INSERT [dbo].[tblTest] ([Id], [Name]) VALUES (1, N'Barack')
  INSERT [dbo].[tblTest] ([Id], [Name]) VALUES (2, N'Nicolas')
  INSERT [dbo].[tblTest] ([Id], [Name]) VALUES (3, N'Brian')
  SET IDENTITY_INSERT [dbo].[tblTest] OFF
  </code></pre>
  
  <p>I generate the xml with these queries:</p>
  
  <pre><code>DECLARE @Xml xml
  SET @Xml = (SELECT Id, Name
              FROM   dbo.tblTest 
              FOR XML PATH('Row'), ROOT('DataRows'));
  
  WITH XMLNAMESPACES (DEFAULT 'http://www.mynamespace.com')
  SELECT  @Xml FOR XML PATH('Names');
  </code></pre>
  
  <p>Xml generated:</p>
  
  <pre><code>&lt;Names xmlns="http://www.mynamespace.com"&gt;
    &lt;DataRows xmlns=""&gt;
      &lt;Row&gt;
        &lt;Id&gt;1&lt;/Id&gt;
        &lt;Name&gt;Barak&lt;/Name&gt;
      &lt;/Row&gt;
      &lt;Row&gt;
        &lt;Id&gt;2&lt;/Id&gt;
        &lt;Name&gt;Nicolas&lt;/Name&gt;
      &lt;/Row&gt;
      &lt;Row&gt;
        &lt;Id&gt;3&lt;/Id&gt;
        &lt;Name&gt;Brian&lt;/Name&gt;
      &lt;/Row&gt;
    &lt;/DataRows&gt;
  &lt;/Names&gt;
  </code></pre>
  
  <p>So, I try this as well:</p>
  
  <pre><code>DECLARE @Xml xml
  
  ;WITH XMLNAMESPACES (DEFAULT 'http://www.mynamespace.com')
  SELECT @Xml = (SELECT Id, Name
      FROM   dbo.tblTest 
      FOR XML PATH('Row'), TYPE);
  
  ;WITH XMLNAMESPACES (DEFAULT 'http://www.mynamespace.com')
  SELECT @Xml
  FOR XML PATH('DataRows'), ROOT('Names')
  </code></pre>
  
  <p>the xml generated is now:</p>
  
  <pre><code>&lt;Names xmlns="http://www.mynamespace.com"&gt;
    &lt;DataRows&gt;
      &lt;Row xmlns="http://www.mynamespace.com"&gt;
        &lt;Id&gt;1&lt;/Id&gt;
        &lt;Name&gt;Barak&lt;/Name&gt;
      &lt;/Row&gt;
      &lt;Row xmlns="http://www.mynamespace.com"&gt;
        &lt;Id&gt;2&lt;/Id&gt;
        &lt;Name&gt;Nicolas&lt;/Name&gt;
      &lt;/Row&gt;
      &lt;Row xmlns="http://www.mynamespace.com"&gt;
        &lt;Id&gt;3&lt;/Id&gt;
        &lt;Name&gt;Brian&lt;/Name&gt;
      &lt;/Row&gt;
    &lt;/DataRows&gt;
  &lt;/Names&gt;
  </code></pre>