SQLServer : XMLの値と属性を「replace value of」で改変する

いままでSQLServer の XML操作については、SELECT + FOR XML PATHでXMLを作成したり、OPENXMLでSELECTしたりしかしてこなかった僕です。

まぁ必ずではなかったのですが、ストアド上でXMLの値とか属性とかを個別に操作したいという案件が発生。「SQLServer XML 操作」とか調べてみると、上述している案件ばかりでてくるので、DOM操作でやろうかなとあきらめてたところ、それっぽいドキュメントを発見。

docs.microsoft.com

何はともあれ、Management Studioで以下のSQLを試してみた。

XML型の@xmlを宣言して、2番目の<TEST>タグの値と属性を5に変更しました。

DECLARE @xml XML = '<ROOT><TEST attr="10">1</TEST><TEST attr="20">2</TEST></ROOT>'

SELECT @xml

SET @xml.modify('replace value of (/ROOT/TEST[2]/text())[1] with "5"')

SELECT @xml

SET @xml.modify('replace value of (/ROOT/TEST[2]/@attr)[1] with "5"')

SELECT @xml

結果が下図。値も属性も改変できることを確認できた。めでたしめでたし。