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?

Mean, that I can not insert this node into the xml structure of the CLOB column with these queries. — Khristian Liahut, Jun 24 '15 at 11:52
That isn't any clearer than 'not working' was. You should show any errors you get in the question; or if you don't get errors then what result you expect and what you actually get. — Alex Poole, Jun 24 '15 at 13:50

score 1 · Accepted Answer · answered Jun 24 '15 at 13:49

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.

I assume [this question](http://stackoverflow.com/q/31002954/266304) is related, despite being from a different account? — Alex Poole, Jun 24 '15 at 13:59
Hi Alex, for both cases the sentences add instead of . Do you know why? — Khristian Liahut, Jun 24 '15 at 14:27
@KhristianLiahut - no; I don't see that in 11.2.0.3 for appendchildxml, only for insertchildxml. Not sure which is 'correct'. You can specify the xmlns for appendchildxml too though. — Alex Poole, Jun 24 '15 at 14:34
@KhristianLiahut - are you referring to it including `xmlns=""` in both; or that they are self-closing tags rather than separate open/close tags? Why does that matter - they are equivalent? If you provided a text value within the node you're inserting/appending you'd get open/close, of course. — Alex Poole, Jun 24 '15 at 15:00

Insert xml Node into a CLOB column with namespace in Oracle

1 Answers1