Re: Creating XML documents using DOM in PL/SQL

Although I am not able to test this suggestion, it appears that inside the loop there is a call to create the root node:

xmlElem1 := xmldom.createElement(xmlDoc,'root');

This should probably be done once before entering the loop.

Frank Hubeny

daniel wrote:

> Hi all
>
> I want to create a XML document from a SQL query. I have done this according
> to the code below.
>
> PROCEDURE test_danne
> IS
> xmlDoc xmldom.DOMDocument;
> xmlNode xmldom.DOMNode;
> xmlNode1 xmldom.DOMNode;
> xmlElem1 xmldom.DOMElement;
> xmlElem2 xmldom.DOMElement;
> xmlText xmldom.DOMText;
>
> CURSOR cur_emp IS
> SELECT *
> FROM emp
> WHERE empno = 7369;
> row_emp emp%ROWTYPE;
> BEGIN
>
> xmlDoc := xmldom.newDOMDocument;
> xmlNode := xmldom.makeNode(xmlDoc);
>
> OPEN cur_emp;
> LOOP
> FETCH cur_emp INTO row_emp;
> EXIT WHEN cur_emp%NOTFOUND;
> xmlElem1 := xmldom.createElement(xmlDoc,'root');
> xmlNode :=
> xmldom.appendChild(xmlNode,xmldom.makeNode(xmlElem1));
> xmlElem2 := xmldom.createElement(xmlDoc,'name');
> xmldom.setAttribute(xmlElem2, 'empno',TO_CHAR(row_emp.empno));
> xmlNode :=xmldom.appendChild(xmlNode,xmldom.makeNode(xmlElem2));
> xmlText := xmldom.createTextNode(xmlDoc,row_emp.ename);
> xmlNode := xmldom.appendChild(xmlNode,xmldom.makeNode(xmlText));
> END LOOP;
> CLOSE cur_emp;
> xmldom.writeToFile(xmlDoc,'c:\xmltest1.txt');
> END;
>
> This works perfectly if i only get one row in my cursor. I get an output
> like this:
> <root>
> <name empno="7369">SMITH</name>
> </root>
>
> The problem is when i get more than one record in my cursor. If i change the
> cursor definition to:
> CURSOR cur_emp IS
> SELECT *
> FROM emp;
> i want a result like this
>
> <root>
> <name empno="7369">SMITH</name>
> <name empno="7389">CARL</name>
> <name empno="7239">ANNA</name>
> </root>
> but i get an error: ORA-29532: Unhandled Java-exception :
> java.lang.NullPointerException
>
> Anybody got an idea?
>
> Please mail me suggestions to daniel.carlsson_at_gravity.se (can't use news at
> work).
>
> TIA
>
> Daniel
  Received on Thu Feb 01 2001 - 01:57:39 CST