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.
