Advertisements
RSS

Tag Archives: XMLDB

Using the Oracle Database to store and present XML data

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: ,

Transformation on XMLType in Oracle XML DB

When using a XMLType table/column in Oracle XML DB to store structured data you are able to perform transformations with the XMLTRANSFORM command. We have a requirement to remove all namespaces for the inserted XML content and decided to implement this mechanism in the database.

Probably piece of cake for a Oracle XML DB guru, I needed Google ;-)

CREATE TABLE XMLSTYLESHEETS
(
CODE VARCHAR2(6 CHAR) NOT NULL,
STYLESHEET "XMLTYPE" NOT NULL,
);

We insert the XSLT stylesheet for removing namespaces in the table:

INSERT INTO XMLSTYLESHEETS (CODE, STYLESHEET)
values ('REMNAM' ,'
<xsl:stylesheet version="1.0" xmlns:xsl=http://www.w3.org/1999/XSL/Transform>
<xsl:output indent="yes" method="xml" encoding="utf-8" omit-xml-declaration="yes"/>
<xsl:template match="*">
<xsl:element name="{local-name()}">
<xsl:apply-templates select="@* | node()"/>
</xsl:element>
</xsl:template>
<xsl:template match="@*">
<xsl:attribute name="{local-name()}">
<xsl:value-of select="."/>
</xsl:attribute>
</xsl:template>
<xsl:template match="comment() | text() | processing-instruction()">
<xsl:copy></xsl:copy>
</xsl:template>
</xsl:stylesheet>');

Then we create a trigger to

create or replace
trigger MYTRIGGER
BEFORE INSERT OR UPDATE ON MYTABLEWITHXML
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
-- remove namespaces from XMLTYPE input
-- DBMS_OUTPUT.PUT_LINE(:new.data_xml.GetClobVal());
SELECT XMLTRANSFORM(:new.data_xml, x.stylesheet)
INTO :new.data_xml
FROM XMLSTYLESHEETS x
WHERE x.CODE = 'REMNAM';
-- DBMS_OUTPUT.PUT_LINE(:new.data_xml.GetClobVal());
END;

Let’s test the trigger:

Insert into MYTABLEWITHXML(MYDATAXML)
values ('
<bk:book xmlns:bk="urn:loc.gov:books" xmlns:isbn="urn:ISBN:0-395-36341-6">
<bk:title>Cheaper by the Dozen</bk:title>
<isbn:number>1568491379</isbn:number>
</bk:book>');

When enabling the DBMS_OUTPUT lines we are able to validate the result of the trigger.
JDeveloper DBMS output

 
Leave a comment

Posted by on 13-08-2013 in Oracle, XMLDB, XSLT

 

Tags: , , , ,