How to add xmlns in the root in XML in SQL Server 2014

Question

I am trying to add xmlns MsgDtTm & MessageIdattributes in root element of XML in SQL Server 2014. 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 don't want to append :objectOfXMLNAMESPACES, I just want xmlns.

Answer 1

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