Swapping data between two XML files in sql script?

108 views Asked by At

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>

These are how the files look and the

 <position>

tags need to be swapped.

1

There are 1 answers

0
Mikael Eriksson On BEST ANSWER

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 Fiddle