1

I would like to dynamically replace the value of an XML Element, in selected XML value(s) being retrieved from a table. This replacement needs to be performed by a function that I would like to call. I can't even begin to find any clues to point me in the right direction...

I have the follow code snippet (incomplete) that I believe is the correct structure for using XPath to get to the element I want, and to qualify by XPath which records I am interested in (one or more), from there I have no idea how to take the result of the called function, and ideally place the result in a sql:column:

UPDATE [database].[dbo].[table_with_xml]
    SET table_with_xml.modify('replace value of (//PrimaryApplicant/FName/text())[1] with sql:column("")')
    FROM [database].[dbo].[table_with_xml] AS [XML_Table]
    WHERE [XML_Table].XML_Field.value('(//HeaderData/CountryCode/text())[1]', 'varchar(100)') = '123'
TDNeuman
  • 11
  • 2

1 Answers1

0

First of all, if you want to update table data from a function, you aren't allowed to do it. You can use a stored proc though. You can send 'replace value of' statement (from you syntax above) to it dynamically and send the value with which you want to replace. Here's some code to demonstrate what I'm trying to say:

Create PROC changeXML
@xmlPath nvarchar(max), -- the statement 'replace value of'
@value nvarchar(max) -- the new value
AS
BEGIN
DECLARE @pathString nvarchar(max), @sql NVARCHAR(MAX);
-- create full statement dynamically
SET @pathString =  'replace value of ('+@xmlPath+'text())[1] with  ("'+@value+'")';
SELECT @pathString;
-- create dynamic sql and pass pathstring to it.
SET @sql = 'UPDATE [dbo].[tableWithXmlData] SET a.modify(''@p'')';
-- execute dynamic sql
EXEC sp_executesql @sql, N'@p nvarchar(max)', @p = @pathString
END

Then run proc something like this:

EXEC [dbo].[chnageXML] @xmlPath = N'path to your xml value', 
@value = N'new value'
sanmis
  • 515
  • 1
  • 7
  • 22