5. November 2008

XML erzeugen in der Datenbank: Ein kleines Tutorial

English title: Generating XML in the database - a brief tutorial

Wie man XML aus der Datenbank heraus generieren kann, hatte ich zwar das eine oder andere Mal in einem Blog-Posting behandelt, jedoch niemals so richtig komplett. Daher widme ich dieses und die nächsten Postings den SQL/XML-Funktionen zum Erzeugen von XML.
Generating XML from table data is a pretty frequent requirement. I had written something about this in the past, but these blog postings were not comprehensive. So this (and the next) blog posting is about XML generation - today in a more comprehensive way.
Wir fangen mal ganz einfach an und erzeugen das einfachste denkbare XML-Dokument mit einem leeren Tag.
We start simple and generate an XML document containing just one empty tag.
SQL> select xmlelement("tag") from dual;

XMLELEMENT("TAG")
--------------------------------------------------
<tag></tag>
Die SQL/XML-Funktionen sind SQL-Funktionen (übrigens Teil von SQL:2003), mit denen aus einer Abfrage heraus beliebig XML generiert werden kann. Für die diversen Elementtypen, die in XML möglich sind, gibt es diverse Funktionen:
Funktion:Verfügbar ab:Zweck:
XMLElement()9.2.0Erzeugt ein XML-Tag
XMLAttributes()9.2.0Erzeugt ein oder mehrere XML-Attribute innerhalb eines Tags
XMLForest()9.2.0Erzeugt mehrere XML-Tags auf einmal
XMLCDATA()10.2.0Erzeugt eine sog. CDATA-Section
XMLComment()10.2.0Erzeugt einen XML-Kommentar
XMLPI()10.2.0Erzeugt eine sog. Processing Instruction
XMLAgg()10.2.0Fasst mehrere XML-Tags zusammen und erzeugt eine Hierarchiestufe
XMLRoot()10.2.0Erzeugt den sog. XML-Prolog
The SQL/XML functions are part of the SQL:2003 standard and allow to construct XML from ordinary SQL queries. For the various components of the XML data model exist different functions:
Function:Availibility:Purpose:
XMLElement()9.2.0Produces a XML-Tag
XMLAttributes()9.2.0Generates one or more XML-Attributes inside a tag
XMLForest()9.2.0Generates multiple tags at once
XMLCDATA()10.2.0Generates a CDATA Section
XMLComment()10.2.0Produces a XML comment
XMLPI()10.2.0Generates a XML processing instruction
XMLAgg()10.2.0Aggregates multiple XML tags to a new hierarchy level
XMLRoot()10.2.0Generates the XML-Prolog
Die Funktionen geben stets XML zurück - aus Sicht der Datenbank ist das dann ein XMLTYPE. Nun einige Beispiele:
The functions return XML - so the returning datatype is XMLTYPE
1. Ein Tag mit Inhalt:
1. Producing a XML tag with contents:
SQL> select xmlelement("tag", 'Inhalt') from dual;

XMLELEMENT("TAG",'INHALT')
-------------------------------------------------------------
<tag>Inhalt</tag>
2. Die Inhalte werden aus einer Tabelle selektiert:
2. The tag content is being selected from a table:
SQL> select xmlelement("ename", ename) from scott.emp;

XMLELEMENT("ENAME",ENAME)
-------------------------------------------------------------
<ename>SMITH</ename>
<ename>ALLEN</ename>
<ename>WARD</ename>
<ename>JONES</ename>
:

14 rows selected.
3. Wir nehmen XML-Attribute hinzu:
3. Add XML attributes:
select 
 xmlelement(
  "ename", 
  XMLAttributes(
   empno as "id", 
   hiredate as "hire-date"
  ),
  ename
) from scott.emp
/

<ename id="7369" hire-date="1980-12-17">SMITH</ename>
<ename id="7499" hire-date="1981-02-20">ALLEN</ename>
<ename id="7521" hire-date="1981-02-22">WARD</ename>
:
4. Wir schachteln XML-Tags ineinander:
4. Nesting XML tags:
select 
 xmlelement(
  "employee",
  xmlelement(
   "name", 
   XMLAttributes(
    empno as "id", 
    hiredate as "hire-date"
   ),
   ename
  )
) from scott.emp
/

