Iswar K Chettri Iswar K Chettri - 19 days ago 6
SQL Question

How to add xmlns in the root in xml - SQL Server 2014

I am trying to add

xmlns MsgDtTm
&
MessageId
attributes in root element of xml in sql server 2014. and I am trying this

declare @TEMP table (ID nvarchar(max), Name nvarchar(max))
declare @count int =0
WHILE @count < 4
BEGIN
declare @name nvarchar(20),@id nvarchar(max)
select @name= SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 7)
,@id= CHAR(ROUND(RAND() * 93 + 33, 0))
insert into @TEMP values(@id,@name)
set @count= @count +1
END

declare @msgId nvarchaR(24)
SET @msgId='11EXP'+REPLACE(convert(varchar(10),getdate(),103),'/','')+'1'
DECLARE @Xml xml
SET @Xml = (select * from @TEMP for xml path('DefaultName'), type)
;WITH XMLNAMESPACES (DEFAULT 'http://abc.go.com')
select GETDATE() as "@MsgDtTm"
,@msgId as "@MessageId"
,--'http://abc.go.com' as "@xmlns",
@Xml for xml path('Person')


and getting this result

<Person xmlns="http://abc.go.com" MsgDtTm="2016-11-21T15:13:10.440" MessageId="11EXP211120161">
<DefaultName xmlns="">
<ID>y</ID>
<Name>7BDCB6</Name>
</DefaultName>
<DefaultName xmlns="">
<ID>2</ID>
<Name>F8E997</Name>
</DefaultName>
<DefaultName xmlns="">
<ID>"</ID>
<Name>01E71C</Name>
</DefaultName>
<DefaultName xmlns="">
<ID>k</ID>
<Name>E4059A</Name>
</DefaultName>
</Person>


I am getting the blank
xmlns
attribute in
Default
element. I want
xmlns
in
Person
element not in
Default
element. My expected result is as follows:

<Person xmlns="http://abc.go.com" MsgDtTm="2016-11-21T15:13:10.440" MessageId="11EXP211120161">
<DefaultName>
<ID>y</ID>
<Name>7BDCB6</Name>
</DefaultName>
<DefaultName>
<ID>2</ID>
<Name>F8E997</Name>
</DefaultName>
<DefaultName>
<ID>"</ID>
<Name>01E71C</Name>
</DefaultName>
<DefaultName>
<ID>k</ID>
<Name>E4059A</Name>
</DefaultName>
</Person>


if I use
;WITH XMLNAMESPACES ('http://abc.go.com' as f)
then it will be in root but in result I will get
xmlns:f="..."
. I dont want to append
:objectOfXMLNAMESPACES
I just want xmlns.
Any solution are highly appreciated.

Answer

It is a very annoying behaviour, that SQL Server adds namespaces to each sub-select over and over.

You will find a lot of workarounds here on SO, some use an ugly cast to NVARCHAR(MAX) to insert the namespace on string base, other use more or less complicated ways.

For you the simplest should be this:

DECLARE @xml XML;
;WITH XMLNAMESPACES (DEFAULT 'http://abc.go.com')
SELECT @xml=
    (
        SELECT ID,Name
        FROM @TEMP
        FOR XML PATH('DefaultName'),ROOT('Person'),TYPE
    );
DECLARE @d DATETIME=GETDATE();
DECLARE @mid VARCHAR(100)='11EXP'+REPLACE(convert(varchar(10),getdate(),103),'/','')+'1';

SET @xml.modify('insert (attribute MsgDtTm {sql:variable("@d")}
                        ,attribute MessageId {sql:variable("@mid")}) into (/*:Person)[1]');

SELECT @xml;

IMPORTANT

Please follow this link, sign in, and vote up.

This is a well known issue lasting for years!!..