按屬性查找所有xml元素并使用t-sql更改它們的值

如何更新單個檔案中的多個 XML 元素？

例如，如果我有下面的 XML，并且我想將任何具有屬性Store_ID="13"的元素更改為具有Store_ID="99".

declare @x xml 
select @x = N'
    <Games>
      <Game>
        <Place City="LAS" State="NV" />
        <Place City="ATL" State="GA" />
        <Store Store_ID="12" Price_ID="162" Description="Doom" />
        <Store Store_ID="12" Price_ID="575" Description="Pac-man" />
        <Store Store_ID="13" Price_ID="167" Description="Demons v3" />
        <Store Store_ID="13" Price_ID="123" Description="Whatever" />
      </Game>
    </Games>
'
select @x 

我可以像這樣使用 SQL 找到所有元素：

select t.c.query('.') 
from @x.nodes('.//*[@Store_ID="13"]') as t(c)

要僅更新第一個元素，我可以像這樣進行更新（或將“1”更改為“2”以更新第二個元素等）：

SET @x.modify('  
  replace value of (.//*[@Store_ID="13"]/@Store_ID)[1]  
  with "99"  
');  
SELECT @x;

檔案replace value of說我一次只能更新一個節點：

It must identify only a single node ... When multiple nodes are selected, an error is raised.

那么如何更新多個元素呢？我可以想象首先查詢以找到有多少個元素，然后回圈并@x.modify()為每個元素呼叫一次，傳遞一個索引引數......但是 a) 感覺不對 b) 當我嘗試它時我得到一個錯誤

-- Find how many elements there are with the attribute to update
declare @numberOfElements int 
select @numberOfElements = count(*) 
from ( 
    select element = t.c.query('.') 
    from @x.nodes('.//*[@Store_ID="13"]') as t(c)
) x 

declare @i int = 1
declare @query nvarchar(max) 

-- loop through and update each one 
while @i <= @numberOfElements begin 

    SET @x.modify('  
      replace value of (.//*[@Store_ID="13"]/@Store_ID)[sql:variable("@i")]  
      with "99"  
    ');  
    set @i = @i   1 ;  
end 

SELECT @x;

運行上面的sql給我錯誤：

Msg 2337, Level 16, State 1, Line 31
XQuery [modify()]: The target of 'replace' must be at most one node, found 'attribute(Store_ID,xdt:untypedAtomic) *'

此外，如果我想對一個表中的許多行運行此操作，其中 XML 資料存盤在一個列中，它會變得非常程式化。

否則我可以強制轉換為nvarchar(max)并對其進行字串操作，然后強制轉換回xml. 同樣，這感覺很糟糕，但也意味著我無法使用 xml 運算式來查找要更新的元素。

uj5u.com熱心網友回復：

如果提前定義了 XML 結構并且眾所周知，那么您可以XML.modify()通過僅重新創建 XML 并應用行內編輯來避免這些限制。

示例 1：使用 .nodes()、.values() 和 FOR XML 重建 XML

update dbo.Example
set x = (
    select
        (
            select Place.value(N'@City', N'nvarchar(max)') as [@City],
                Place.value(N'@State', N'nvarchar(max)') as [@State]
            from Game.nodes('Place') as n(Place)
            for xml path(N'Place'), type
        ),
        (
            select
                case when (StoreID = 13) then 99 else StoreID end as [@Store_ID],
                PriceID as [@Price_ID],
                [Description] as [@Description]
            from Game.nodes('Store') as n(Store)
            cross apply (
                select  Store.value(N'@Store_ID', N'int'),
                    Store.value(N'@Price_ID', N'int'),
                    Store.value(N'@Description', N'nvarchar(max)')
            ) attributes(StoreID, PriceID, [Description])
            for xml path('Store'), type
        )
    from x.nodes(N'/Games/Game') Games(Game)
    for xml path(N'Game'), root(N'Games')
);

示例 2：使用 XQuery 重建 XML

update dbo.Example
set x = (
    select  x.query('
<Games>
    <Game>
        {
            for $place in /Games/Game/Place
                return $place
        }
        {
            for $store in /Games/Game/Store
                let $PriceID := $store/@Price_ID
                let $StoreID_Input := xs:integer($store/@Store_ID) 
                let $StoreID := if ($StoreID_Input != 13) then $StoreID_Input else 99
                let $Description := $store/@Description
                return <Store Store_ID="{$StoreID}" Price_ID="{$PriceID}" Description="{$Description}"/>
        }
    </Game>
</Games>')
);

uj5u.com熱心網友回復：

這是使用 XQuery 的另一種相對通用的方法。

資料庫

DECLARE @x XML =
N'<Games>
    <Game>
        <Place City="LAS" State="NV"/>
        <Place City="ATL" State="GA"/>
        <Store Store_ID="12" Price_ID="162" Description="Doom"/>
        <Store Store_ID="12" Price_ID="575" Description="Pac-man"/>
        <Store Store_ID="13" Price_ID="167" Description="Demons v3"/>
        <Store Store_ID="13" Price_ID="123" Description="Whatever"/>
    </Game>
</Games>';

DECLARE @oldId int = 13
    , @newId int = 99;

SET @x = @x.query('<Games><Game>
    {
        for $i in /Games/Game/*
        return if ($i[(local-name()="Store") and @Store_ID=sql:variable("@oldId")]) then
            element Store { attribute Store_ID {sql:variable("@newId")}, $i/@*[not(local-name()="Store_ID")]}
        else $i
    }
</Game></Games>');

-- test
SELECT @x;

輸出

<Games>
  <Game>
    <Place City="LAS" State="NV" />
    <Place City="ATL" State="GA" />
    <Store Store_ID="12" Price_ID="162" Description="Doom" />
    <Store Store_ID="12" Price_ID="575" Description="Pac-man" />
    <Store Store_ID="99" Price_ID="167" Description="Demons v3" />
    <Store Store_ID="99" Price_ID="123" Description="Whatever" />
  </Game>
</Games>

uj5u.com熱心網友回復：

根據這個答案，不可能在單個陳述句中更新多次，因此要使用 xml，您需要回圈并modify()重復呼叫，直到更新所有值。

我最初的回圈嘗試是錯誤的：只需modify()在第一個匹配元素上執行直到沒有更多：

while @x.exist('.//*[@Store_ID="13"]')=1
begin 

    SET @x.modify('  
      replace value of (.//*[@Store_ID="13"]/@Store_ID)[1]
      with "99"  
    ');  
end 

在我的簡單示例中，我使用了硬編碼值"13"，"99"但對于實際代碼，您可以使用sql:variable("@variableName")( ref ) 和sql:column("tableNameOrAlias.colName")( ref )，例如

declare @oldId int = 13
    ,   @newId int = 99 

while @x.exist('.//*[@Store_ID=sql:variable("@oldId")]')=1
begin 

    SET @x.modify('  
      replace value of (.//*[@Store_ID=sql:variable("@oldId")]/@Store_ID)[1]
      with sql:variable("@newId")  
    ');  
end 

標籤：sql服务器XMLsql-server-2019

上一篇：如何在XSL中選擇以特殊字符開頭的句子？

下一篇：優化聚合管道