SQL - Blank default namespaces

Question

I have a relation which has an XML column storing data in the following structure

<Report id="b5d9b8da-7af4-4257-b825-b28af91dd833">
    <CreatedDate>04-12-2012</CreatedDate>
    <LastUpdated>04-12-2012</LastUpdated>
    <Reference>abc123</Reference>
</Report>

I'm writing a stored procedure to retrieve all reports and join them and wrap them in a root node called reports. I have the following so far;

WITH XMLNAMESPACES(DEFAULT 'http://www.defaultnamespace.com/1.0')
        SELECT
    @Xml = 
    (

            SELECT
                (
                    SELECT xml.query('.')
                    FROM
                        [database].[Reports]
                    WHERE
                        ClientId = @clientId
                    FOR XML PATH(''),
                    TYPE
                )
            FOR XML PATH('Reports'),
            TYPE
        )

Whilst this returns all the reports in the right format, there exists a blank default namespace on the report element like the following;

<Reports xmlns="http://www.defaultnamespace.com/1.0">
<Report  xmlns="" id="b5d9b8da-7af4-4257-b825-b28af91dd833">
    <CreatedDate>04-12-2012</CreatedDate>
    <LastUpdated>04-12-2012</LastUpdated>
    <Reference>abc123</Reference>
</Report>
</Reports>

Could someone explain a suitable way of excluding the namespace on the report element?

Any help is greatly appreciated guys :)

Answer 1

It's a little messy and probably not very efficient but you can redefine namespaces with an XQuery over your intermediate XML.

Instead of using SQL Server's WITH XMLNAMESPACES you declare the default namespace in XQuery, for example...

if object_id(N'Reports') is not null drop table [Reports];
go
create table [Reports] (
    [ClientId] int not null,
    [xml] [xml] not null
)
go
insert [Reports] ([ClientID], [xml])
    values (1, N'<Report id="b5d9b8da-7af4-4257-b825-b28af91dd833">
    <CreatedDate>04-12-2012</CreatedDate>
    <LastUpdated>04-12-2012</LastUpdated>
    <Reference>abc123</Reference>
</Report>');
go
declare @clientId int = 1
select  (
    select [xml].query('/*:Report')
    from [Reports]
    where ClientId = @clientId
    for xml path('Reports'), type
    ).query('declare default element namespace "http://www.defaultnamespace.com/1.0";
    for $x in /*:Reports return
        <Reports>
        {
            for $y in $x/*:Report return
            <Report>
                {attribute id {$y/@id}}
                {element CreatedDate {$y/*:CreatedDate/text()}}
                {element LastUpdated {$y/*:LastUpdated/text()}}
                {element Reference {$y/*:Reference/text()}}
            </Report>
        }
        </Reports>')
go

This will return the following block of XML:

<Reports xmlns="http://www.defaultnamespace.com/1.0">
  <Report id="b5d9b8da-7af4-4257-b825-b28af91dd833">
    <CreatedDate>04-12-2012</CreatedDate>
    <LastUpdated>04-12-2012</LastUpdated>
    <Reference>abc123</Reference>
  </Report>
</Reports>