XML replace node
-
----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>'
-
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()
');
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply