在sql脚本中的两个XML文件之间交换数据? [英] Swapping data between two XML files in sql script?

问题描述

我的一个数据库表中有一个包含 xml 文件的列.但是，这些文件中包含需要交换的标签，有没有办法用 sql 脚本自动执行此操作?

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>

标签需要交换.

推荐答案

您需要分两步完成.首先从另一个节点添加位置节点，然后删除已经存在的位置节点.

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 小提琴

这篇关于在sql脚本中的两个XML文件之间交换数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！