如果不存在 SQL Server XML 添加属性 [英] SQL Server XML add attribute if non-existent

问题描述

如果属性不存在，我正在尝试添加该属性.应该很简单，但我是 XML XPath/XQuery/等的新手，所以请原谅我的无知.

I am trying to add an attribute if it does not exist. It should be simple, but I am new to XML XPath/XQuery/etc., so excuse my ignorance.

我希望能够传递 XML 数据并对其进行修改...

I want to be able to pass XML data and modify it...

ALTER FUNCTION [dbo].[ConvertXmlData](@xmlData XML)
RETURNS XML
AS
BEGIN
  RETURN @xmlData.<something here>
END

如果我传递如下数据:

<something> 
   this is sample data <xxx id="1"/> and <xxx id="2" runat="server" />. More <yyy id="3" />
</something>

我愿意

<something>
this is sample data <xxx id="1" runat="server" /> and <xxx id="2" runat="server" />. More <yyy id="3" />

</something>

而不是:

<something>
this is sample data <xxx id="1" runat="server" /> and <xxx id="2" runat="server" runat="server"/>. More <yyy id="3" />
</something>

推荐答案

你可以做

SET @xmlData.modify('insert attribute runat { "server" } into descendant::xxx[not(@runat)][1]');

然而，这只会改变没有 runat 属性的第一个 xxx 后代，至少在 SQL Server 2005 中你一次只能修改一个节点.

This will however only change the first xxx descendant not having a runat attribute, at least with SQL Server 2005 you can only modify one node at a time.

也许将上述内容与 WHILE 结合起来有帮助，例如

Maybe combining the above with a WHILE helps e.g.

WHILE @xmlData.exist('descendant::xxx[not(@runat)]') = 1
BEGIN
  SET @xmlData.modify('insert attribute runat { "server" } into descendant::xxx[not(@runat)][1]');
END

我无权访问 SQL Server 2008 R2，但我认为修改仍然一次仅限于一个节点，因此您可以尝试

I don't have access to SQL Server 2008 R2 but I think the modify is still limited to one node at a time so you could try

ALTER FUNCTION [dbo].[ConvertXmlData](@xmlData XML)
RETURNS XML
AS
BEGIN
    WHILE @xmlData.exist('descendant::xxx[not(@runat)]') = 1
    BEGIN
      SET @xmlData.modify('insert attribute runat { "server" } into descendant::xxx[not(@runat)][1]');
    END
  RETURN @xmlData
END

这篇关于如果不存在 SQL Server XML 添加属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！