問(wèn)題描述
我有一個(gè)存儲(chǔ)的 SQL 過(guò)程，它已經(jīng)聲明了具有這種結(jié)構(gòu)的 xml 變量
I have a stored SQL procedure, which has declared xml variable with this kind of structure
<a>
<b>4</b>
<b>18</b>
<b>2</b>
</a>
我需要為這個(gè) XML 數(shù)據(jù)做一些類(lèi)似 UPDATE a SET b=b-1 WHERE b>@MyIntVariable 的事情.在 MS Transact SQL 中執(zhí)行此操作的最佳方法是什么?
I need to do something like UPDATE a SET b=b-1 WHERE b>@MyIntVariable for this XML data. What is the best way to do it in MS Transact SQL?
推薦答案
modify 函數(shù)最適合處理 xml 數(shù)據(jù).請(qǐng)參閱 http://msdn.microsoft.com/en-us/library/ms190675(v=sql.105).aspx.
The modify function would be the most appropriate for manipulating xml data. See http://msdn.microsoft.com/en-us/library/ms190675(v=sql.105).aspx.
DECLARE @NUM INT = 10
DECLARE @xml XML = N'
<a>
<b>4</b>
<b>18</b>
<b>2</b>
</a>
';
SELECT @XML;
DECLARE @COUNT INT
SET @COUNT = @XML.value ('count(/a/b)', 'int');
WHILE @COUNT > 0
BEGIN
SET @XML.modify('replace value of (/a/b[sql:variable("@COUNT")]/text())[1] with
(
if ((/a/b[sql:variable("@COUNT")])[1] > sql:variable("@NUM")) then
(/a/b[sql:variable("@COUNT")])[1] - 1
else
(/a/b[sql:variable("@COUNT")])[1] cast as xs:double ?
)
')
SET @COUNT = @COUNT - 1;
END
SELECT @XML
這篇關(guān)于用xml到sql按條件更新所有節(jié)點(diǎn)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！