XQuery Rename/Replace Xml Node and Update K2 Workflow Process Instance Xml Fields

I recently ran across a case where a property in our application was re-termed due to a change in requirements. Updating the code was very easy, however it meant that all of the Xml documents needed to be updated and that was not going to be as easy. It would involve updating the serialized object xml documents in our workflow data repository and K2.

Out of curiosity I ran the process using the old Xml to see what would happen. Deserializing the old Xml documents resulted in everything after that renamed property being omitted from the deserialization and it didn't throw and error. That was unexpected, but good information to know.

As it turns out, updating the workflow data repository was easy. K2 has many issues which resulted in the K2 update sql. The code below shows how I accomplished this:

CREATE PROCEDURE [dbo].[usp_brock_RenameXmlNode]
AS
BEGIN
 -- UPDATE Workflow Instances
 UPDATE [WorkflowInstances] SET InstanceData.modify('
  insert element NewTag {/ObjectXML/OldTag/text()} after (/ObjectXML/OldTag)[1]
 ')
 WHERE  WorkflowID='7B4DEB62-D462-4FE8-A8E2-2057B5F31B19'
   AND InstanceData.exist('/ObjectXML/OldTag') = 1
 UPDATE [WorkflowInstances] SET InstanceData.modify('
  delete (//OldTag)[1]
 ')
 WHERE  WorkflowID='7B4DEB62-D462-4FE8-A8E2-2057B5F31B19'
   AND InstanceData.exist('/ObjectXML/OldTag') = 1
 
 -- UPDATE K2 Workflows
 -- Create Table Var and Populate
 DECLARE @K2ProcXml2XmlType table
 (
  ProcInstID INT PRIMARY KEY,
  WorkflowName VARCHAR(128),
  WorkflowID UNIQUEIDENTIFIER,
  InstanceData XML,
  IsModified BIT
 )
 PRINT 'POPULATE TABLE VAR';
 INSERT INTO @K2ProcXml2XmlType 
  SELECT pin.ID
     ,ps.[Name] WorkflowName
     , wi.WorkflowID
     ,CONVERT(XML,REPLACE(CAST(f.string as nvarchar(max)),'<?xml version="1.0" encoding="utf-8"?>','')) InstanceData
     , 0 --IsModified
    FROM [K2].[dbo]._FieldOnDemand f INNER JOIN [K2].[dbo]._ProcInst pin ON f.ProcInstID=pin.ID
    INNER JOIN [DemoDatabase].dbo.WorkflowInstances wi ON wi.ProcessID=pin.ID
    INNER JOIN [K2].[dbo]._Proc p ON pin.ProcID = p.ID
    INNER JOIN [K2].[dbo]._ProcSet ps ON p.ProcSetID = ps.ID
  WHERE  (ps.[Name] in ('WorkflowX'))

 UPDATE @K2ProcXml2XmlType
 SET InstanceData.modify('insert element NewTag {/ObjectXML/OldTag/text()} after (/ObjectXML/OldTag)[1]'), IsModified = 1
 WHERE (InstanceData IS NOT NULL) 
  AND (InstanceData.exist('/ObjectXML/OldTag') = 1)

 UPDATE @K2ProcXml2XmlType
 SET InstanceData.modify('delete (//OldTag)[1]'), IsModified = 1
 WHERE (InstanceData IS NOT NULL) 
  AND (InstanceData.exist('/ObjectXML/OldTag') = 1)


 -- End Result of all updates
 --PRINT 'QUERY RESULT:';
 --SELECT
 --        [ProcInstID]
 --   , WorkflowName
 --   , InstanceData
 --  FROM @K2ProcXml2XmlType src
 --WHERE src.IsModified = 1;

 -- Populate Original Table and Query
 PRINT 'POPULATE TABLE FROM VAR:';
 UPDATE dst
 SET dst.String = CONVERT(ntext,'<?xml version="1.0" encoding="utf-8"?>'+CAST(src.InstanceData as nvarchar(max)))
 FROM @K2ProcXml2XmlType src INNER JOIN [K2].[dbo].[_FieldOnDemand] dst ON src.ProcInstID = dst.ProcInstID
 WHERE src.IsModified = 1;

        -- END K2 Update
END;
GO

exec dbo.usp_brock_RenameXmlNode

DROP PROCEDURE dbo.usp_brock_RenameXmlNode

It runs quite fast.

There are a couple caveats, you can't create the XPath location dynamically (unless you are using dynamic sql which I try to avoid at all costs). This means that you can't pass old and new name parameters into the stored procedure and update the records. Disappointing as it is, any future renames should require an easy find and replace.

I tried using a cursor to do this type of updating, but the resulting SQL in execution times in more than more than 90 minutes (I cancelled it at that point) and verified with the DBA's that I was not doing something wrong, I abandoned that path and went back to the set based updates.