oracle 11g PLSQL append xml document as child to another xml document

Question

For several days I'm trying to find a solution to my problem, and so far no luck. Maybe someone can help me. Here is a piece of my code

doc DBMS_XMLDOM.DOMDocument;
doc := DBMS_XMLDOM.newDOMDocument;
DBMS_XMLDOM.setVersion(doc, '1.0');
DBMS_XMLDOM.setcharset(doc, 'ISO-8859-15');
root := DBMS_XMLDOM.makeNode(doc);
root := DBMS_XMLDOM.appendChild(root, DBMS_XMLDOM.makeNode(DBMS_XMLDOM.createElement(doc, 'toto')));

All above is working. The following piece of code works on Oracle 9g but not on 11g and I'm trying to find the solution to be able to make it work:

 xml := XMLTYPE.EXTRACT(lr.FIELD_XML, '/description/test');//from a table
 childDoc := DBMS_XMLDOM.newDOMDocument(xml);
 childRoot := DBMS_XMLDOM.makeNode(DBMS_XMLDOM.getDocumentElement(childDoc));
 node := DBMS_XMLDOM.appendChild(productNode, childRoot); --it fails here 

I've looked up on the Internet and it fails because I cannot append as a child a xml document to another xml document. I should replace the appendChild but I don't know how .

So, here is my question: anyone any idea how to solve this.

Thanks alot.

C.C.

Answer 1

XMLTYPE.EXTRACT(lr.FIELD_XML, '/description/test') comes from other document and is not part of doc DBMS_XMLDOM.DOMDocument. You have to import the xml into your dom structure first and after this append it.

declare 
  doc DBMS_XMLDOM.DOMDocument;
  root dbms_xmldom.DOMNode;
  toto_node dbms_xmldom.DOMNode;
  v_xml xmltype := xmltype('<a><b>bbbb</b><c>ccccc</c></a>') ;
  childDoc  DBMS_XMLDOM.DOMDocument;
  childDocElement   DBMS_XMLDOM.DOMELEMENT;
begin 
doc := DBMS_XMLDOM.newDOMDocument;

DBMS_XMLDOM.setVersion(doc, '1.0');
DBMS_XMLDOM.setcharset(doc, 'ISO-8859-15');
root := DBMS_XMLDOM.makeNode(doc);
toto_node := DBMS_XMLDOM.appendChild(root, DBMS_XMLDOM.makeNode(DBMS_XMLDOM.createElement(doc, 'toto1')));
childDoc  := DBMS_XMLDOM.NEWDOMDOCUMENT(v_xml);
childDocElement      := DBMS_XMLDOM.getDocumentElement(childDoc);
childDocElement      := DBMS_XMLDOM.makeElement(DBMS_XMLDOM.importNode(doc,DBMS_XMLDOM.makeNode(childDocElement),TRUE));

root := DBMS_XMLDOM.appendChild(toto_node,DBMS_XMLDOM.makeNode(childDocElement));
dbms_output.put_line(DBMS_XMLDOM.GETXMLTYPE(doc).getClobVal());
end;