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