4. November 2014

XML-Strukturen analysieren - mit SQL!

Analyze XML documents ... with SQL!
In diesem Blog Posting schreibe ich etwas über die "Analyse" von XML-Dokumenten mit SQL-Mitteln. Das Extrahieren einzelner Inhalte aus XML mit SQL-Funktionen wie XMLTABLE (oder früher: EXTRACTVALUE) habe ich ja schon oft behandelt. Um damit arbeiten zu können, muss man jedoch die Namen der XML-Tags, für die man sich interessiert, deren Position und ggfs. auch deren Namespace kennen. De-jure gibt es zu jedem XML-Dokument eine Dokumentation oder gar ein XML Schema, aus dem man diese Informationen entnehmen kann, de-facto jedoch ...
Daher gibt es heute einige Beispiele für SQL-Abfragen in XML-Dokumenten, die keine Inhalte extrahieren, sondern Informationen über den Aufbau. Vielleicht kann der eine oder andere von euch das gelegentlich gebrauchen.
Wenn die SQL-Funktionen XMLQUERY oder XMLTABLE (ich selbst verwende eigentlich nur XMLTABLE) verwendet werden, so wird zur Selektion innerhalb des XML-Dokumentes XPath oder XQuery genutzt. Neben der reinen Navigation im XML bietet diese Abfragesprache allerdings auch Funktionen an, mit denen man den Namen des Tags (name()), den Namespace (namespace-uri()) oder andere Dinge erfahren kann. Damit bauen wir ein einfaches Beispiel: Zu einem gegebenen XML-Dokument soll die SQL-Abfrage alle Tags auflisten.
select tag from xmltable(
  '//*'
  passing xmltype(
   '<doc>
     <tag1>
      <tag1a>Text</tag1a>
     </tag1>
     <tag2/>
     <tag2/>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'name()'
)

TAG
----------
doc
tag1
tag1a
tag2
tag2

5 Zeilen ausgewählt.
Die XMLTABLE-Funktion erhält neben dem XML-Dokument (welcher hier als Literal direkt in die Abfrage eingebaut wurde) den XPath-Ausdruck //* übergeben - dieser selektiert alle Tags auf allen Hierarchieebenen. In der COLUMNS-Klausel, welche die Informationen auf die Ergebnisspalten der SELECT-Abfrage abbildet, wird dann die XPath-Funktion name() verwendet; im Ergebnis wird für jedes vorkommende XML-Tag eine Zeile generiert, die dessen Namen enthält. Wie man an tag2 erkennen kann, wird wirklich für jedes Tag eine Zeile gebildet - wenn man nur eindeutige Namen haben möchte, kann man dies mit einem SELECT DISTINCT erreichen; auch das Zählen der Vorkommen ist so natürlich überhaupt kein Problem mehr.
select tag, count(*) from xmltable(
  '//*'
  passing xmltype(
   '<doc>
     <tag1>
      <tag1a>Text</tag1a>
     </tag1>
     <tag2/>
     <tag2/>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'name()'
)
group by tag
 
TAG          COUNT(*)
---------- ----------
doc                 1
tag1                1
tag2                2
tag1a               1

4 Zeilen ausgewählt.
Auch Namespace-Informationen (Blog Posting) lassen sich auf diese Weise extrahieren. Die folgende SQL-Abfrage zeigt die Anwendung der XPath-Funktionen local-name() und namespace-uri().
select 
  fulltag, tag, nsuri
from xmltable(
  '//*'
  passing xmltype(
   '<doc xmlns="http://mein-namespace/a" xmlns:ns1="http://mein-namespace/b">
     <ns1:tag>Text</ns1:tag>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'local-name()',
    fulltag varchar2(10) path 'name()',
    nsuri   varchar2(30) path 'namespace-uri()'
)

FULLTAG    TAG        NSURI
---------- ---------- ------------------------------
doc        doc        http://mein-namespace/a
ns1:tag    tag        http://mein-namespace/b

2 Zeilen ausgewählt.
Das ist insbesondere hilfreich, wenn man die Namespaces nicht genau kennt - die Deklaration derselben erfolgt zwar meist zu Beginn des XML-Dokumentes, das muss aber nicht so sein; es ist nach dem XML-Standard durchaus möglich, dass mitten im Dokument ein Tag mit einer neuen Namespace-Deklaration auftaucht.
Als nächstes wollen wir Informationen über den Kontext eines Tags herausfinden. XPath erlaubt nicht nur die fast immer verwendete Navigation zu den Child-Elementen, sondern unter anderem auch die Navigation zu den Elternelementen. Die folgende SQL-Abfrage zeigt, wie das geht. Hier wird aber nicht mehr mit reinem XPath gearbeitet; vielmehr packen wir unsere Aufgabe in eine XQuery-Abfrage.
select 
  tagname, 
  parentname,
  count(*) occurrences
from xmltable(
  'for $x in //*
   return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
  passing xmltype(
    '<root-tag>
      <level1-tag><level2-tag/></level1-tag>
      <level1-tag><level2-tag/></level1-tag>
      <level1-tag/>
     </root-tag>')
  columns
    tagname    varchar2(15) path '/r/t',
    parentname varchar2(15) path '/r/p'
)
group by tagname, parentname
/

TAGNAME         PARENTNAME      OCCURRENCES
--------------- --------------- -----------
root-tag                                  1
level1-tag      root-tag                  3
level2-tag      level1-tag                2
Wie man sehen kann, wird im XQuery Ausdruck für jedes gefundene XML-Tag ein "neues" XML-Dokument zusammengebaut, und zwar von der Struktur "<r><t>{tag-name}</t><p>{parent-tag-name}</p></r>". Dieses wird dann an die COLUMNS-Klausel weitergegeben; welche logischerweise innerhalb der Tags <r>, <t> and <p> navigiert und deren Inhalte auf die relationalen Ergebnisspalten abbildet. Der XQuery-Ausdruck selbst selektiert für jedes Element dessen Namen, dann navigiert er mit dem Ausdruck parent::* zum Elternknoten und selektiert dessen Namen. Wir erhalten zu jedem XML-Tag sein Parent-Tag. Und da wir in der Oracle-Datenbank sind, wissen wir sofort, was nun zu tun ist ...
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(
      '<root-tag>
        <level1-tag><level2-tag/></level1-tag>
        <level1-tag><level2-tag/></level1-tag>
        <level1-tag/>
       </root-tag>')
    columns
      tagname    varchar2(15) path '/r/t',
      parentname varchar2(15) path '/r/p'
  )
  group by tagname, parentname
)
select 
  lpad(' ', level * 2, ' ') || tagname || ' ('||occurrences||')' as tag
from tags
start with parentname is null
connect by prior tagname = parentname
/

TAG
--------------------------------------------------------------
  root-tag (1)
    level1-tag (3)
      level2-tag (2)

3 Zeilen ausgewählt.
Das ist doch gar nicht übel, oder ...? Eine SQL-Abfrage gibt uns einen Überblick über die Struktur im XML-Dokument. Das wollen wir nun an einem größeren XML-Dokument testen. Am besten nehmen wir eins, was die Datenbank bereits hat. Und zwar speichert die XML DB die Konfiguration der Protokollserver (FTP, HTTP, WebDAV) in einem XML-Dokument ab. Man kann es mit der PL/SQL-Funktion DBMS_XDB.CFG_GET abrufen.
SQL> select dbms_xdb.cfg_get().getclobval() from dual

DBMS_XDB.CFG_GET().GETCLOBVAL()
--------------------------------------------------------------------------------
<xdbconfig xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns:xsi="http://w
ww.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/x
db/xdbconfig.xsd http://xmlns.oracle.com/xdb/xdbconfig.xsd">
  <sysconfig>
    <acl-max-age>15</acl-max-age>
    <acl-cache-size>32</acl-cache-size>
    <invalid-pathname-chars/>
    <case-sensitive>true</case-sensitive>
    <call-timeout>6000</call-timeout>
    <max-link-queue>65536</max-link-queue>
    <max-session-use>100</max-session-
Nun wenden wir die obige SQL-Abfrage auf dieses XML-Dokument an.
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(dbms_xdb.cfg_get().getclobval())
    columns
      tagname    varchar2(30) path '/r/t',
      parentname varchar2(30) path '/r/p'
  )
  group by tagname, parentname
  )
select 
  lpad(' ', level * 2, ' ') || tagname || ' ('||occurrences||')' as tag
from tags
start with parentname is null
connect by prior tagname = parentname
/

TAG
------------------------------------------------------------
  xdbconfig (1)
    sysconfig (1)
      acl-cache-size (1)
      acl-evaluation-method (1)
:
      persistent-sessions (1)
      protocolconfig (1)
        common (1)
          extension-mappings (1)
:
            mime-mappings (1)
              mime-mapping (51)
                extension (51)
                mime-type (51)
:
                  servlet-schema (2)
              servlet-mappings (1)
                servlet-mapping (6)
                  servlet-name (6)
                  servlet-pattern (6)
            welcome-file-list (1)
              welcome-file (2)
      resource-view-cache-size (1)
      xdbcore-loadableunit-size (1)
      xdbcore-log-level (1)
      xdbcore-logfile-path (1)
      xdbcore-xobmem-bound (1)

92 Zeilen ausgewählt.
Mit anderen hierarchischen Funktionen kann nun auch problemlos der komplette Pfad zu den XML-Tags ausgegeben werden; und man sieht sehr schön, wo ein bestimmtes XML-Tag steht und wie man es selektieren muss. Diese SQL-Abfrage können wir nun nochmals erweitern und damit gezielt nach bestimmten Tags suchen: Die folgende Abfrage zeigt alle Tags namens session-timeout mitsamt deren vollständigen Navigationspfad im XML-Dokument an. Der komplette Pfad wurde mit der SQL-Funktion SYS_CONNECT_BY_PATH generiert - das hat mit XML nichts zu tun; diese Funktion kann in jeder hierarchischen Abfrage genutzt werden.
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(dbms_xdb.cfg_get().getclobval())
    columns
      tagname    varchar2(30) path '/r/t',
      parentname varchar2(30) path '/r/p'
  )
  group by tagname, parentname
), hierarchie as (
  select 
    tagname,
    sys_connect_by_path(tagname, '/') as pfad
  from tags
  start with parentname is null
  connect by prior tagname = parentname
)
select pfad 
from hierarchie
where tagname = 'session-timeout'
/

