Replacing Node name in an XML thats stored in a SQL Server database column

With XQuery, something like:

create function SwitchToLiveNode(@doc xml)
returns xml
as
begin
    declare @val varchar(200) = @doc.value('(/CompanyStatus/ProductionServers/ProductionServer/Test_Node)[1]', 'varchar(200)')

    declare @newNode xml = concat('<Live_Node>',@val,'</Live_Node>')

    SET @doc.modify('         
    insert sql:variable("@newNode")      
    as last         
    into (/CompanyStatus/ProductionServers/ProductionServer)[1]         
    ')

    set @doc.modify('delete /CompanyStatus/ProductionServers/ProductionServer/Test_Node')         

    return @doc
end

go

declare @doc xml = '
<CompanyStatus>
 <ProductionServers>
  <ProductionServer>
    <Patch>0</Patch>
    <Status>Green</Status>
    <Test_Node>Yes</Test_Node>
 </ProductionServer>
  </ProductionServers>
</CompanyStatus>'



select dbo.SwitchToLiveNode(@doc)

One quick option is via Replace()

(corrected your xml)

Example

Update YourTable 
   set XMLCol = replace(cast(XMLCol as nvarchar(max)),'Test_Node>','Live_Node>')

The Updated XML

<CompanyStatus>
  <ProductionServers>
    <ProductionServer>
      <Patch>0</Patch>
      <Status>Green</Status>
      <Live_Node>Yes</Live_Node>
    </ProductionServer>
  </ProductionServers>
</CompanyStatus>

EDIT - If Test_Node has Attributes (as correctly pointed out by Dai)

Update YourTable 
   set XMLCol = replace(replace(cast(XMLCol as varchar(max)),'</Test_Node>','</Live_Node>'),'<Test_Node>','<Live_Node>')

This was my suggestion

• save with attributes
• tolerant with the element's position (as long as this element is unique)

Check it out:

DECLARE @xml XML=
N'<CompanyStatus>
 <ProductionServers>
  <ProductionServer>
    <Patch>0</Patch>
    <Status>Green</Status>
    <Test_Node a="x" b="y" c="z">Yes</Test_Node>
 </ProductionServer>
  </ProductionServers>
</CompanyStatus>';

--This will create the <Test_Node> with all its attributes (if there are any) with the new element name <Live_Node>:

DECLARE @NewNode XML=
    (
     SELECT @xml.query(N'let $nd:=(//*[local-name()="Test_Node"])[1]
                         return
                         <Live_Node> {$nd/@*}
                         {$nd/text()}
                         </Live_Node>
                        ')
    );

--this will first insert the "@NewNode" directly after the original, and will remove the original:

SET @xml.modify(N'insert sql:variable("@NewNode") after (//*[local-name()="Test_Node"])[1]');
SET @xml.modify(N'delete (//*[local-name()="Test_Node"])[1]');

SELECT @xml;

The result

<CompanyStatus>
  <ProductionServers>
    <ProductionServer>
      <Patch>0</Patch>
      <Status>Green</Status>
      <Live_Node a="x" b="y" c="z">Yes</Live_Node>
    </ProductionServer>
  </ProductionServers>
</CompanyStatus>

UPDATE: The same with tabular data using an updateable CTE:

DECLARE @xmlTable TABLE (YourXml XML);
INSERT INTO @xmlTable VALUES
(--Test_Node has got attributes
N'<CompanyStatus>
 <ProductionServers>
  <ProductionServer>
    <Patch>0</Patch>
    <Status>Green</Status>
    <Test_Node a="x" b="y" c="z">Yes</Test_Node>
 </ProductionServer>
  </ProductionServers>
</CompanyStatus>'
)
,( --different position, no attributes
N'<CompanyStatus>
 <ProductionServers>
    <Test_Node>Yes</Test_Node>
  <ProductionServer>
    <Patch>0</Patch>
    <Status>Green</Status>
 </ProductionServer>
  </ProductionServers>
</CompanyStatus>'
)
,( --No test node at all
N'<CompanyStatus>
 <ProductionServers>
  <ProductionServer>
    <Patch>0</Patch>
    <Status>Green</Status>
 </ProductionServer>
  </ProductionServers>
</CompanyStatus>'
);

--the updateable CTE returns the original and the new node. This can be updated in one go:

WITH ReadNode AS
(
    SELECT t.YourXml.query(N'let $nd:=(//*[local-name()="Test_Node"])[1]
                        return
                        <Live_Node> {$nd/@*}
                        {$nd/text()}
                        </Live_Node>
                    ') AS NewNode
         ,t.YourXml AS Original
    FROM @xmlTable AS t
)
UPDATE ReadNode SET Original.modify(N'insert sql:column("NewNode") after (//*[local-name()="Test_Node"])[1]');

UPDATE @xmlTable SET YourXml.modify(N'delete (//*[local-name()="Test_Node"])[1]');

SELECT *
FROM @xmlTable 

While your question asks for a solution for SQL I think the best solution is to parse the XML properly with an XML library, which would be easiest in C#.

Fortunately you can use this from within SQL Server as a SQL-CLR stored procedure:

CREATE PROCEDURE ReplaceTestNode(@xml xml) AS EXTERNAL NAME StoredProcedures.ReplaceTestNode

You cannot rename XML elements using System.Xml unfortunately. Instead you create a new replacement node, insert it into the same place as the original, move the contents (and any attributes) over, then remove the original.

public static class StoredProcedures {

    [SqlProcedure]
    public static void ReplaceTestNode(SqlXml data, out SqlXml output) {
        XmlDocument doc = new XmlDocument();

        using( XmlReader rdr = data.CreateReader() ) {
            doc.Load( rdr );
        }

        ReplaceElementName( doc, "Test_Node", "Live_Node" );

        using( XmlReader outRdr = new XmlNodeReader( doc ) ) {
            output = new SqlXml( outRdr );
        }
    }
}

private static void ReplaceElementName(XmlDocument doc, String oldName, String newName) {

    XmlNodeList testNodes = doc.GetElementsByTagName( oldName );
    List<XmlElement> testElements = testNodes
        .Where( n => n.NodeType == XmlNodeType.Element )
        .Cast<XmlElement>()
        .ToList();

    foreach( XmlElement element in testElements ) {

        // 1: Create replacement element and insert in the same location:
        XmlElement replacement = doc.CreateElement( newName );
        element.ParentElement.InsertBefore( replacement, element );

        // 2: Move child nodes over
        foreach(XmlNode child in element.ChildNodes) {
            replacement.AppendChild( child );
        }

        // 3: Move attributes over
        foreach(XmlAttribute attrib in element.Attributes) {
            replacement.Attributes.Append( attrib );
        }

        // 4: Remove
        element.ParentElement.Remove( element );
    }
}

UPDATE TABLE_NAME set COLUMN_NAME=cast(REPLACE(cast(COLUMN_NAME as VARCHAR(max)),'Test_Node','Live_Node')as XML)

[Note: Use this script put your table name and the targeted column name]