本文介紹了SQL:遞歸更新表中的XML列的處理方法,對大家解決問題具有一定的參考價值,需要的朋友們下面隨著小編來一起學習吧!
問題描述
我在SQL Server 2012中有一個表
使用以下列定義
CREATE TABLE [dbo].[tblStepList](
[ToDoId] [int] IDENTITY(1,1) NOT NULL,
[Data] [xml] NOT NULL
}
并且數據列是帶有
的XML
<Steplist>
<Step>
<StepId>e36a3450-1c8f-44da-b4d0-58e5bfe2a987</StepId>
<Rank>1</Rank>
<IsComplete>false</IsComplete>
<TextReadingName>bug-8588_Updated3</TextReadingName>
</Step>
<Step>
<StepId>4078c1b1-71ea-4578-ba61-d2f6a5126ba1</StepId>
<Rank>2</Rank>
<TextReadingName>reading1</TextReadingName>
</Step>
</Steplist>'
我想用新的XML更新表的每一行,以便在TextReadingID之后查找名為TextReadingID的新節點
我想插入一個名為TextReadingId值的新節點,我希望我的TextReadingId值是如下所示的數字
<Steplist>
<Step>
<StepId>e36a3450-1c8f-44da-b4d0-58e5bfe2a987</StepId>
<Rank>1</Rank>
<IsComplete>false</IsComplete>
<TextReadingName>bug-8588_Updated3</TextReadingName>
<TextReadingId>1</TextReadingId>
</Step>
<Step>
<StepId>4078c1b1-71ea-4578-ba61-d2f6a5126ba1</StepId>
<Rank>2</Rank>
<TextReadingName>reading1</TextReadingName>
<TextReadingId>1</TextReadingId>
</Step>
</Steplist>';
這就是我嘗試的,但它沒有按預期工作
DECLARE @i int;
SELECT
@i = s.data.value('count(/Steplist/Step)', 'nvarchar(max)')
FROM tblStepList s
SET data.modify('insert <TextReadingId>{sql:variable("@i")}</TextReadingId> as last into (/Steplist/Step[sql:variable("@i")])[1]')
print @i
End
推薦答案
請嘗試以下解決方案。
它正在使用XQuery及其FLWOR表達式。
SQL
USE tempdb;
GO
DROP TABLE IF EXISTS [dbo].[tblStepList];
CREATE TABLE [dbo].[tblStepList](
[ToDoId] [int] IDENTITY(1,1) NOT NULL,
[Data] [xml] NOT NULL
);
INSERT INTO dbo.tblStepList ([Data]) VALUES
(N'<Steplist>
<Step>
<StepId>e36a3450-1c8f-44da-b4d0-58e5bfe2a987</StepId>
<Rank>1</Rank>
<IsComplete>false</IsComplete>
<TextReadingName>bug-8588_Updated3</TextReadingName>
</Step>
<Step>
<StepId>4078c1b1-71ea-4578-ba61-d2f6a5126ba1</StepId>
<Rank>2</Rank>
<TextReadingName>reading1</TextReadingName>
</Step>
</Steplist>');
-- before
SELECT * FROM dbo.tblStepList;
UPDATE dbo.tblStepList
SET [Data] = [Data].query('<Steplist>
{
for $x in /Steplist/Step
let $pos := count(Steplist/*[. << $x]) + 1
return <Step>{$x/*,
if (not($x/TextReadingId)) then <TextReadingId>{$pos}</TextReadingId>
else ()}
</Step>
}
</Steplist>');
-- after
SELECT * FROM dbo.tblStepList;
這篇關于SQL:遞歸更新表中的XML列的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,