SQL HTTPPOSTCLOB in Verbindung mit XMLTABLE

[oulbrich]

<trac sq="164"><esr d="2021-03-23T13:42:23" tn="900000041">Hallo,

ich bekomme über ein SQL Webservice Anfrage (HTTPPOSTCLOB) eine Antwort als XML String. Den würde ich gerne in der selben Anfrage parsen und als Tabelle ausgeben. Geht das? Wenn ja wie stelle ich das an. <tag>
Das ist die Antwort vom Webservice. Mit interessierung nur die mit TAG

<trac sq="164"> <result rc="OK">
  <usermessage>Summe falsch: 528.9</usermessage>
  <warning>w-#T_DIFF 8.50 PayA[].Amt-ESR.T</warning>
 </result>
 <esr d="2021-03-23T13:42:23" tn="900000041">
 <fis tid="16">
  <code>V0;7831130;Kassenbeleg-V1;Beleg^528.90_0.00_0.00_0.00_0.00^537.40:Bar;16;9;2021-03-23T12:42:23.000Z;2021-03-23T12:42:23.000Z;ecdsa-plain-SHA256;unixTime;O9q2KbD0LlUrsLPlXl8gv9hOXwB829VM7ILhr6e2rFFgyil5VP7IAupwVcuExDh0q1Gk5pBapdEUY19z42IXLeEVVbX18w==;AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA==</code>
  <tag label="TrNr:" value="16" name="FN">
  <tag label="Beg.:" value="2021-03-23 13:42:23" name="StartD">
  <tag label="Ende:" value="2021-03-23 13:42:23" name="FinishD">
  <tag label="TSE :" value="0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef" name="Serial">
  <tag label="SigZ:" value="9" name="SignCnt">
  <tag label="Sign:" value="O9q2KbD0LlUrsLPlXl8gv9hOXwB829VM7ILhr6e2rFFgyil5VP7IAupwVcuExDh0q1Gk5pBapdEUY19z42IXLeEVVbX18w==" name="Sign">
 </tag></tag></tag></tag></tag></tag></fis>
</esr></trac>

Gruss Olaf</tag></esr></trac>

[Fuerchau]

Wer hat denn sowas verbrochen.
Dein Problem ist, dass die tag-Einträge geschachtelt sind.
Per SQL mit XPath musst du dann einen Union bauen, der alle tag-Elemente selektiert:

/trac/esr/fis/tag
/trac/esr/fis/tag/tag
/trac/esr/fis/tag/tag/tag
/trac/esr/fis/tag/tag/tag/tag
/trac/esr/fis/tag/tag/tag/tag/tag
/trac/esr/fis/tag/tag/tag/tag/tag/tag

Was willst du machen, wenn mal mehr als 6 Stufen vorkommen?
Besser wäre es, wenn jeder tag auch einen /tag hätte.

[oulbrich]

Besser wäre es, wenn jeder tag auch einen /tag hätte.

Ja das wäre besser. Aber der Webservice mach das halt so. Vorteil ist es ist immer die gleiche Anzahl . Wie lese ich aber die Attribute aus? Mit Path @Value ?

[Andreas_Prouza]

Genau beim PATH '/trac/esr/fis/tag@value'.
Wie genau das mit XMLTABLE gemacht wird, gibt es hier viele Beispiele.

Tust mir aber leid, dass du mit so einer Struktur arbeiten musst.
Ist so als ob man 10 Geschirrspüler kauft um 10 Teller zu waschen ... k.a. warum mir gerade dieses Beispiel einfällt :-)

lg Andreas

[Fuerchau]

Nun, wenn die Teller so groß sind, dass nur einer rein passt aber alle gleichzeitig gereinigt werden müssen, hast du keine andere Chance. Oder du stellst eben 10 Leute ein und schickst sie den Rest des Tages in Kurzarbeit.

[oulbrich]

ist das dann so korrekt:

SELECT *
FROM XMLTABLE('/TraC'
PASSING XMLPARSE(
DOCUMENT mylib.xml_var
)
COLUMNS
Fis varchar(256) PATH 'TraC/Fis/Tag/@SQ',
Tag varchar(256) PATH 'TraC/Fis/Tag/@Label'
) AS RESULT;

[Fuerchau]

Wo ist denn @SQ versteckt;-)?

[oulbrich]

Ganz oben im XML.
Das kann auch auch weg. Ich brauchen nur die TAG und davon das Label und Value.
Das klappt auch. Was mir noch fehlt es der Union.
Ich habe mir einen kleinen Test gebaut. Der funktioniert aber nur mit 1 Satz. Habe Problem den XML code ins Forum zustellen deswegen mit -

create or replace variable mylib.xml_var varchar(10000) ccsid 1208;

values mylib.xml_var;

set mylib.xml_var =
'-TraC>
-Fis>
-Tag Value="16" Label="122844488845"/>
-Tag Value="20" Label="11122888845"/>
-/Fis>
-/TraC>'
';


SELECT *
FROM XMLTABLE('/TraC'
PASSING XMLPARSE(
DOCUMENT mylib.xml_var
)
COLUMNS
Wert1 varchar(256) PATH '/TraC/Fis/Tag/@Label[1]'
,Wert2 varchar(256) PATH '/TraC/Fis/Tag/@Value[1]'
) AS RESULT
;

[B.Hauser]

Wofür brauchst Du denn einen Union?
Welche Informationen willst Du denn auslesen?

Das folgende Beispiel bringt Dir sq und die Label, Werte und Namen von allen 6 Leveln zurück

Select *
   From Xmltable('/trac' 
                 Passing Xmlparse(Document YourSchema.Gblxmlvar) 
                 Columns SQ VarChar(10)  Path '@sq',
                         Label1 VarChar(25)  Path './esr/fis/tag/@label', 
                         Value1 VarChar(125) Path './esr/fis/tag/@value',
                         Name1  VarChar(25)  Path './esr/fis/tag/@name',
                         Label2 VarChar(25)  Path './esr/fis/tag/tag/@label',
                         Value2 VarChar(125) Path './esr/fis/tag/tag/@value',
                         Name2  VarChar(25)  Path './esr/fis/tag/tag/@name',
                         Label3 VarChar(25)  Path './esr/fis/tag/tag/tag/@label', 
                         Value3 VarChar(125) Path './esr/fis/tag/tag/tag/@value',
                         Name3  VarChar(25)  Path './esr/fis/tag/tag/tag/@name',
                         Label4 VarChar(25)  Path './esr/fis/tag/tag/tag/tag/@label',
                         Value4 VarChar(125) Path './esr/fis/tag/tag/tag/tag/@value',
                         Name4  VarChar(25)  Path './esr/fis/tag/tag/tag/tag/@name',
                         Label5 VarChar(25)  Path './esr/fis/tag/tag/tag/tag/tag/@label', 
                         Value5 VarChar(125) Path './esr/fis/tag/tag/tag/tag/tag/@value',
                         Name5  VarChar(25)  Path './esr/fis/tag/tag/tag/tag/tag/@name',
                         Label6 VarChar(25)  Path './esr/fis/tag/tag/tag/tag/tag/tag/@label',
                         Value6 VarChar(125) Path './esr/fis/tag/tag/tag/tag/tag/tag/@value',
                         Name6  VarChar(25)  Path './esr/fis/tag/tag/tag/tag/tag/tag/@name')
                 As Result;

Birgitta

[Fuerchau]

Den Union benötige ich dann, wenn ich die Werte zeilenweise verarbeiten will.
Von den Zeiten der Pseudoarrays (sprich Denormalisierung) sind wir doch eigentlich schon weg;-).

Allerdings könnte man nun deinen SQL mit dem hier schon mal veröffentlichten

select ...
cross join lateral (
) as tabelle(...)

ergänzen um doch wieder Zeilenzu bekommen.

[oulbrich]