<employee><name id="7369" hire-date="1980-12-17">SMITH</name></employee>
<employee><name id="7499" hire-date="1981-02-20">ALLEN</name></employee>
<employee><name id="7521" hire-date="1981-02-22">WARD</name></employee>
:
Einschub: Kann man das ganze auch eingerückt darstellen (pretty print)? Ja, das geht - aber erst ab Oracle11g (11.1.0). Aber Vorsicht: Der zurückgegebene Datentyp ist nun CLOB - XMLSerialize() dient dazu, das XML in einen Text umzuwandeln, wird also genutzt, bevor das generierte XML die Datenbank "verlässt".
BTW: You might want to have pretty printed XML (with indents). This is possible with 11g (11.1.0). The returning data type changes to CLOB when you use XMLSerialize(). This function should be used when the generated XML has to "leave" the database as text.
select 
 xmlserialize(
  content 
   xmlelement(
    "employee",
    xmlelement(
     "ename", 
     XMLAttributes(
      empno as "id", 
      hiredate as "hire-date"
     ),
     ename
    )
   ) 
  indent 
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17">SMITH</ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20">ALLEN</ename>
</employee>

<employee>
  <ename id="7521" hire-date="1981-02-22">WARD</ename>
</employee>
Technisch hat die Einrückung keine Bedeutung - sie dient lediglich dazu, das XML für den Menschen besser lesbar zu machen. Meistens ist jedoch eine Anwendung oder ein Prozeß der Konsument für das XML - und dieser benötigt kein Pretty Print. Im folgenden sind alle SQL-Abfragen mit Pretty-Printing angegeben; auf einer 10g-Umgebung einfach die XMLSerialize()-Funktion zu Beginn und das INDENT zum Ende entfernen
Pretty printing is just for better "human readability" of the XML document. A pretty printed XML document has the same semantics as a non pretty printed one. So if the XML is being consumed by another application pretty printing is not necessary. The following queries are shown with the pretty printing clause; for a 10g environment just remove the XMLSerialize() function (don't forget the INDENT keyword at the end).
5. Der Inhalt des Tags soll als sog. CDATA-Section generiert werden; das wird vor allem dann genommen, wenn die Texte länger sind und viele XML-Zeichen (<, >, &) beinhalten:
5. Now we want to generate a tag content as CDATA section; this is useful for longer texts with many "special XML characters" (<, >, &):
select
 xmlserialize(
  content
   xmlelement(
    "employee",
    xmlelement(
     "ename",
     XMLAttributes(
      empno as "id",
      hiredate as "hire-date"
     ),
     xmlcdata(ename)
    )
   )
  indent
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17"><![CDATA[SMITH]]></ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20"><![CDATA[ALLEN]]></ename>
</employee>
6a. Mehrere Tags auf einmal erzeugen (mit XMLForest()):
6a. Generate multiple XML tags at one (with XMLForest()):
select 
 xmlserialize(
  content 
   xmlelement(
    "employee",
    xmlelement(
     "ename", 
     XMLAttributes(
      empno as "id", 
      hiredate as "hire-date"
     ),
     xmlforest(
      ename as "name",
      job as "job",
      sal as "salary",
      comm as "commission"
     )
    )
   ) 
  indent 
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17">
    <name>SMITH</name>
    <job>CLERK</job>
    <salary>800</salary>
  </ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20">
    <name>ALLEN</name>
    <job>SALESMAN</job>
    <salary>1600</salary>
    <commission>300</commission>
  </ename>
</employee>
Man sieht, dass das Tag commission (welches aus der Spalte COMM gespeist wird), mal vorhanden ist und mal nicht. XMLForest() erzeugt ein Tag, wenn Inhalte da sind, wenn nicht, wird keins erzeugt.
The tag commission (which contains the value of the table column COMMN) is present for some XML documents and for not for others. XMLForest() does not generate a tag if the column value is NULL.
6b. Mehrere Tags auf einmal erzeugen (jeweils explizit mit XMLElement()):
6b. Now we do the same with XMLElement():
select 
 xmlserialize(
  content 
   xmlelement(
    "employee",
    xmlelement(
     "ename", 
     XMLAttributes(
      empno as "id", 
      hiredate as "hire-date"
     ),
     xmlelement("name", ename),
     xmlelement("job", job),
     xmlelement("salary", sal),
     xmlelement("commission", comm)
    )
   ) 
  indent 
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17">
    <name>SMITH</name>
    <job>CLERK</job>
    <salary>800</salary>
    <commission/>
  </ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20">
    <name>ALLEN</name>
    <job>SALESMAN</job>
    <salary>1600</salary>
    <commission>300</commission>
  </ename>
</employee>
Und ist das Tag commission immer vorhanden - es wurde ja auch explizit verlangt. Wenn die Tabellenspalte den Inhalt NULL hat, bleibt das XML-Tag leer.
The tag commission is now present in each XML document. For NULL values the tag is empty.
7. Wir fügen einen Kommentar ein:
7. Generating a comment:
select 
 xmlserialize(
  content 
   xmlelement(
    "employee",
    xmlelement(
     "ename", 
     XMLAttributes(
      empno as "id", 
      hiredate as "hire-date"
     ),
     xmlelement("name", ename),
     xmlelement("job", job),
     xmlelement("salary", sal),
     xmlelement("commission", comm),
     XMLComment('Dieses XML-Dokument wurde am '||to_char(sysdate, 'DD.MM.YYYY HH24:MI') ||' generiert.')
    )
   ) 
  indent 
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17">
    <name>SMITH</name>
    <job>CLERK</job>
    <salary>800</salary>
    <commission/>
    <!--Dieses XML-Dokument wurde am 02.11.2008 19:18 generiert.-->
  </ename>
</employee>
8. Nun soll der Inhalt der Tabellenspalte den Namen des XML-Tags bestimmen (dies ist ab Version 10.2.0.3 möglich):
8. Now the column value should determine the XML tag's name (this is available with 10.2.0.3 and higher):
select
 xmlserialize(
  content
   xmlelement(
    "employee",
    xmlelement(
     "ename",
     XMLAttributes(
      empno as "id",
      hiredate as "hire-date"
     ),
     xmlelement("name", ename),
     xmlelement(evalname(job)),
     xmlelement("salary", sal),
     xmlelement("commission", comm),
     XMLComment('Dieses XML-Dokument wurde am '||to_char(sysdate, 'DD.MM.YYYY HH24:MI') ||' generiert.')
    )
   )
  indent
 ) as xml
from scott.emp
/

XML
----------------------------------------------------------------------
<employee>
  <ename id="7369" hire-date="1980-12-17">
    <name>SMITH</name>
    <CLERK/>
    <salary>800</salary>
    <commission/>
    <!--Dieses XML-Dokument wurde am 03.11.2008 11:18 generiert.-->
  </ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20">
    <name>ALLEN</name>
    <SALESMAN/>
    <salary>1600</salary>
    <commission>300</commission>
    <!--Dieses XML-Dokument wurde am 03.11.2008 11:18 generiert.-->
  </ename>
</employee>

:
Für heute soll's das sein. Beim nächsten Mal geht's dann mit Themen wie XML-Hierarchien, komplexen Views oder XML-Zeichensätzen (Encoding) weiter ...
Okay ... this should be enough for today ... We'll continue in the next blog posting with advanced topics like hierarchies, complex Views or how to produce an XML encoding clause ...

Kommentare:

M. Meyer hat gesagt…

>>Einschub: Kann man das ganze auch eingerückt darstellen (pretty print)? Ja, das geht - aber erst ab Oracle11g (11.1.0).

Unter 10g kann man dafür die extract()-Methode des XMLTYPE nutzen. Beispiel für's HR-Schema:

select
xmlelement(
"employee",
xmlelement(
"name",
XMLAttributes(
t.employee_id as "id",
t.hire_date as "hire-date"
),
t.first_name
)
).extract('*').getclobval() from hr.employees T
WHERE ROWNUM<=3;

Carsten Czarski hat gesagt…

Stimmt absolut!

Nur hier bitte aufpassen - EXTRACT parst das Dokument einmal; das "Pretty Printing" kostet so also auch richtig was in Sachen CPU.

Viele Grüße

-Carsten

teddi hat gesagt…

Super Tutorial Carsten,

vielen Dank. Zumal ich genau diese Erklärungen gemacht habe für ein akademisches Projekt. jedoch habe ich schon viel früher mit einer Sache zu kämpfen nämlich das Registrieren eines auf meinem Laufwerk gespeichertes XSD-Schema. Wenn Ich das mit "dbms_xmlschema.registerSchema" tue erhalte ich: ora-22288 als Fehlermeldung zurück.
Ich muss auch erwähnen, dass ich in meinem Schema eine "uri" statt eine "url" habe.
Ich bin für jede Hilfe dankbar.
Danke.

Carsten Czarski hat gesagt…

Hallo teddi,

das XML Schema muss für die REGISTERSCHEMA-Aufrufe als CLOB oder als BFILE übergeben werden - ein CLOB muss sich in der Datenbank befinden; ein BFILE muss ein Verzeichnis auf dem Datenbankrechner sein. Wie es dann geht, ist hier beschrieben ...

http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb05sto.htm#i1032806


Beste Grüße

-Carsten

teddi hat gesagt…

Lieber Carsten,
sollte das Einrücken nicht auch mit: SYS_XMLGEN funktionieren?

Ich habe folgendes Statement in SQL Developper geschrieben:
select SYS_XMLGEN(
XMLAgg(
xmlelement("emp", xmlattributes(f.empno as nr),
xmlelement("name", f.ename),
xmlelement("job", f.job),
xmlelement("chef", f.mgr)
)), XMLFormat.createformat('mitarbeiter', 'NO_SCHEMA', null)) as Erg
from emp f where f.deptno= 20;

Das Ergebnis war nur in einer Zeile dargestellt. Und sieht dann so aus:
ERG
--------------------------------------------------------------------------------


JONESMANAGER7839SCOTTANALYST7566


Habe ich was übersehen?
Danke für deine Erleuchtungen.
LG
Teddi

Carsten Czarski hat gesagt…

Hallo Teddi,

das habe ich gerade mit SQL*Plus auf 11.2.0.3 getestet - dort werden die Tags ordnungsgemäß angezeigt. In welcher Umgebung hast Du den Test denn gemacht ...?

Beste Grüße

-Carsten

Anonym hat gesagt…

Lieber Carsten ersten entschuldige bitte, dass ich mich seitdem nichtt mehr gemeldet habe.
Das Generieren der XML-Datei ist viel aufwändiger als ich am Anfang vermutete.
Um auf deiner Frage vom Nov. zurück zu kommen habe ich das ganze unter sql Developper erstellt. Außerdem musste ich festellen, dass ich mit der SQL/XML Bibliothek ein großes Performance Problem habe (beim Erstellen eine größere Datei) als mit dbms_xmltype. Ich bin dabei mich darin einzuarbeiten.
LG
Teddi

Carsten Czarski hat gesagt…

Hallo Teddi,

normalerweise sind die SQL/XML Funktionen zur XML-Erzeugung das schnellste, was die Datenbank hat.

Wie generierst Du denn das XML-Dokument ...? Manchmal kommt es vor, dass es durch einen ungeschickten Funktionsaufruf nochmals geparst wird ... und das ist dann langsam ...

Wenn Du möchtest, können wir das ja offline weiterverfolgen - schreib' mich einfach per Mail an ...

Beste Grüße

-Carsten

Anonym hat gesagt…

Super!!
Finde ich ne gute Idee Carsten ich schreibe dir heute Abend an die im Impressum angegebene Adresse.
Danke für dein Angebot!

LG
Teddi

Anonym hat gesagt…

Hallo,
wenn man bisher unter 10g .extract('*').getclobval() benutzt hat muss man beim Umstieg auf 11g aufpassen, denn das funktioniert dann nicht mehr. Wenn man das XML dann ins Filesystem schreibt kann es sein, dass UTL_FILE.MAX_LINESIZE überschritten wird, da kein Linebreak mehr kommt. Also beim Umstieg auf serialize wechseln.

LG
Magnus

Beliebte Postings