Renaming XML elements and attributes in XML data columns in Microsoft SQL Server using T-SQL

We've recently had an issue whereby we wanted to rename an XML attribute that is stored in an XML data column within SQL Server.

There are several ways to achive this

• Cast the column to NVARCHAR(MAX) and perform a string replacement. This is a bit risky as you're not working with strongly typed data.
• Perform the update from the .NET application obtaining the data from SQL and performing the transformation in .NET. This has the advantage of having the power of .NET to perform the modification. You also get error handling as you can loop through each result and perform the XML transformation and if you detect an error you can see exactly which table row caused the issue, you can also run the whole update in a transaction.
• Perform the update within T-SQL using the XML methods built into SQL Server. This has the advantage of being able to be run directly within SQL Management Studio, and within a transaction. It can also therefore be run as part of the installation, with other database schema updates being performed in the same transaction and is the option that we've detailed below.

Here's our XML, we have an old ID column we want to rename to Identifier.

<Relationships>

    <Relationship ID="123" />

    <Relationship ID="456" />

</Relationships>

For simplicity we are creating a new table variable to store the XML

DECLARE @ItemCore TABLE ( rowId INT IDENTITY PRIMARY KEY, Relationships XML )

-- 1. Two relationships

INSERT INTO @ItemCore ( Relationships )

VALUES ( '<Relationships>

    <Relationship ID="123" />

       <Relationship ID="456" />

</Relationships>' )

Unfortunately T-SQL doesn't have an XML rename() method.

We do have a modify() method, but unfortunately this only works on a single element.

So what we have to do is this, while there are any Relationships that do not have an Identifier attribute keep running in a loop.

Whilst in the loop, modify the Relationship[not(@Identifier)])[1] and insert an attribute, this will modify the first Relationship in every row of the database before the loop begins again, the first attribute on the next run of the loop will be a different attribute (because the previous first one now has the attribute so won't be selected).

---- While there are relationships without an "Identifier" attribute, insert a new attribute using the value of the ID attribute.

WHILE EXISTS(SELECT 1

             FROM @ItemCore

             WHERE [@ItemCore].Relationships.exist('Relationships/Relationship[not(@Identifier)]') = 1)

BEGIN

  UPDATE @ItemCore SET

    [Relationships].modify('

              insert attribute Identifier { /Relationships/Relationship[not(@Identifier)][1]/@ID  }

              into (/Relationships/Relationship[not(@Identifier)])[1]')

  WHERE [@ItemCore].[Relationships].exist('Relationships/Relationship[not(@Identifier)]') = 1

END         

 

Once all Relationships have an Identifier attribute the deprecated ID attributes can be deleted. Luckily this time we can run a delete against all Relationships in all rows so the code is much simpler.

-- Delete all ID attributes

UPDATE @ItemCore SET

[Relationships].modify('delete (/Relationships/Relationship/@ID)')