XML ondersteuning in PL/SQL

Mei 2003 - De vorige Whitebooks van deze serie over XML gingen over Oracle's XML Developers Toolkit (XDK) en over de XML ondersteuning in de Oracle8i en 9i databases. Dit Whitebook gaat in op een aantal XML functies van PL/SQL (Oracle8i en Orace9i) waarmee XML documenten aangemaakt kunnen worden. 

De volgende onderwerpen worden besproken:

• de XMLGEN package
• de XML-SQL utility voor PL/SQL
• de XMLDOM package

In de voorbeelden gebruiken we de welbekende DEPT als master tabel en EMP als detail tabel. Iedere employee heeft werkt voor exact 1 department. Ieder department heeft geen, 1 of meer employees.

SELECT deptno, dname FROM dept;

DEPTNO     DNAME
---------- --------------
10         ACCOUNTING
20         RESEARCH
30         SALES
40         OPERATIONS

SELECT empno, ename, deptno FROM emp;
EMPNO ENAME  DEPTNO
----- -----  ----------
7369  SMITH  20
7499  ALLEN  30
7521  WARD   30
7566  JONES  20
7654  MARTIN 30
7698  BLAKE  30
7782  CLARK  10
7788  SCOTT  20
7839  KING   10
7844  TURNER 30
7876  ADAMS  20
7900  JAMES  30
7902  FORD   20
7934  MILLER 10 

XMLGEN Package

De eenvoudigste manier om een XML document te maken met PL/SQL is met behulp van de XMLGEN package. De functie getXml() accepteert parameters voor de SQL query en het meta data type (zoals de DTD) en retourneert een CLOB met het XML document.

SELECT xmlgen.getXml
       ('SELECT empno "EMP_NO", ename "NAME", deptno "DEPT_NO"
        FROM emp WHERE deptno = 10'
       , 0
       ) 
FROM dual;
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMP_NO>7782</EMP_NO>
      <NAME>CLARK</NAME>
      <DEPT_NO>10</DEPT_NO>
   </ROW>
   <ROW num="2">
      <EMP_NO>7839</EMP_NO>
      <NAME>KING</NAME>
      <DEPT_NO>10</DEPT_NO>
   </ROW>
   <ROW num="3">
      <EMP_NO>7934</EMP_NO>
      <NAME>MILLER</NAME>
      <DEPT_NO>10</DEPT_NO>
   </ROW>
</ROWSET>

Het result is een root element genaamd ROWSET, welke een aantal ROW-elementen bevat. Ieder ROW-element heeft een rij volgnummer als attribuut en bevat de elementen EMP_NO, NAME, en DEPT_NO. Merk op dat de namen van de ROW-elementen iets afwijken van de kolom namen van de EMP tabel.

Het gebruiken van de XML-SQL utility (XSU)

De XML-SQL Utility (XSU) biedt een eenvoudige mogelijkheid om gegevens uit een SQL query om te zetten naar XML en vice versa. XSU biedt een goede basis functionaliteit om gegevens middels XML op te halen en wegschrijven naar de database.

DBMS_XMLQUERY en DBMS_XMLSAVE zijn twee packages waarmee XML gegenereerd en opgeslagen kan worden middels XML. De DBMS_XMLSAVE package wordt gebruikt om XML data direct in de database op te slaan. Mogelijke functies zijn insertXML(), updateXML(), en deleteXML(). In deze Whitebook zullen we ons echter voornamelijk focussen op het genereren van XML met behulp van DBMS_XMLQUERY.

Het genereren van XML door de aanroep van de getXML() functie resulteert in een CLOB die het XML document bevat. De zogenaamde " context handle" - die benodigd is in vervolg aanroepen - wordt in de eerste stap gecreëerd.

DECLARE
    queryCtx  dbms_xmlquery.ctxType;
    result    CLOB;
BEGIN
    -- definieer de query context
    queryCtx := dbms_xmlquery.newContext
                ('SELECT empno "EMP_NO", ename "NAME", deptno "DEPT_NO"
                 FROM emp WHERE deptno = :DEPTNO'
                ); 

De ROW en ROWSET tag namen kunnen eenvoudig worden gewijzigd indien de DTD of de XML schema definitie expliciet gedefinieerde tags hebben die afwijken van de kolommen:

    dbms_xmlquery.setRowTag
       (queryCtx, 'EMP');
    dbms_xmlquery.setRowSetTag
      (queryCtx, 'EMPSET'); 

