XML replace node

• LOOKUP_BI-756009

  SSCertifiable

  Points: 7286

  More actions

  June 19, 2018 at 12:49 pm

  #406728

  ----I have the following xml structure

  ----I need to replace the entire <select> node IN @option_xml

  ----with entirely new values for each child node within it by matching on bn_id value FROM TABLE @opt_xml

  ----Prepopulate table

  DECLARE @opt_xml TABLE (bn_id INT, select_t varchar(max), select_k varchar(max))

  INSERT INTO @opt_xml (bn_id , select_t , select_k)

  SELECT 11,'L','T'

  UNION

  SELECT 12,'X','M'

  
  

  SELECT * FROM @opt_xml

  DECLARE @option_xml XML='<bd>

  <cat>

  <grp>

  <ben>

  <bn_id>11</bn_id>

  <select>

  <select_t />

  <select_k>F</select_k>

  </select>

  </ben>

  <ben>

  <bn_id>12</bn_id>

  <select>

  <select_t>G</select_t>

  <select_k>F</select_k>

  </select>

  </ben>

  </grp>

  </cat>

  </bd>'

  SELECT @option_xml AS BEFORE

  
  

  
  

  DECLARE @bn_id INT ,@select_t xml, @select_k xml

  DECLARE db_cursor CURSOR FOR

  SELECT bn_id, CONVERT(XML,(SELECT ISNULL(select_t,''))), CONVERT(XML,(SELECT ISNULL(select_k,''))) FROM @opt_xml

  OPEN db_cursor

  FETCH NEXT FROM db_cursor INTO @bn_id ,@select_t , @select_k

  WHILE @@FETCH_STATUS = 0

  
  

  BEGIN

  
  

  --Try to delete each child attribute/node and add it back

  SET @option_xml.modify( 'delete (/bd/cat/grp/ben/select/select_t)' )

  
  

  --Add a new attribute/node based on @bn_id

  
  

  SET @option_xml.modify('insert sql:variable("@select_t")

  
  

  after (/bd/cat/grp/ben/select/select_t/text()[. >> (/bd/cat/grp/ben/ben_id[.=sql:variable("@bn_id")])[1]])[1]')

  
  

  FETCH NEXT FROM db_cursor INTO @bn_id ,@select_t , @select_k

  
  

  END

  
  

  CLOSE db_cursor;

  
  

  DEALLOCATE db_cursor;

  
  

  SELECT @option_xml AS AFTER

  
  

  ----THE END RESULTS SHOULD BE

  '<bd>

  <cat>

  <grp>

  <ben>

  <bn_id>11</bn_id>

  <select>

  <select_t>L</select_t>

  <select_k>T</select_k>

  </select>

  </ben>

  <ben>

  <bn_id>12</bn_id>

  <select>

  <select_t>X</select_t>

  <select_k>M</select_k>

  </select>

  </ben>

  </grp>

  </cat>

  </bd>'

  
  

  
  

• Eirikur Eiriksson

  SSC Guru

  Points: 182915

  More actions

  June 20, 2018 at 1:47 am

  #1994570

  The XML DML is a little cumbersome but this examples should get you going
  😎
  Before
  <bd>
<cat>
  <grp>
  <ben>
   <bn_id>11</bn_id>
   <select>
    <select_t />
    <select_k>F</select_k>
   </select>
  </ben>
  <ben>
   <bn_id>12</bn_id>
   <select>
    <select_t>G</select_t>
    <select_k>F</select_k>
   </select>
  </ben>
  </grp>
</cat>
</bd>

  
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @opt_xml TABLE
(
  bn_id INT
 ,select_t varchar(max)
 ,select_k varchar(max)
);

  INSERT INTO @opt_xml (bn_id , select_t , select_k)
SELECT 11,'L','T'
UNION
SELECT 12,'X','M';

  DECLARE @TXML XML = '<bd>
<cat>
  <grp>
  <ben>
   <bn_id>11</bn_id>
   <select>
    <select_t />
    <select_k>F</select_k>
   </select>
  </ben>
  <ben>
   <bn_id>12</bn_id>
   <select>
    <select_t>G</select_t>
    <select_k>F</select_k>
   </select>
  </ben>
  </grp>
</cat>
</bd>';
-- INSPECT THE OLD AND THE NEW VALUES
SELECT
  SEL.DATA.value('(bn_id/text())[1]','INT')       AS bn_id
 ,SEL.DATA.value('(select/select_t/text())[1]','VARCHAR(MAX)') AS select_t
 ,SEL.DATA.value('(select/select_k/text())[1]','VARCHAR(MAX)') AS select_k
 ,OX.select_k
 ,OX.select_t
FROM  @TXML.nodes('bd/cat/grp/ben') SEL(DATA)
CROSS APPLY @opt_xml  OX
WHERE   OX.bn_id  = SEL.DATA.value('(bn_id/text())[1]','INT');

  -- REPLACE AN EXISTING VALUE
DECLARE @BN_ID INT = 11;
DECLARE @neval VARCHAR(10) = (SELECT X.select_k FROM @opt_xml X WHERE X.bn_id = @BN_ID);
SET @TXML.modify('
replace value of (bd/cat/grp/ben[bn_id/text()=sql:variable("@BN_ID")]/select/select_k/text())[1]
with sql:variable("@NEVAL")');

  -- INSERT A VALUE AS AN EMPTY VALUE CANNOT BE REPLACED
SET @TXML.modify('
insert text {sql:variable("@NEVAL")}
as first into (bd/cat/grp/ben[bn_id/text()=sql:variable("@BN_ID")]/select/select_t)[1]
');

  SELECT @TXML;


  After
  <bd>
<cat>
  <grp>
  <ben>
   <bn_id>11</bn_id>
   <select>
    <select_t>L</select_t>
    <select_k>F</select_k>
   </select>
  </ben>
  <ben>
   <bn_id>12</bn_id>
   <select>
    <select_t>G</select_t>
    <select_k>F</select_k>
   </select>
  </ben>
  </grp>
</cat>
</bd>

  To clear all the node values, one can use a traversing // delete modification
  

  SET @TXML.modify('
delete //select_t/text()
');
SET @TXML.modify('
delete //select_k/text()
');