Replacing Node Name In An Xml Thats Stored In A Sql Server Database Column

I'd like to know how I can replace a child node name in a xml that I stored in my SQL Server database Example XML

Solution 1:

With XQuery, something like:

createfunction SwitchToLiveNode(@doc xml)
returns xml
asbegindeclare@valvarchar(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@docend

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)

Solution 2:

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 asvarchar(max)),'</Test_Node>','</Live_Node>'),'<Test_Node>','<Live_Node>')

Solution 3:

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_Nodea="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@NewNodeXML=
    (
     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_Nodea="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_Nodea="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:

WITHReadNodeAS
(
    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

Solution 4:

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:

CREATEPROCEDUREReplaceTestNode(@xml xml) ASEXTERNALNAMEStoredProcedures.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.

publicstaticclassStoredProcedures {

    [SqlProcedure]
    publicstaticvoidReplaceTestNode(SqlXml data, out SqlXml output) {
        XmlDocument doc = newXmlDocument();

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

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

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

privatestaticvoidReplaceElementName(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 overforeach(XmlNode child in element.ChildNodes) {
            replacement.AppendChild( child );
        }

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

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

Solution 5:

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]

Share