How to rename Oracle XMLTYPE node

Question

I have an XMLType in PL / SQL and I need to rename some of the nodes and some of the values. For instance:

<root> <fields> <a>foo</a> <b>bar</b> </fields> </root> 

I want to include in this:

 <root> <fields> <a>foo</a> <c>baz</c> </fields> </root> 

I know that I can update the value as follows:

 SELECT UpdateXML(my_xml, '/root/fields/b/text()', 'baz') INTO my_xml_updated FROM DUAL; 

Result:

 <root> <fields> <a>foo</a> <b>baz</b> </fields> </root> 

But how can I update the name of a node from <b> to <c> (without affecting the contents of the node)?

Answer 1

One option is to use XMLTRANSFORM to rename a node. See Also, for example. Rename nodes using XSLT .

 with xmldata as (select xmltype('<root> <fields> <a>foo</a> <b>bar</b> </fields> </root>') val from dual), stylesheet as (select '<?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <!-- Identity transformation --> <xsl:template match="node()|@*"> <xsl:copy> <xsl:apply-templates select="node()|@*"/> </xsl:copy> </xsl:template> <!-- Identity transformation overridden for element b --> <xsl:template match="b"> <xsl:element name="c"> <xsl:apply-templates select="node()|@*"/> </xsl:element> </xsl:template> </xsl:stylesheet>' val from dual) select xmltransform(x.val, s.val) from xmldata x, stylesheet s; 

Output:

 XMLTRANSFORM(X.VAL,S.VAL) -------------------------------------------------------------------------------- <root> <fields> <a>foo</a> <c>bar</c> </fields> </root> 

Answer 2

Or you could define an xsl stylesheet that would make the changes and use dbms_xslprocessor to make your changes.