Voordat de query kan worden uitgevoerd moet de waarde van variabele (:DEPTNO) worden doorgegeven (het zogenaamde " binden" van de waarde van de variabele aan de SQL query). Let er op dat de betreffende "bind"  variabelen met een ’:’ beginnen.

    dbms_xmlquery.setBindValue
       (queryCtx, 'DEPTNO', 10); 

Nu kan de query worden uitgevoerd om vervolgens het XML resultaat te verkrijgen als een CLOB. De eenvoudige procedure printClobOut() ondersteunt het printen van de CLOB naar het scherm. Als laatste moet de "query handle" worden afgesloten en vrijgegeven.

    result := dbms_xmlquery.getXml(queryCtx);
   printClobOut(result);
    dbms_xmlquery.closeContext(queryCtx);
END;
/

Het resultaat is als volgt:

<?xml version = '1.0'?>
<EMPSET>
   <EMP num="1">
      <EMP_NO>7782</EMP_NO>
      <NAME>CLARK</NAME>
      <DEPT_NO>10</DEPT_NO>
   </EMP>
   <EMP num="2">
      <EMP_NO>7839</EMP_NO>
      <NAME>KING</NAME>
      <DEPT_NO>10</DEPT_NO>
   </EMP>
   <EMP num="3">
      <EMP_NO>7934</EMP_NO>
      <NAME>MILLER</NAME>
      <DEPT_NO>10</DEPT_NO>
   </EMP>
</EMPSET> 

De DBMS_XMLQUERY package biedt nog meer functionaliteit zoals 

• Pagineren met behulp van setMaxRows() en setSkipRows(). 
• Het gebruiken van stylesheets met behulp van setStylesheetHeader() en useStylesheet(). De utility genereert eerst het XML document, roept dan de parser aan, past de stylesheet toe, en genereert vervolgens het resultaat hetgeen zeer ten goede komt aan de performance. 

XMLDOM Package

De XMLDOM package implementeert het Document Object Model Interface (DOM Interface) zoals gedefineerd door de W3C XML aanbevelingen. De volgende interfaces zijn o.a. gedefinieerd:

• DOM Attribute interface 
• DOM Document interface 
• DOM DocumentType interface 
• DOM DOMImplementation interface 
• DOM Element interface 
• DOM Node interface 
• DOM NodeList interface 
• DOM Notation interface 
• DOM ProcessingInstruction interface 
• DOM Text interface 

We zullen met behulp van eenvoudig voorbeeld het gebruik van de XMLDOM package toelichten. Allereerst dienen een aantal speciale privileges aan de gebruiker van onderstaande code te worden gegeven. Een private synonymvereenvoudigt het gebruik van de package. Bijvoorbeeld voor de gebruiker scott:

GRANT javauserpriv to scott;
GRANT javasyspriv to scott;
GRANT EXECUTE ON xmldom TO scott;
CREATE SYNONYM scott.xmldom FOR SYS.xmldom; 

In de declaratie hebben we een aantal DOM references nodig. De DOMDocument handle is de belangrijkste - het wordt gebruikt in de meeste vervolg aanroepen. Vervolgens zijn meerdere DOM node handles benodigd om de refereren aan de hoofd node, de root node, de user node, en de item node voor ieder element. De cursor selecteert de employees voor een bepaald department.

DECLARE
    doc       xmldom.DOMDocument;
    main_node xmldom.DOMNode;
    root_node xmldom.DOMNode;
    user_node xmldom.DOMNode;
    item_node xmldom.DOMNode;
    root_elmt xmldom.DOMElement;
    item_elmt xmldom.DOMElement;
    item_text xmldom.DOMText;
    CURSOR get_users(p_deptno NUMBER) IS
      SELECT empno
      , ename
      , deptno
      , rownum
      FROM emp
      WHERE deptno = p_deptno; 

We beginnen met het aanmaken van een nieuwe document handle. Vervolgens maken we de hoofd node voor dit document. Het root element is genaamd EMPSET en wordt als child node aan de hoofd node toegevoegd. De geretourneerde handle wordt gebruikt als root node voor vervolg aanroepen.

BEGIN
    doc       := xmldom.newDOMDocument;
    main_node := xmldom.makeNode(doc);
    root_elmt := xmldom.createElement(doc, 'EMPSET');
    root_node := xmldom.appendChild(main_node, xmldom.makeNode(root_elmt)); 

