Insert xml Node into a CLOB column with namespace in Oracle

Question

I have next below xml value in CLOB column in Oracle 11g.

<Energy xmlns="http://euroconsumers.org/notifications/2009/01/notification">     
    <WEBSITE>WWW.VERLAAG.BE</WEBSITE>
    <CUSTOMERID>xxxxxx</CUSTOMERID>
    <Gender>M</Gender>
    <Telephone>0000000000</Telephone>
</Energy>

I want to add a new node called: Language

to look like this:

<Energy xmlns="http://euroconsumers.org/notifications/2009/01/notification">     
    <WEBSITE>WWW.VERLAAG.BE</WEBSITE>
    <CUSTOMERID>xxxxxx</CUSTOMERID>
    <Gender>M</Gender>
    <Telephone>0000000000</Telephone>
    <Language></Language>
</Energy>

I've used next below sentence:

update tmp_tab_noemail_test_aankoop p1
set p1.sce_msg = insertchildxml(p1.sce_msg, '/Energy', 'Language',
                 xmltype('<Language><Language/>'),
                 'xmlns="http://euroconsumers.org/notifications/2009/01/notification')
.getclobval();

And also this one:

update tmp_tab_noemail_test_aankoop p1
set p1.sce_msg = APPENDCHILDXML(p1.sce_msg,
                 '/Energy',
                 XMLType('<Language><Language/>'),
                 'xmlns="http://euroconsumers.org/notifications/2009/01/notification')
.getclobval()

But any of these functions are working.

Any idea?

Answer 1

In both of your statements you are not converting your initial CLOB to XMLType, and your closing tag for the new node is malformed - you have <Language/> instead of </Language>. Either provide opening and closing tags, or a single self-closing one, not a mix of both. You're also missing the closing double-quote in your namespace.

These both work:

update tmp_tab_noemail_test_aankoop p1
set p1.sce_msg = insertchildxml(XMLType(p1.sce_msg), '/Energy', 'Language',
  XMLType('<Language></Language>'),
  'xmlns="http://euroconsumers.org/notifications/2009/01/notification"').getclobval();

Or:

update tmp_tab_noemail_test_aankoop p1
set p1.sce_msg = APPENDCHILDXML(XMLType(p1.sce_msg), '/Energy',
  XMLType('<Language></Language>'),
  'xmlns="http://euroconsumers.org/notifications/2009/01/notification"').getclobval();

The latter looks a little better as the new tag appears as

<Language/>

rather than

<Language xmlns=""/>

You can preserve the namespace with insertchild but then it appears explicitly in the new node even though it matches the top-level Energy namespace; which doesn't matter functionally but looks a bit odd.