Skip to Main Content
  • Questions

  • appendChildXML with parent-namespace in the child

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Peter.

Asked: October 10, 2016 - 1:28 pm UTC

Last updated: October 11, 2016 - 8:46 am UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

Hi Chris & Connor

I want to append child nodes to an XML but have problems because of the (parent)namespace in the children: The child node in appendchildxml() is not a valid XML because of the namespace that comes from the parent.

The XML should look like this:
<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://www.def.com" xmlns:web="http://www.abc.com">
   <soapenv:Header />
   <soapenv:Body>
      <web:AAA>
         <web:BBB>
            <typ:Version>1.0</typ:Version>
            <typ:DDD>
               <typ:EEE>999</typ:EEE>
               <typ:FFF>888</typ:FFF>
               <typ:DDDs>
                  <typ:DDD>
                     <typ:EEE>10</typ:EEE>
                     <typ:FFF>11</typ:FFF>
                  </typ:DDD>
                  <typ:DDD>
                     <typ:EEE>20</typ:EEE>
                     <typ:FFF>22</typ:FFF>
                  </typ:DDD>
                  ...
               </typ:DDDs>
            </typ:DDD>
         </web:BBB>
      </web:AAA>
   </soapenv:Body>
</soapenv:Envelope>

Here is the link to the test script in LiveSql:
https://livesql.oracle.com/apex/livesql/s/dziispyp99rw0vsna4pj5un3c

I have no idea how to solve this problem with XML functions. It makes no sense for me to convert the XML to a string (or even worser: CLOB), insert the children via character functions and to convert this string to an XML again.

Thanks for your help
Peter

and Chris said...

First up, be aware that appendchildXML deprecated in 12c. So you'd be better off going for XQuery Update now to future proof your code.

In either case, in the XMLType you're adding you need to supply typ as a namespace:

select xmlquery('declare namespace soapenv = "http://schemas.xmlsoap.org/soap/envelope/"; 
declare namespace web = "http://www.abc.com";
declare namespace typ = "http://www.def.com"; (: :) 
copy $i := $p1 modify (
  for $j in $i/soapenv:Envelope/soapenv:Body/web:AAA/web:BBB/typ:DDD/typ:DDDs
  return insert nodes $p2 as last into $j
)
return $i'
  passing column_value as "p1",
          xmltype ( '<typ:DDD>
  <typ:EEE>test</typ:EEE>
  <typ:FFF>test</typ:FFF>
</typ:DDD>' ) as "p2"
  returning content) xml
from   xmltable('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://www.def.com" xmlns:web="http://www.abc.com">
  <soapenv:Header />
  <soapenv:Body>
    <web:AAA>
      <web:BBB>
        <typ:Version>1.0</typ:Version>
        <typ:DDD>
          <typ:EEE>999</typ:EEE>
          <typ:FFF>888</typ:FFF>
          <typ:DDDs></typ:DDDs>
        </typ:DDD>
      </web:BBB>
    </web:AAA>
  </soapenv:Body>
</soapenv:Envelope>');

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00234: namespace prefix "typ" is not declared
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.

select xmlquery('
declare namespace soapenv = "http://schemas.xmlsoap.org/soap/envelope/"; 
declare namespace web = "http://www.abc.com";
declare namespace typ = "http://www.def.com"; (: :) 
copy $i := $p1 modify (
  for $j in $i/soapenv:Envelope/soapenv:Body/web:AAA/web:BBB/typ:DDD/typ:DDDs
  return insert nodes $p2 as last into $j
)
return $i'
  passing column_value as "p1",
          xmltype ( '<typ:DDD xmlns:typ="http://www.def.com">
  <typ:EEE>test</typ:EEE>
  <typ:FFF>test</typ:FFF>
</typ:DDD>' ) as "p2"
  returning content) xml
from   xmltable('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://www.def.com" xmlns:web="http://www.abc.com">
  <soapenv:Header />
  <soapenv:Body>
    <web:AAA>
      <web:BBB>
        <typ:Version>1.0</typ:Version>
        <typ:DDD>
          <typ:EEE>999</typ:EEE>
          <typ:FFF>888</typ:FFF>
          <typ:DDDs></typ:DDDs>
        </typ:DDD>
      </web:BBB>
    </web:AAA>
  </soapenv:Body>
</soapenv:Envelope>');

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://www.def.com" xmlns:web="http://www.abc.com">
  <soapenv:Header />
  <soapenv:Body>
    <web:AAA>
      <web:BBB>
        <typ:Version>1.0</typ:Version>
        <typ:DDD>
          <typ:EEE>999</typ:EEE>
          <typ:FFF>888</typ:FFF>
          <typ:DDDs>
            <typ:DDD>
              <typ:EEE>test</typ:EEE>
              <typ:FFF>test</typ:FFF>
            </typ:DDD>
          </typ:DDDs>
        </typ:DDD>
      </web:BBB>
    </web:AAA>
  </soapenv:Body>
</soapenv:Envelope>


select appendchildXML(column_value, 
  'soapenv:Envelope/soapenv:Body/web:AAA/web:BBB/typ:DDD/typ:DDDs',
  xmltype ( '<typ:DDD xmlns:typ="http://www.def.com">
  <typ:EEE>test</typ:EEE>
  <typ:FFF>test</typ:FFF>
</typ:DDD>' ),
  'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
   xmlns:typ="http://www.def.com" 
   xmlns:web="http://www.abc.com"') xml
from   xmltable('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://www.def.com" xmlns:web="http://www.abc.com">
  <soapenv:Header />
  <soapenv:Body>
    <web:AAA>
      <web:BBB>
        <typ:Version>1.0</typ:Version>
        <typ:DDD>
          <typ:EEE>999</typ:EEE>
          <typ:FFF>888</typ:FFF>
          <typ:DDDs></typ:DDDs>
        </typ:DDD>
      </web:BBB>
    </web:AAA>
  </soapenv:Body>
</soapenv:Envelope>');

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://www.def.com" xmlns:web="http://www.abc.com">
  <soapenv:Header />
  <soapenv:Body>
    <web:AAA>
      <web:BBB>
        <typ:Version>1.0</typ:Version>
        <typ:DDD>
          <typ:EEE>999</typ:EEE>
          <typ:FFF>888</typ:FFF>
          <typ:DDDs>
            <typ:DDD>
              <typ:EEE>test</typ:EEE>
              <typ:FFF>test</typ:FFF>
            </typ:DDD>
          </typ:DDDs>
        </typ:DDD>
      </web:BBB>
    </web:AAA>
  </soapenv:Body>
</soapenv:Envelope>


PS - Thanks for providing a LiveSQL test case!

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks a lot!

Peter, October 11, 2016 - 10:06 am UTC

Peter
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here