Parsen einer XML Struktur mit mehrfachen Zeilen

Veröffentlicht von

4

Heute möchte ich eine Möglichkeit vorstellen, wie man in Oracle eine XML Struktur mit mehrfachen Einträgen parsen kann. So hat man z.B. folgende Struktur:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

<!--?xml version="1.0" encoding="WINDOWS-1252"?-->
 
 
    4711
    Angebot
 
 
    4712
    Angebot
 
 
    4713
    Auftrag
 
 
    4714
    Mail

Um diese oder eine vergleichbare Struktur nun in Oracle in einem SQL zu parsen und die Werte abzufragen, genügt ein auf den ersten Blick etwas komplizierteres SQL. Basierend auf der im Beitrag Konvertiere LONG RAW nach CLOB mit Zeichsatzkonvertierung dargestellten Tabelle und obiger XML Struktur im CLOB sieht das SQL dann so aus:

1
2
3
4
5
6
7
8
9
10

SELECT DOKID
FROM (select b.DOKID 
      from  kunden_temp t, 
            XMLTABLE('//*:DocList/*:Doc'
                   Passing xmltype.createxml( t.dokument )
                   COLUMNS "DOKID" VARCHAR2(80 CHAR) 
                        PATH '/*:Doc/*:DocId') b
     )
WHERE DOKID is not null
;

Liegt das CLOB in der Tabelle „kunden_temp“ schon als XMLTYPE vor, ist natürlich der Aufruf von xmltype.createxml() überflüssig.

Natürlich kann man Ende jede andere WHERE Bedingung hinzugefügt werden. Auch kann man das Parsing noch erweitern, um den DocType zu bekommen, wenn man nur z.B. alle Mails zu bekommen.

Anders sieht es aber aus, wenn eine XML Struktur mehrfachen Zeilen hat, d.h. mehrere Einträge kommen direkt hintereinander, auf einer Ebene vor. Z.B. folgende Struktur:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE TABLE xmltest (
  dokument xmltype
)
/
DELETE FROM xmltest;
INSERT INTO xmltest VALUES (xmltype(
'
  Rainer Hartwig
 
    Oracle 11g
    Oracle 12c
    Linux
 
'
))
/
commit;

Dann liefert obiges SQL – angepasst auf diese XML Struktur – die Fehlermeldung

ORA-19279: XPTY0004 - xquery dynamic TYPE mismatch expected singleton SEQUENCE

Um diese Struktur auszulesen, muss das SQL etwas erweitert werden zu:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT DOKID
FROM (SELECT b.DOKID
      FROM  xmltest t, 
            XMLTABLE('//blog'
                   Passing  t.dokument 
                   COLUMNS  themen XMLTYPE PATH 'themen') r,
                   XMLTABLE ('/themen/thema'
                      PASSING r.themen
                      COLUMNS "DOKID" VARCHAR2(80 CHAR) 
                        PATH '/thema') b
     )
WHERE DOKID IS NOT NULL;

und liefert dann die drei Zeilen

1
2
3

Oracle 11g
Oracle 12c
Linux

zurück.

Viel Erfolg.

4 thoughts on “Parsen einer XML Struktur mit mehrfachen Zeilen”

  1. Sodeli! Ich habe eine Lösung hierzu gebastelt, die für mich als SQL-Pur-Genießer und Programmierer besser ausschaut, weil XML-Pfad nur an einer Stelle angegeben werden muss:

    select D.*
    from
    xmltest T
    cross join
    xmltable(‚/blog/themen/thema‘
    passing T.dokument
    columns thema varchar(100) path ‚./text()‘) D;

    Antworten

  2. Zufälliger Weise habe ich so was Ähnliches die Tage für SQL Server probiert. Da kommt aber viel eleganter CROSS APPLY – Operator und NODES-Extraktionsfunktion zur Verwendung. Hierbei ein Code-Beispiel in T-SQL, wie man in einer Tabelle mit mehreren Zeilen sowohl skalare als auch XML-Werte mit eben mehreren Einträgen hat und diese abfragt:

    declare @Tab table (
    Id integer,
    xmlIrgendwas xml
    )

    insert into @Tab values
    (1, “),
    (2, “)

    select
    Id,
    T.C.value(‚@name‘, ‚varchar(max)‘)
    from @Tab
    cross apply xmlIrgendwas.nodes(‚/root/test‘) T(C)

    Raus kommt dann:

    1 aaa
    1 bbb
    2 ccc

    Nun will ich schauen, ob man das auch nicht in Oracle so realisieren kann…

    Antworten

    • Ähm… Die Web-Seite hat die XML-Werte im Insert-Statement raus geschnitten…

      insert into @Tab values
      (1, ‚<root><test name=“aaa“ /><test name=“bbb“ /></root>‘),
      (2, ‚<root><test name=“ccc“ /></root>‘)

      Antworten

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.