Voor ieder record uit de query wordt een nieuw element genaamd EMP aangemaakt. Analoog aan het vorige voorbeeld wordt een rij volgnummer toegevoegd als attribuut aan het element. Het element wordt als child node toegevoegd aan de root node. De geretourneerde handle wordt gebruikt als user node voor vervolg aanroepen.

    FOR get_users_rec IN get_users(10) LOOP
        item_elmt := xmldom.createElement
                      (doc
                      , 'EMP'
                      );
        xmldom.setAttribute
                      (item_elmt
                      , 'num'
                      , get_users_rec.rownum
                      );
        user_node := xmldom.appendChild
                      (root_node
                      , xmldom.makeNode(item_elmt)
                      ); 

Nu worden de tekst elementen toegevoegd aan het DOM document. In de eerste stap wordt een nieuwe element genaamd EMP_NO aangemaakt. Dit element wordt toegevoegd als child node aan de user node. In de tweede stap wordt een tekst node aangemaakt welke de record data bevat, in dit geval het employee nummer. Deze tekst node wordt als child node toegevoegd aan de item node.

        item_elmt := xmldom.createElement
                      (doc
                      , 'EMP_NO'
                      );
        item_node := xmldom.appendChild
                      (user_node
                     , xmldom.makeNode(item_elmt)
                      );
       item_text := xmldom.createTextNode
                      (doc
                      , get_users_rec.empno
                      );
        item_node := xmldom.appendChild
                      (item_node
                      , xmldom.makeNode(item_text)
                      ); 

Hetzelfde kan nu gedaan worden voor de tekst elements NAME en DEPT_NO.

Nadat alle records verwerkt zijn en alle gegevens in het DOM document opgenomen zijn, kan het bijvoorbeeld weggeschreven worden en de geallokeerde resources weer worden vrijgegeven:

    END LOOP;
    xmldom.writeToFile(doc, '/tmp/xml/docSample.xml');
    xmldom.freeDocument(doc);
END;
/

Let er op dat de XMLDOM package de XML file op iedere willekeurige plek kan wegschrijven ongeacht de de waarde van de database parameter UTL_FILE_DIR.

De weggeschreven file bevat de volgende regels:

<?xml version = '1.0' encoding = 'UTF-8'?>
<EMPSET>
   <EMP num="1">
      <EMP_NO>7782</EMP_NO>
      <NAME>CLARK</NAME>
      <DEPT_NO>10</DEPT_NO>
   </EMP>
   <EMP num="2">
      <EMP_NO>7839</EMP_NO>
      <NAME>KING</NAME>
      <DEPT_NO>10</DEPT_NO>
   </EMP>
   <EMP num="3">
      <EMP_NO>7934</EMP_NO>
      <NAME>MILLER</NAME>
      <DEPT_NO>10</DEPT_NO>
   </EMP>
</EMPSET> 

Het gebruik van de XMLDOM package kan complex lijken in het begin, maar de structuur van het DOM document is volgens een eenvoudige manier opgebouwd: Ieder element of attribuut moet toegevoegd worden als node aan een parent node. De zelfde aanpak met vergelijkbare methoden wordt gebruikt in de XML implementatie van Java.

Enige beperkingen: 

• Let er op dat de gehele DOM in het geheugen wordt gehouden totdat het document wordt vrijgegeven. Het totaal aantal nodes is daarom afhankelijk van de configuratie van het database geheugen, bijv. met een JAVA_POOL_SIZE van 10.0000.000 bytes kunnen ongeveer 20.000 nodes worden aangemaakt.
• Een andere beperking is een geheugen lek in de Java implementatie van de DOM interface en de XML parser. Deze bug is gerapporteerd voor Sun Solaris en wordt verholpen door Oracle 8.1.7.3 en bug fix 2104071 of met Oracle 9.2.

Conclusie

De XMLGEN package kan worden gebruikt voor het genereren van eenvoudige XML documenten. De gegevens worden opgehaald met 1 SQL statement.

XSU heeft een tweetal packages DBMS_XMLQUERY en DBMS_XMLSAVE voor het genereren en opslaan van XML documenten. Extra functionaliteit is voorhanden om waarden van variabelen aan de query te "binden", voor het pagineren van de result set voor het direct toepassen van een stylesheets.

De meest geavanceerde aanpak is met behulp van de XMLDOM package. Deze biedt de optimale ondersteuning voor de DOM interface. Iedere DTD of XML schema definitie kan met behulp van deze package geïmplementeerd worden.