SQL – Blank default namespaces

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

Your issue is that the column was not stored with a default namespace of “http://www.defaultnamespace.com/1.0”.

So the logical name of your tag is NS = “”, name =Report.

SQL Server is being absolutely correct.

What you would like to do is to say

by the way, that XML data I stored, I want you to rewrite every tag from the “” namespace logically attached to every name, to a tag of the same relative name in the “http://www.defaultnamespace.com/1.0” namespace, and then make that the default namespace

AFAIK, You can’t do that (but if you find a way let me know!). The closest you can get is cast it to nvarchar(max) with no namespace, then cast it back again with the desired namespace applied.