PFAD
--------------------------------------------------------------------------------
/xdbconfig/sysconfig/protocolconfig/common/session-timeout
/xdbconfig/sysconfig/protocolconfig/ftpconfig/session-timeout
/xdbconfig/sysconfig/protocolconfig/httpconfig/session-timeout

3 Zeilen ausgewählt.
Das Ergebnis kann direkt verwendet werden, wie die Probe zeigt ...
select timeout
from xmltable (
  xmlnamespaces (DEFAULT 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'), 
  '/xdbconfig/sysconfig/protocolconfig/ftpconfig/session-timeout'
  passing dbms_xdb.cfg_get()
  columns timeout path 'text()'
);

TIMEOUT
-----------------------------------------------------
6000

1 Zeile wurde ausgewählt.
Wie haben wir den Namespace herausgefunden ...? Ach ja ...
select distinct nsuri
from xmltable(
  '//*'
  passing dbms_xdb.cfg_get()
  columns
    nsuri   varchar2(50) path 'namespace-uri()'
)
/ 

NSURI
--------------------------------------------------
http://xmlns.oracle.com/xdb/xdbconfig.xsd

1 Zeile wurde ausgewählt.
Damit soll es genug sein. Beachtet bitte, dass diese Art der XML-Abfragen von der Datenbank nicht besonders optimiert werden können; es findet stets ein XML-Parsing statt. Auf sehr großen Dokumentbeständen oder großen Einzeldokumenten können diese Abfragen also durchaus länger dauern. Aber ansonsten steht der Analyse von XML - mit SQL nichts im Wege.
This blog posting will be about "analysis" of XML documents - in the database, with the SQL language. I have blogged several times about how to extract information from XML documents using the SQL functions XMLTABLE or EXTRACTVALUE (in earlier database versions). But to work with these functions, one must know, where the information of interest is located within the XML document. One must know the name of the XML tag, its position within the hierarchy, its namespace and so on. Of course, there is always good and comprehensive documentation (or an XML Schema) which contains this kind of information ... but I'll proceed anyway ...
So, today you'll see SQL queries on XML documents, which don't extract actual content, but information about the XML structure. Perhaps this is useful for somebody - sometime.
Within the SQL functions XMLQUERY or XMLTABLE (I hardly use XMLQUERY myself), XML tags are being selected. For this selection, either the XQuery or the more simple XPath language can be used. Beyond the pure navigational features, XPath also contains some functions to obtain information about the XML structure: name() retrieves the name of the current XML tag, namespace-uri() gets the full XML namespace information, and there are some more. With this in mind, we can build our first example. A SQL query is supposed to list the names of all XML tags.
select tag from xmltable(
  '//*'
  passing xmltype(
   '<doc>
     <tag1>
      <tag1a>Text</tag1a>
     </tag1>
     <tag2/>
     <tag2/>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'name()'
)

TAG
----------
doc
tag1
tag1a
tag2
tag2

5 rows selected.
For clarity, we pass the XML document directly into the XMLTABLE function as a literal. The XPath expression //* selects all XML tags in all hierarchy levels. This "intermediate XML result" will then go the the COLUMNS clause which maps it to the relational result set of the SQL query. In this case, the COLUMNS clause applies the name() function on each selected node. You'll see a row for each XML tag - including duplicates. To get only distinct tag names, we can utilize SQL features (SELECT DISTINCT); and counting the XML tags is also a very easy task now (these are the things I really like in the Oracle database - combine functionality and do awesome things.)
select tag, count(*) from xmltable(
  '//*'
  passing xmltype(
   '<doc>
     <tag1>
      <tag1a>Text</tag1a>
     </tag1>
     <tag2/>
     <tag2/>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'name()'
)
group by tag
 
TAG          COUNT(*)
---------- ----------
doc                 1
tag1                1
tag2                2
tag1a               1

4 rows selected.
We can also extract XML namespace information (blog posting about that). The following example illustrates the usage of local-name() and namespace-uri().
select 
  fulltag, tag, nsuri
from xmltable(
  '//*'
  passing xmltype(
   '<doc xmlns="http://mein-namespace/a" xmlns:ns1="http://mein-namespace/b">
     <ns1:tag>Text</ns1:tag>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'local-name()',
    fulltag varchar2(10) path 'name()',
    nsuri   varchar2(30) path 'namespace-uri()'
)

FULLTAG    TAG        NSURI
---------- ---------- ------------------------------
doc        doc        http://mein-namespace/a
ns1:tag    tag        http://mein-namespace/b

2 rows selected.
That is very useful, when you don't know all the used namespaces within your XML document. In most cases these are being declared at the beginning, but this is not mandatory. A namespace declaration can occur at any position in your XML document, so perhaps there is an XML tag from an unknown namespace - at the very end of the XML document ... you can't be sure. Of course, in pratice we always have an XML schema or a documentation, so this is all theoretical ...
Next, we'll extract information about the context of an XML tag. XPath does not only allow to navigate from a parent to a child, but also from a child to a parent - or from a tag to its sibling. Whereas the child axis is the default, there are other axes to navigate along. The following SQL query will use the parent axis in order to retrieve the name of each XML tag's parent. Since this is a bit more complex, we don't use pure XPath any more. We'll utilize XQuery syntax instead.
select 
  tagname, 
  parentname,
  count(*) occurrences
from xmltable(
  'for $x in //*
   return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
  passing xmltype(
    '<root-tag>
      <level1-tag><level2-tag/></level1-tag>
      <level1-tag><level2-tag/></level1-tag>
      <level1-tag/>
     </root-tag>')
  columns
    tagname    varchar2(15) path '/r/t',
    parentname varchar2(15) path '/r/p'
)
group by tagname, parentname
/

TAGNAME         PARENTNAME      OCCURRENCES
--------------- --------------- -----------
root-tag                                  1
level1-tag      root-tag                  3
level2-tag      level1-tag                2

3 rows selected.
I don't want to provide a full explanation of XQuery here (there are plenty of tutorials in the web), but you might see how it works. The XQuery expression "builds" another XML document (for each XML tag) with the simple structure "<r><t>{tag-name}</t><p>{parent-tag-name}</p></r>". This is being passed to the COLUMNS clause. The XPath expressions in the COLUMNS clause now selects items from the "intermediate" XML document, so they navigate within the <r>, <t> and <p> tags. Looking at this queries' results and knowing that we are within the Oracle database, we should know, what the next step is ...
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(
      '<root-tag>
        <level1-tag><level2-tag/></level1-tag>
        <level1-tag><level2-tag/></level1-tag>
        <level1-tag/>
       </root-tag>')
    columns
      tagname    varchar2(15) path '/r/t',
      parentname varchar2(15) path '/r/p'
  )
  group by tagname, parentname
)
select 
  lpad(' ', level * 2, ' ') || tagname || ' ('||occurrences||')' as tag
from tags
start with parentname is null
connect by prior tagname = parentname
/

TAG
--------------------------------------------------------------
  root-tag (1)
    level1-tag (3)
      level2-tag (2)

3 rows selected.
Another nice instance of combining technology: A SQL query gives an overview on the structure of an XML document. Other technologies would require you to install tools or to author complex procedural code - and here we are doing all this with query language. Let's test this on more complex XML document - I'll take one which is already present in an Oracle database: The configuration of the XML DB protocol servers (FTP, HTTP, WebDAV) is being stored as XML within the database. We can retrieve it with the PL/SQL function DBMS_XDB.CFG_GET.
SQL> select dbms_xdb.cfg_get().getclobval() from dual

DBMS_XDB.CFG_GET().GETCLOBVAL()
--------------------------------------------------------------------------------
<xdbconfig xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns:xsi="http://w
ww.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/x
db/xdbconfig.xsd http://xmlns.oracle.com/xdb/xdbconfig.xsd">
  <sysconfig>
    <acl-max-age>15</acl-max-age>
    <acl-cache-size>32</acl-cache-size>
    <invalid-pathname-chars/>
    <case-sensitive>true</case-sensitive>
    <call-timeout>6000</call-timeout>
    <max-link-queue>65536</max-link-queue>
    <max-session-use>100</max-session-
    :
Now, let's apply the above query on that document ...
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(dbms_xdb.cfg_get().getclobval())
    columns
      tagname    varchar2(30) path '/r/t',
      parentname varchar2(30) path '/r/p'
  )
  group by tagname, parentname
  )
select 
  lpad(' ', level * 2, ' ') || tagname || ' ('||occurrences||')' as tag
from tags
start with parentname is null
connect by prior tagname = parentname
/

TAG
------------------------------------------------------------
  xdbconfig (1)
    sysconfig (1)
      acl-cache-size (1)
      acl-evaluation-method (1)
:
      persistent-sessions (1)
      protocolconfig (1)
        common (1)
          extension-mappings (1)
:
            mime-mappings (1)
              mime-mapping (51)
                extension (51)
                mime-type (51)
:
                  servlet-schema (2)
              servlet-mappings (1)
                servlet-mapping (6)
                  servlet-name (6)
                  servlet-pattern (6)
            welcome-file-list (1)
              welcome-file (2)
      resource-view-cache-size (1)
      xdbcore-loadableunit-size (1)
      xdbcore-log-level (1)
      xdbcore-logfile-path (1)
      xdbcore-xobmem-bound (1)

92 rows selected.
We now get an overview on the hierarchy of the XML DB protocol server configuration; without a tool - just with SQL. Using the SYS_CONNECT_BY_PATH function, we can let the database even generate a full path to each XML tag. And with a WHERE clause we can look for specific tags of interest. The following query shows this: We are interested in the full path to a tag named session-timeout.
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(dbms_xdb.cfg_get().getclobval())
    columns
      tagname    varchar2(30) path '/r/t',
      parentname varchar2(30) path '/r/p'
  )
  group by tagname, parentname
), hierarchie as (
  select 
    tagname,
    sys_connect_by_path(tagname, '/') as pfad
  from tags
  start with parentname is null
  connect by prior tagname = parentname
)
select path
from hierarchie
where tagname = 'session-timeout'
/