@Brigitta Ich habe das genauso gemacht. Es werden aber 0 Sätze ausgewählt.

Hier die Testumgebung. Bei den XML Daten habe ich den Anfang durch eine # ersetzt sonst kann ich das nicht posten.


create or replace variable mylib.xml_var1 varchar(10000) ccsid 1208;


values mylib.xml_var1;


set mylib.xml_var1 =
'#TraC SQ="164">
#Result RC="OK">
#UserMessage>Summe falsch: 528.9#/UserMessage>
#Warning>w-#T_DIFF 8.50 PayA.Amt-ESR.T#/Warning>
#/Result>
#ESR D="2021-03-23T13:42:23" TN="900000041"/>
#Fis TID="16">
#Code>V0;7831130;Kassenbeleg-V1;Beleg^528.90_0.00_0.00_0.00_0.00^537.40:Bar;16; 9;2021-03-23T12:42:23.000Z;2021-03-23T12:42:23.000Z;ecdsa-plain-SHA256;unixTime;O9q2KbD0LlUrsLPlXl8gv9hOXwB829VM7I Lhr6e2rFFgyil5VP7IAupwVcuExDh0q1Gk5pBapdEUY19z42IX LeEVVbX18w==;AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA==
#Tag Label="TrNr:" Value="16" Name="FN"/>
#Tag Label="Beg.:" Value="2021-03-23 13:42:23" Name="StartD"/>
#Tag Label="Ende:" Value="2021-03-23 13:42:23" Name="FinishD"/>
#Tag Label="TSE :" Value="0123456789abcdef0123456789abcdef0123456789abcdef01 23456789abcdef" Name="Serial"/>
#Tag Label="SigZ:" Value="9" Name="SignCnt"/>
#Tag Label="Sign:" Value="O9q2KbD0LlUrsLPlXl8gv9hOXwB829VM7ILhr6e2rFFgyil5VP 7IAupwVcuExDh0q1Gk5pBapdEUY19z42IXLeEVVbX18w==" Name="Sign"/>
#/Fis>
#/TraC>';


Select *
From Xmltable('/trac'
Passing Xmlparse(Document mylib.xml_var1)
Columns SQ VarChar(10) Path '@sq',
Label1 VarChar(25) Path './esr/fis/tag/@label',
Value1 VarChar(125) Path './esr/fis/tag/@value',
Name1 VarChar(25) Path './esr/fis/tag/@name',
Label2 VarChar(25) Path './esr/fis/tag/tag/@label',
Value2 VarChar(125) Path './esr/fis/tag/tag/@value',
Name2 VarChar(25) Path './esr/fis/tag/tag/@name',
Label3 VarChar(25) Path './esr/fis/tag/tag/tag/@label',
Value3 VarChar(125) Path './esr/fis/tag/tag/tag/@value',
Name3 VarChar(25) Path './esr/fis/tag/tag/tag/@name',
Label4 VarChar(25) Path './esr/fis/tag/tag/tag/tag/@label',
Value4 VarChar(125) Path './esr/fis/tag/tag/tag/tag/@value',
Name4 VarChar(25) Path './esr/fis/tag/tag/tag/tag/@name',
Label5 VarChar(25) Path './esr/fis/tag/tag/tag/tag/tag/@label',
Value5 VarChar(125) Path './esr/fis/tag/tag/tag/tag/tag/@value',
Name5 VarChar(25) Path './esr/fis/tag/tag/tag/tag/tag/@name',
Label6 VarChar(25) Path './esr/fis/tag/tag/tag/tag/tag/tag/@label',
Value6 VarChar(125) Path './esr/fis/tag/tag/tag/tag/tag/tag/@value',
Name6 VarChar(25) Path './esr/fis/tag/tag/tag/tag/tag/tag/@name')
As Result;

select mylib.xml_var1 From SYSIBM.SYSDUMMY1

[oulbrich]

@Fuerchau. Wie würde das mit dem CROSS JOIN LATERAL in meinem Bsp. aussehen ? Brauche ich dann keine Union mehr?