17. November 2008

XML erzeugen in der Datenbank: Das Tutorial wird fortgesetzt

English title: Generating XML from database tables ... cont'd ...

Heute geht es mit dem im vorletzten Posting begonnenen Tutorial zum Thema XML generieren weiter. Versprochen waren ja Themen wie Hierarchien, Zeichensätzen und komplexe XML-Views. Zunächst bauen wie eine Hierarchie ein: Dazu gibt es die Funktion XMLAgg()
This is the second part of the generating XML tutorial which started in the posting a week ago .... Today's topics are XML hierarchys, more complex views and custom XML encodings. First we add a hierarchy; the SQL/XML function XMLAgg() is used for this
select
 xmlserialize(
  content
   xmlelement(  
    "department",
    xmlelement("name", d.dname),
    (
     select
      XMLAgg(
       xmlelement(
        "employee",
        xmlelement(
         "ename",
         XMLAttributes(
          e.empno as "id",
          e.hiredate as "hire-date"
         ),
         xmlelement("name", e.ename),
         xmlelement(evalname(e.job)),
         xmlelement("salary", e.sal),
         xmlelement("commission", e.comm),
         XMLComment('Dieses XML-Dokument wurde am '||to_char(sysdate, 'DD.MM.YYYY HH24:MI') ||' generiert.')
        )
       )
      )
     from scott.emp e where e.deptno = d.deptno
    )
   )
   indent
 ) as xml
from scott.dept d
/

XML
------------------------------------------------------------------------------
<department>
  <name>ACCOUNTING</name>
  <employee>
    <ename id="7782" hire-date="1981-06-09">
      <name>CLARK</name>
      <MANAGER/>
      <salary>2450</salary>
      <commission/>
      <!--Dieses XML-Dokument wurde am 14.11.2008 20:53 generiert.-->
    </ename>
  </employee>
  <employee>
    <ename id="7839" hire-date="1981-11-17">
      <name>KING</name>
      <PRESIDENT/>
      <salary>5000</salary>
      <commission/>
      <!--Dieses XML-Dokument wurde am 14.11.2008 20:53 generiert.-->
    </ename>
  </employee>
  <employee>
    <ename id="7934" hire-date="1982-01-23">
      <name>MILLER</name>
      <CLERK/>
      <salary>1300</salary>
      <commission/>
      <!--Dieses XML-Dokument wurde am 14.11.2008 20:53 generiert.-->
    </ename>
  </employee>
</department>

<department>
  <name>RESEARCH</name>
  <employee>
    <ename id="7369" hire-date="1980-12-17">
:
:
Diese XMLAgg()-Funktion kann beliebig geschachtelt werden, so dass auch komplexere, hierarchische XML-Dokumente möglich werden. Es leuchtet aber sofort ein, dass diese SQL-Abfragen bzw. die View-Definitionen in der Praxis etwas komplizierter werden, da meistens mehr als zwei Tabellen beteiligt sind. Versucht man, das alles in ein- und dieselbe View-Definition zu packen, dürfte man recht schnell die Übersicht verlieren ...
You can nest the XMLAgg() calls as much as you like; more complex XML documents with more hierarchy levels are possible. But it's obvious that the SQL queries get more and more confusing as you include more and more tables. And in real life there are most often more than two tables involved ...
Es empfiehlt sich eine Art "Bausteinkonzept". Zunächst erzeugen wir eine XML-View auf die Angestellten. Das ist wiederum die letzte Abfrage des vorletzten Postings.
The best approach is to create building blocks. First we create a view for just the employees - the SQL query is the last query in the first part of this tutorial.
create or replace view emp_xml as
select
 empno, 
 deptno,
 xmlelement(
  "employee",
  xmlelement(
   "ename",
   XMLAttributes(
    e.empno as "id",
    e.hiredate as "hire-date"
   ),
   xmlelement("name", e.ename),
   xmlelement(evalname(e.job)),
   xmlelement("salary", e.sal),
   xmlelement("commission", e.comm)
  )
 ) xml
from scott.emp e 
/

SQL> desc emp_xml
 Name                                      Null?    Typ
 ----------------------------------------- -------- -------------
 EMPNO                                     NOT NULL NUMBER(4)
 DEPTNO                                             NUMBER(2)
 XML                                                XMLTYPE
