sql-server - 用Transact-SQL替换所有记录中的多个XML属性值？

Question

问题

我的MSSQL数据库有一个表records和一个XML列data，其用法如下：

<record id="1">
  <field tag="DI" occ="1" lang="de-DE">Höhe</field>
  <field tag="DI" occ="1" lang="en-GB">height</field>
  <field tag="WA">173</field>
  <field tag="EE">cm</field>
  <field tag="DI" occ="2" lang="de-DE">Breite</field>
  <field tag="DI" occ="2" lang="en-GB">width</field>
  <field tag="WA">55</field>
  <field tag="EE">cm</field>
</record>

我想一次更新表中的所有行，将 /record/field/@lang替换为当前 en-US所在的 en-GB（所有具有该属性值的元素）。

已经尝试过类似...

declare @i int;
declare @xml xml;
set @xml = (select top(1) [data] from [my-database].[dbo].[records]);
select @i = @xml.value('count(/record/field[lang="en-GB"])', 'int')

while @i > 0
begin
    set @xml.modify('
            replace value of
                (/record/field[lang="en-GB"]/text())[1]
            with "en-US"
    ')

    set @i = @i - 1
end

select @xml;

...但是它返回的数据不变，并且仅在选择了单行的情况下有效。如何进行这项工作并一次性更新所有行？

解

我最终按照Shnugo的建议使用了XQuery。我的概括查询如下：

UPDATE [my-database].[dbo].[records] SET data = data.query(N'
    <record>
    {
        for $attr in /record/@*
        return $attr
    }
    {
        for $fld in /record/*
        return
        if (local-name($fld) = "field")
        then <field>
        {
            for $attr in $fld/@*
            return
            if (local-name($attr) = "lang" and $attr = "en-GB")
            then attribute lang {"en-US"}
            else $attr
        }
        {$fld/node()}
        </field>
        else $fld
    }
    </record>
')
FROM [my-database].[dbo].[records]
WHERE [data].exist('/record/field[@lang="en-GB"]') = 1;
SELECT * FROM [my-database].[dbo].[records]

似乎最上面的节点 <record>的名称需要进行硬编码，因为MSSQL服务器不支持动态元素名称（也不支持属性名称）。它的属性以及 <field>以外的所有子元素都将使用上述代码自动复制。

Answer 1

我将其作为第二个答案，因为它采用了完全不同的方法。以下代码将.query()与FLWOR查询结合使用，按原样读取XML，但是当内容为lang时更改属性en_GB：

DECLARE @xml XML=
N'<record id="1">
  <field tag="DI" occ="1" lang="de-DE">Höhe</field>
  <field tag="DI" occ="1" lang="en-GB">height</field>
  <field tag="WA">173</field>
  <field tag="EE">cm</field>
  <field tag="DI" occ="2" lang="de-DE">Breite</field>
  <field tag="DI" occ="2" lang="en-GB">width</field>
  <field tag="WA">55</field>
  <field tag="EE">cm</field>
</record>';

查询

SELECT @xml.query
(N'
    <record id="{/record/@id}">
    {
        for $fld in /record/field
        return <field>
        {
            for $attr in $fld/@*
            return
            if(local-name($attr)="lang" and $attr="en-GB") then attribute lang {"en-US"}
            else $attr
        }
        {$fld/text()}
        </field>
    }
    </record>
')

结果

<record id="1">
  <field tag="DI" occ="1" lang="de-DE">Höhe</field>
  <field tag="DI" occ="1" lang="en-US">height</field>
  <field tag="WA">173</field>
  <field tag="EE">cm</field>
  <field tag="DI" occ="2" lang="de-DE">Breite</field>
  <field tag="DI" occ="2" lang="en-US">width</field>
  <field tag="WA">55</field>
  <field tag="EE">cm</field>
</record>

更新：这也适用于所有表的行：

尝试以下操作一次更新一个完整的表：

DECLARE @tbl TABLE(ID INT IDENTITY,YourXml XML)
INSERT INTO @tbl VALUES
(
N'<record id="1">
  <field tag="DI" occ="1" lang="de-DE">Höhe</field>
  <field tag="DI" occ="1" lang="en-GB">height</field>
  <field tag="WA">173</field>
  <field tag="EE">cm</field>
  <field tag="DI" occ="2" lang="de-DE">Breite</field>
  <field tag="DI" occ="2" lang="en-GB">width</field>
  <field tag="WA">55</field>
  <field tag="EE">cm</field>
</record>'
)
,(
N'<record id="2">
  <field tag="DI" occ="1" lang="de-DE">Höhe</field>
  <field tag="DI" occ="1" lang="en-GB">height</field>
  <field tag="WA">173</field>
  <field tag="EE">cm</field>
  <field tag="DI" occ="2" lang="de-DE">Breite</field>
  <field tag="DI" occ="2" lang="en-GB">width</field>
  <field tag="WA">55</field>
  <field tag="EE">cm</field>
</record>'
);

UPDATE @tbl SET YourXml=YourXml.query
(N'
    <record id="{/record/@id}">
    {
        for $fld in /record/field
        return <field>
        {
            for $attr in $fld/@*
            return
            if(local-name($attr)="lang" and $attr="en-GB") then attribute lang {"en-US"}
            else $attr
        }
        {$fld/text()}
        </field>
    }
    </record>
');

SELECT * FROM @tbl