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 更新表的每一行，以便在 TextReading 名稱之后使用名為 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

uj5u.com熱心網友回復：

請嘗試以下解決方案。

它使用 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;

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/337524.html

標籤：sql-server xml 查询语句查询 xquery-sql

上一篇：Next.js、Firebase、未處理的運行時錯誤-TypeError：無法讀取未定義的屬性“initializeApp”

下一篇：如何在powershell的Compare-Object中訪問我的自定義側面指標？