Die Informationen, die wir zum Join brauchen, werden als normale, relationale Tabellenspalten "neben" das XML gestellt. Außerdem haben wir die Funktion XMLSerialize() wieder entfernt, da das generierte XML im weiteren nochmals (und zwar als XML) benötigt wird. Die Umwandlung in den CLOB (und damit die Formatierung des XML) sollte immer nur zum Schluß erfolgen. Nun die View für die Abteilungen:
The information required for the joins is in normal relational columns besides the XML content. Furthermore the function XMLSerialize() is removed, since the generated XML will be needed by other views (as XML!). The rendering as a CLOB should be the last step in each case. Now the department view follows ...
create or replace view dept_xml as
select
 d.deptno,  
 xmlelement("department",
  xmlelement("name", d.dname),
  (
   select 
    XMLAgg(xml)
   from emp_xml e 
   where e.deptno = d.deptno
  )
 ) xml
from scott.dept d
/

SQL> desc dept_xml
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------
 DEPTNO                                             NUMBER(2)
 XML                                                XMLTYPE
Die Spalte XML dieser View enthält nun auch die Inhalte der View EMP_XML. Nach diesem Konzept können nun "Bausteine" anhand fachlicher Einheiten gebildet und zu komplexeren Objekten zusammengesetzt werden. Und wenn sich an der XML-Struktur für den employee etwas ändert, braucht man nur die View EMP_XML anzupassen - die Änderung wirkt sich wie immer auch auf alle anderen Views, in denen das XML verwendet wird.
The column XML of this view also contains the content of the EMP_XML view. Following this concept "building blocks" can be created based on business objects. And if the XML structure of EMP_XML changes, this is also reflected in all depending views.
Das Rendering des XML ist nun der letzte Schritt: Hier kann wieder die Funktion XMLSerialize() verwendet werden ...
The actual XML rendering is now the last step - and here the function XMLSerialize() might be used ...
select
 xmlserialize(
  document xmlroot(xml, version '1.0') 
 ) as xml_clob
from dept_xml
/

XML_CLOB
---------------------------------------------------------------------------
<?xml version="1.0"?>
<department>
  <name>ACCOUNTING</name>
  <employee>
    <ename id="7782" hire-date="1981-06-09">
      <name>CLARK</name>
      <MANAGER/>
      <salary>2450</salary>
      <commission/>
    </ename>
  </employee>
  <employee>
    <ename id="7839" hire-date="1981-11-17">
      <name>KING</name>
      <PRESIDENT/>
:
:
Nun (als letztes) zu den Zeichensätzen. Zunächst ist festzuhalten, dass XML in der Datenbank immer im Zeichensatz der Datenbank vorliegt. Ist der Datenbank-Zeichensatz also AL32UTF8, so liegt ein XML-Dokument auch stets in diesem Zeichensatz vor (das ist genauso wie bei einem CLOB). Soll das XML nun einen anderen Zeichensatz haben, so kann es nicht mehr als CLOB oder XMLTYPE vorliegen. Es muss in einen BLOB umgewandelt werden. Ein BLOB ist ohne Zeichensatzinformation; hier sind also beliebige Encodings möglich.
Last but not least we come to the XML encodings. The most important prerequisite is that any char datatype (except for NCHAR or NVARCHAR2) is encoded in the database characterset. This also applies to XMLTYPE. When the database has the characterset AL32UTF8 then each XMLTYPE is encoded as Unicode. If another encoding is needed the document has to be rendered as a BLOB.
select
 xmlserialize(
  document xmlroot(xml, version '1.0') as blob encoding 'windows-1252'
 ) as xml_blob
from dept_xml
/

XML_BLOB
--------------------------------------------------------------------------------
3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D2257494E444F57532D3132
3532223F3E0A3C6465706172746D656E743E0A3C6E616D653E4143434F554E54494E473C2F6E616D
653E0A3C656D706C6F7965653E0A3C656E616D652069643D22373738322220686972652D64617465
:
Guckt man sich das dann "richtig" an, so verbirgt sich hinter den Hexcodes folgender Inhalt.
These hexcodes are XML ...
<?xml version="1.0" encoding="WINDOWS-1252"?>
<department>
<name>ACCOUNTING</name>
:
Alternativ zur Funktion XMLSerialize() können auch die XMLTYPE-Methoden getblobval() und getclobval() verwendet werden. XMLSerialize() ist aber der Standard.
As an alternative to XMLSerialize() the XMLTYPE methods getblobval() or getclobval() might be also used. But XMLSerialize() is standard ...
select
  xmlroot(xml, version '1.0').getblobval(nls_charset_id('WE8MSWIN1252')) as xml_blob
from dept_xml
/

Beliebte Postings