Add a namespace on an xml generated by a query

I try to add a namespace on xml using WITH XMLNAMESPACES.

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...

I provided an example:

Queries for creating the table and the data:

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], [开发者_如何学GoName]) VALUES (3, N'Brian')
SET IDENTITY_INSERT [dbo].[tblTest] OFF

I generate the xml with these queries:

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');

Xml generated:

<Names xmlns="http://www.mynamespace.com">
  <DataRows xmlns="">
    <Row>
      <Id>1</Id>
      <Name>Barak</Name>
    </Row>
    <Row>
      <Id>2</Id>
      <Name>Nicolas</Name>
    </Row>
    <Row>
      <Id>3</Id>
      <Name>Brian</Name>
    </Row>
  </DataRows>
</Names>

So, I try this as well:

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')

the xml generated is now:

<Names xmlns="http://www.mynamespace.com">
  <DataRows>
    <Row xmlns="http://www.mynamespace.com">
      <Id>1</Id>
      <Name>Barak</Name>
    </Row>
    <Row xmlns="http://www.mynamespace.com">
      <Id>2</Id>
      <Name>Nicolas</Name>
    </Row>
    <Row xmlns="http://www.mynamespace.com">
      <Id>3</Id>
      <Name>Brian</Name>
    </Row>
  </DataRows>
</Names>

Daniel, the xmlns="" on the <DataRows> element means, set the default namespace for <DataRows> and all descendants to no namespace.

In other words, if the xmlns="" were not there, the whole XML tree would be in the http://www.mynamespace.com namespace. (Because namespace declarations are inherited, until overridden.) And that's probably what you wanted. But SQL Server thinks you wanted only the <Names> element to be in that namespace. So it is "helpfully" removing the default namespace for all descendant elements.

The solution, then, is to tell SQL Server that all the elements, not just <Names>, should be in the http://www.mynamespace.com namespace.

(If you ask me how to do that, the answer is I don't know SQL Server XML features that well. But maybe clarifying what's happening and what needs to happen will help you figure out how to make it happen.)

Update in light of newly posted query and output:

@Daniel, your output is now technically correct. All the output elements are in the http://www.mynamespace.com namespace. The xmlns="http://www.mynamespace.com" declarations on the <Row> elements are redundant... they don't change the namespace of any element.

You may not like them the extra declarations, but they should not make any difference to any downstream XML tools.

If you want to remove them, and if you can't do that by tweaking the SQL query, you could run the resulting XML through an XSLT stylesheet. Even an identity transformation will probably get rid of the redundant namespace declarations, I believe.