Het gemak van xmltype

Heel af en toe krijgt een 3rd party applicatie waar wij hier beheer voor doen een fout item via een xml message binnen.
Hiervoor biedt de applicatie een beheerscherm waar de xml aangepast kan worden en daarna opnieuw aangeboden kan worden.
Dat voldeed prima tot we er op een dag ineens 90 foute berichten kregen.

Een blik op de onderliggende Oracle database liet zien dat de berichten met foutmelding in een tabel opgeslagen worden.
Zowel de errorstack als de message staan in een clob veld.
Wat we wilden automatiseren was een node uit de xml knippen waar het betreffende item uit de foutmelding in voor kwam.

Het wordt dus een update statement op de betreffende message tabel waarbij we de payload aanpassen:

update message_error_log lg
set lg.payload = ?

Eerst identificeren we de juiste berichten in de where clause. We weten het messagetype en de foutmelding:

 where lg.message_type = 1234
and    lg.exception_type = ‘com.exception.ItemNotFoundException’

Het vinden van het itemnummer in de foutmelding kan met ouderwets knippen en plakken:

to_char (substr (lg.stacktrace
,                instr (lg.stacktrace, ‘item does not exist:’) + 19
,                  (  instr (lg.stacktrace
,                            ‘at com.backend.process’
)
– 2
)
– (instr (lg.stacktrace, ‘item does not exist:’) + 19)
)
)

Eventueel zou bovenstaande ook met een reguliere expressie kunnen.

Om zeker te zijn dat het gevallen van dit specifieke probleem zijn controleren we of het itemnummer in de xml op de juiste plaats voorkomt:

and    extractvalue (xmltype (lg.payload)
,                       ‘/Message/Payload/Forecast/ItemDemand/itemNo[.=”‘
|| to_char (substr (lg.stacktrace
,                                        instr (lg.stacktrace, ‘item does not exist:’) + 19
,                                          (  instr (lg.stacktrace
,                                                    ‘at com.backend.process’
)
– 2
)
– (instr (lg.stacktrace, ‘item does not exist:’) + 19)
)
)
|| ‘”]’
) is not null

Eerst wordt hier de clob in een xmltype gezet: xmltype (lg.payload)
Vervolgens wordt een xpath expressie gemaakt met het item uit de foutmelding:

‘/Message/Payload/Forecast/ItemDemand/itemNo[.=”<itemnummer>”]’
Hiermee wordt gecontroleerd of het betreffende nummer op de juiste plaats in de xml voorkomt.
Deze waarde wordt dan met extractvalue eruit geplukt.

Nu hebben we de juiste rijen te pakken en komt het knippen in de xml:

 update message_error_log lg
set lg.payload =
deletexml (xmltype (lg.payload)
,                       ‘/Message/Payload/Forecast/ItemDemand[itemNo=”‘
|| to_char (substr (lg.stacktrace
,                                        instr (lg.stacktrace, ‘item does not exist:’) + 19
,                                          (  instr (lg.stacktrace
,                                                    ‘at com.backend.process’
)
– 2
)
– (instr (lg.stacktrace, ‘item does not exist:’) + 19)
)
)
|| ‘”]’
).getclobval ()

Eerst weer converteren naar xmltype: xmltype (lg.payload)
Dan met een iets andere xpath expressie op zoek naar de node waarin het item voorkomt (let op de plaats van de blokhaken!):
‘/Message/Payload/Forecast/ItemDemand[itemNo=”<itemnummer>”]’
Met deletexml wordt de betreffende node eruit geknipt.
Wat rest is de xmltype weer terug in een clob te veranderen, dit kan met de methode getclobval van het xmltype: .getclobval ()

Het eindresultaat:

update message_error_log lg
set lg.payload =
deletexml (xmltype (lg.payload)
,                       ‘/Message/Payload/Forecast/ItemDemand[itemNo=”‘
|| to_char (substr (lg.stacktrace
,                                        instr (lg.stacktrace, ‘item does not exist:’) + 19
,                                          (  instr (lg.stacktrace
,                                                    ‘at com.backend.process’
)
– 2
)
– (instr (lg.stacktrace, ‘item does not exist:’) + 19)
)
)
|| ‘”]’
).getclobval ()
where lg.message_type = 1234
and    lg.exception_type = ‘com.exception.ItemNotFoundException’
and    extractvalue (xmltype (lg.payload)
,                       ‘/Message/Payload/Forecast/ItemDemand/itemNo[.=”‘
|| to_char (substr (lg.stacktrace
,                                        instr (lg.stacktrace, ‘item does not exist:’) + 19
,                                          (  instr (lg.stacktrace
,                                                    ‘at com.backend.process’
)
– 2
)
– (instr (lg.stacktrace, ‘item does not exist:’) + 19)
)
)
|| ‘”]’
) is not null

In de applicatie konden we nu alle berichten opnieuw aanbieden en ze waren verwerkt.
Deze generieke oplossing kan vervolgens voor alle toekomstige gevallen gebruikt worden.
Dit bespaart tijd en is minder foutgevoelig dan het handmatige aanpassen.