Advertisements
RSS

Using the Oracle Database to store and present XML data

22 Sep

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

 

Advertisements
 
Leave a comment

Posted by on 22-09-2016 in Oracle

 

Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: