如何在 SQL Server 2014 的 XML 根目录中添加 xmlns

2022-01-16 00:00:00 xml sql sql-server sql-server-2008-r2 sql-server-2014

我正在尝试添加 xmlns MsgDtTm &MessageIdSQL Server 2014 中 XML 根元素中的属性.我正在尝试:

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

得到这个结果

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

我在 Default 元素中获得了空白的 xmlns 属性.我希望 Person 元素中的 xmlns 不在 Default 元素中.我的预期结果如下:

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>

如果我使用 ;WITH XMLNAMESPACES ('http://abc.go.com' as f) 那么它将在 root 中，但结果我会得到 xmlns:f=...".我不想附加 :objectOfXMLNAMESPACES，我只想要 xmlns.

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.

推荐答案

SQL Server 一遍又一遍地为每个子选择添加命名空间，这是一个非常烦人的行为.

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

您会在 SO 上找到很多解决方法，有些使用丑陋的 NVARCHAR(MAX) 强制转换以在字符串基础上插入命名空间，其他使用或多或少复杂的方式.

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;

重要

请点击此链接，登录并投票.

这是一个持续年的众所周知的问题！..

相关文章