CODEWITHSUNDEEP
sqlxmlsql-server-2008xquery
heymega
heymega

Reputation: 9411

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

Upvotes: 3

Views: 1530

Answers (2)

AlwaysLearning
AlwaysLearning

Reputation: 8819

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>

Upvotes: 1

Ben
Ben

Reputation: 35633

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.

Upvotes: 0

Related Questions