How to rename an 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 example:

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

I want to turn the above into this:

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

I know I can update the value like this:

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

The result is:

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

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

Your link points to a documentation of _Berkeley DB_, not to _Oracle DB_. — user272735, Oct 18 '11 at 04:23
Thanks, I removed that aspect of the question since it was redundant. — wweicker, Oct 18 '11 at 23:05

score 3 · Accepted Answer · edited May 23 '17 at 12:12

One option is to use XMLTRANSFORM to rename a node. See also e.g. Rename nodes with 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>

This discards the contents of `c` but can be modified like so: — wweicker, Oct 18 '11 at 22:58
@wweicker: Yes it did, but now the answer is updated to meet the changed requirements :) — user272735, Oct 19 '11 at 04:17

score 0 · Answer 2 · answered Oct 18 '11 at 12:46

0

Or, you could define an xsl stylesheet that will make the change and use dbms_xslprocessor to do your modifications.

answered Oct 18 '11 at 12:46

Jim Hudson

7,959
2
23
15

How to rename an Oracle XMLTYPE node

2 Answers2

Linked