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
– Khristian Liahut, Commented Jun 24, 2015 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
– Alex Poole, Commented Jun 24, 2015 at 13:50

Alex Poole · Accepted Answer · 2015-06-24 13:49:27Z

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.

answered Jun 24, 2015 at 13:49

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alex Poole Over a year ago

I assume this question is related, despite being from a different account?

Khristian Liahut Over a year ago

Hi Alex, for both cases the sentences add <Language xmlns=""/> instead of <Language></Language>. Do you know why?

Alex Poole Over a year ago

@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 Over a year ago

@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.

Collectives™ on Stack Overflow

Insert xml Node into a CLOB column with namespace in Oracle

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related