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
/

Kommentare:

Patrick Wolf hat gesagt…

Hallo Carsten,

tolles Tutorial zum Thema XML! Ich verwende XML ja viel zu wenig, aber ich habe gerade mal wieder fuer einen APEX On-Demand Prozess eine XML Ausgabe machen muessen und da hat Dein Tutorial als Gedaechnisstuetze super weitergeholfen.

Uebrigens fuer eine hierarchische Darstellung (eine richtige mit CONNECT BY) habe ich DBMS_XmlGen.newContextFromHierarchy verwendet. Ist das noch aktuell oder gibt es da was direkt im SQL Sprachumfang was ich verwenden koennte?

Danke
Patrick

Carsten Czarski hat gesagt…

Hi Patrick,

das geht auch, man muss aber ein wenig tricksen .. und eine Funktion verwenden ...

dazu habe ich vor einer Ewigkeit auch schonmal was geschrieben ...

Viele Grüße

-Carsten

Frank Schmidt hat gesagt…

Kleiner Nachtrag zum encoding:

Das beschriebene Vorgehen zum Erstellen des XML-File benötigt 11g. Auf Rückfrage hat Hr. Czarski aber auch eine Lösung für Oracle 10g in Angebot:

select
xmlroot(xml, version'1.0').getblobval(nls_charset_id('WE8MSWIN1252')) as xml_blob
from dept_xml
/

-> erzeugt den XML-Header incl. encoding-Phrase auch unter 10gR2.

Vielen Dank für diesen Tipp!

Martin Wolter hat gesagt…

Hallo Carsten,

erstmal danke für dieses Tutorial! Ich hätte eine winzige Bitte: unter dem Link http://www.doag.org/pub/docs/sig/development/2008/11-06/ aus der Linksammlung (Sidebar) scheint das angebotene PDF defekt zu sein. Wäre es möglich, dort ein intaktes PDF zu hinterlegen?

Vielen Dank im Voraus & Grüße,
Martin

Carsten Czarski hat gesagt…

Hallo Martin,

ich werde das mal an die DOAG weitergeben; in der Zwischenzeit hilft aber vielleicht ein "Workaround". Es ist tatsächlich ein Zipfile: Also einfach umbenennen in ".zip" und dann öffnen ... geht wirklich ...

Grüße

-Carsten

Martin Wolter hat gesagt…

Hallo Carsten,

super, hat geklappt - danke!

Grüße,
Martin

Beliebte Postings