PATH
--------------------------------------------------------------------------------
/xdbconfig/sysconfig/protocolconfig/common/session-timeout
/xdbconfig/sysconfig/protocolconfig/ftpconfig/session-timeout
/xdbconfig/sysconfig/protocolconfig/httpconfig/session-timeout

3 rows selected.
You can actually use this result, as the following test shows you.
select timeout
from xmltable (
  xmlnamespaces (DEFAULT 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'), 
  '/xdbconfig/sysconfig/protocolconfig/ftpconfig/session-timeout'
  passing dbms_xdb.cfg_get()
  columns timeout path 'text()'
);

TIMEOUT
-----------------------------------------------------
6000

1 row selected.
And how did we find out the namespace (which is used in the XMLNAMESPACES clause) ...?
select distinct nsuri
from xmltable(
  '//*'
  passing dbms_xdb.cfg_get()
  columns
    nsuri   varchar2(50) path 'namespace-uri()'
)
/ 

NSURI
--------------------------------------------------
http://xmlns.oracle.com/xdb/xdbconfig.xsd

1 row selected.
OK folks, enough for today. When playing with these features, keep in mind, that the database does not optimize these kinds of XML query for performance - so they might take longer for large XML documents or large document sets. But having this in mind, nothing should prevent you from analyzing your XML documents - with SQL!

Beliebte Postings