Creating XML documents using DOM in PL/SQL

From: daniel <daniel.carlsson_at_gravity.se>
Date: Wed, 31 Jan 2001 00:42:03 +0100
Message-ID: <957goo$o2n$1@merkurius.lu.se>

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 Tue Jan 30 2001 - 17:42:03 CST