September 11, 2015 at 12:43 am
I have a number of records in a table which contain an xml field. I need to update a specific piece of text within the xml column, here's an example of the column tags
<Name>Assets - Reconciler</Name>
<UpdatedOn>2015-05-20T13:23:09.230</UpdatedOn>
<CreatedOn>2015-05-20T13:22:04.920</CreatedOn>
<UpdatedBy>RLG\JoeBloggs</UpdatedBy>
<CreatedBy>RLG\JoeBloggs</CreatedBy>
<InputDirectory>\\99.999.999.9\Active</InputDirectory>
<OutputDirectory>\\99.999.999.9\Archive</OutputDirectory>
<ErrorDirectory>\\99.999.999.9\Errors</ErrorDirectory>
<LoadId>0</LoadId>
<Type>ExcelFile</Type>
I need to update the IP addresses in the various 'directory' tags, that's all I need to change as the rest of the directory structure will stay the same. Is there an easy way to do this for all records in the table
September 11, 2015 at 1:24 am
Quick example
😎
USE tempdb;
GO
SET NOCOUNT ON;
/* XML snip to update, note added a ROOT element */
DECLARE @TXML XML = '<ROOT>
<Name>Assets - Reconciler</Name>
<UpdatedOn>2015-05-20T13:23:09.230</UpdatedOn>
<CreatedOn>2015-05-20T13:22:04.920</CreatedOn>
<UpdatedBy>RLG\JoeBloggs</UpdatedBy>
<CreatedBy>RLG\JoeBloggs</CreatedBy>
<InputDirectory>\\99.999.999.9\Active</InputDirectory>
<OutputDirectory>\\99.999.999.9\Archive</OutputDirectory>
<ErrorDirectory>\\99.999.999.9\Errors</ErrorDirectory>
<LoadId>0</LoadId>
<Type>ExcelFile</Type>
</ROOT>';
DECLARE @NEW_IP VARCHAR(15) = '123.456.789.012';
DECLARE @SAMPLE_TABLE TABLE
(
ST_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,TXML XML NOT NULL
);
INSERT INTO @SAMPLE_TABLE(TXML)
VALUES (@TXML),(@TXML),(@TXML);
SELECT
ST.ST_ID
,ST.TXML
FROM @SAMPLE_TABLE ST;
UPDATE ST
SET TXML.modify('replace value of (/ROOT/InputDirectory/text())[1] with sql:variable("@NEW_IP")')
FROM @SAMPLE_TABLE ST;
UPDATE ST
SET TXML.modify('replace value of (/ROOT/OutputDirectory/text())[1] with sql:variable("@NEW_IP")')
FROM @SAMPLE_TABLE ST;
UPDATE ST
SET TXML.modify('replace value of (/ROOT/ErrorDirectory/text())[1] with sql:variable("@NEW_IP")')
FROM @SAMPLE_TABLE ST;
SELECT
ST.ST_ID
,ST.TXML
FROM @SAMPLE_TABLE ST;
Output
<ROOT>
<Name>Assets - Reconciler</Name>
<UpdatedOn>2015-05-20T13:23:09.230</UpdatedOn>
<CreatedOn>2015-05-20T13:22:04.920</CreatedOn>
<UpdatedBy>RLG\JoeBloggs</UpdatedBy>
<CreatedBy>RLG\JoeBloggs</CreatedBy>
<InputDirectory>123.456.789.012</InputDirectory>
<OutputDirectory>123.456.789.012</OutputDirectory>
<ErrorDirectory>123.456.789.012</ErrorDirectory>
<LoadId>0</LoadId>
<Type>ExcelFile</Type>
</ROOT>
September 11, 2015 at 4:33 am
With a bit of tweaking I got it to work
Many Thanks
September 11, 2015 at 4:42 am
Newbie36037 (9/11/2015)
With a bit of tweaking I got it to workMany Thanks
You are very welcome.
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply