問題描述

我的一個數(shù)據(jù)庫表中有一個包含 xml 文件的列.但是，這些文件中包含需要交換的標簽，有沒有辦法用 sql 腳本自動執(zhí)行此操作?

I have a column in one of my data base tables that holds xml files. However, these files have tags inside them that need to be swapped, is there a way to automate this with a sql script?

<ViewcenterLayout>
  <viewcenter_config>
    <gl_type>EStop</gl_type>
    <data_access>
      <access_as>INPUT</access_as>
      <access_id>1391</access_id>
      <parent_id>0</parent_id>
      <server_id>17</server_id>
      <subsystem>0</subsystem>
    </data_access>
    <data>
      <dimension x="1" y="1" z="1" />
      <curve_info ir="0" or="0" degree="0" />
      <position x="416.96044921875" y="24.0833339691162" z="563.815856933594" />
      <rotation x="0" y="180" z="0" />
      <color>FFD3D3D3</color>
      <is_position_relative>false</is_position_relative>
    </data>
  </viewcenter_config>
</ViewcenterLayout>

這些是文件的外觀和

 <position>

標簽需要交換.

推薦答案

您需要分兩步完成.首先從另一個節(jié)點添加位置節(jié)點，然后刪除已經(jīng)存在的位置節(jié)點.

You need to do this in two steps. First add the position node from the other node and then remove the position node that was already there.

declare @PK1 varchar(11);
declare @PK2 varchar(11);

set @PK1 = 'cos190101-1';
set @PK2 = 'cos190101-2';

-- Add the new position node after the one that already exists
update T
set X.modify('insert sql:column("Pos") after (ViewcenterLayout/viewcenter_config/data/position)[1]')
from (
     select T.X.query('ViewcenterLayout/viewcenter_config/data/position') as Pos,
            case T.PK when @PK1 then @PK2 else @PK1 end as PK
     from T
     where T.PK in (@PK1, @PK2)
     ) as S
where T.PK = S.PK;

-- Delete the first position node
update T
set X.modify('delete (ViewcenterLayout/viewcenter_config/data/position)[1]')
where T.PK in (@PK1, @PK2);

SQL 小提琴

這篇關(guān)于在sql腳本中的兩個XML文件之間交換數(shù)據(jù)?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！