Because we investigated the possibilities to store our (old) BPM Human Task data outside the SOAINFRA database (for archive, metrics and search queries on short history) we looked into a few possibilities in a spike / PoC. Because the Task data is actually structured XML data of which we do not know yet what future needs would require, the most safe solution was to store the complete XML document in a datastore.

Luckily the Oracle database has the option to store XML data and use views to represent the data “the old fashion way”. So this design in high-level looks like this.

xmldata

First we create a table:

CREATE TABLE TEST_TAAK
( "ID" NUMBER,
"TAAK_ID" VARCHAR2(36 BYTE),
"VERSIE" VARCHAR2(4 BYTE),
"PAYLOAD" "XMLTYPE"
)

Then insert a HumanTask (task) XML element into the table.
To make sure we don’t get any errors like:

  • “ORA-31011: XML parsing failed”
  • “SQL Error: ORA-01704: string literal too long; Cause: The string literal is longer than 4000 characters.”

we declare a variable to hold the XML string before we update/insert it.

Declare vXmlStr xmltype:=xmltype('<task><title>My Task</title><payload><CaseNumber>Case-1</CaseNumber><DocumentUrl>http://mydocument</DocumentUrl><DocumentNaam>myDocument</DocumentNaam></payload><taskDefinitionURI>default/Process_1.0!1600.93239/htMyTask</taskDefinitionURI><ownerRole>MyCasus_1.0.Users</ownerRole><priority>3</priority><identityContext>jazn.com</identityContext><systemAttributes><xmlstuff>much stuff</xmlstuff><taskDefinitionName>htMyTask</taskDefinitionName><xmlstuff>more stuff</xmlstuff></systemAttributes><systemMessageAttributes><numberAttribute1>0.0</numberAttribute1></systemMessageAttributes><sca><applicationName>default</applicationName><xmlstuff>more stuff</xmlstuff></sca></task>');
Begin
  Update TEST_TAAK set PAYLOAD = vXmlStr where ID=1;
End;

taskxml1

CREATE OR REPLACE FORCE VIEW TEST_TAAK_VW ("TAAK_ID", "VERSIE", "XML_TITLE", "XML_TASKDEFINITIONNAME", "XML_PAYLOAD")
AS
SELECT TT.TAAK_ID
, TT.VERSIE
, XMLRGL.TITLE
, XMLRGL.TASKDEFINITIONNAME
, XMLRGL.PAYLOAD
FROM
TEST_TAAK TT
, XMLTABLE( '/task' PASSING TT.PAYLOAD
COLUMNS TITLE VARCHAR2(40) PATH 'title'
, TASKDEFINITIONNAME VARCHAR2(40) PATH 'systemAttributes/taskDefinitionName'
, PAYLOAD XMLTYPE PATH 'payload'
) AS XMLRGL;

And the result, voila:

 

taskxml2

 

Houd jij je kennis graag up to date?

Mis niets meer van onze kennisdocumenten, events, blogs en cases: ontvang als eerste het laatste nieuws in je inbox!

Fijn dat we je op de hoogte mogen houden!