February 28, 2005, 11:47 AM
gcoCreating report from XML Document in an DBMS CLOB fields
I need to create a report from an Oracle RDBMS table datasource.
The Table has multiple columns and one is of type CLOB. The
CLOB field contains XML document (or rather XML text). In order
to report on the XML document, I created a master file with the
XML DTD. I then merged the Oracle table's master file with the
DTD master file.
My problems is that I do not know how to reference the XML fields
from the combined master file in a report. I could not find any example of how
to reference XML field from WebFOCUS document.
I'll appreciate it very much if anyone can give me an example of how
to reference XML filed in a report.
BELOW IS AN EXAMPLE OF WHAT I DID (I got this example from WebFOCUS
manual). Help!!!
Table's Master File:
FILE=XMLPRO1 ,SUFFIX=SQLPRO ,$
SEGNAME=XMLPRO1 ,SEGTYPE=S0 ,$
FIELD=FLD1 ,FLD1 ,A2 ,A2 ,MISSING=ON ,$
FIELD=FLD2 ,FLD2 ,TX50 ,TX ,MISSING=ON ,$
FIELD=FLD3 ,FLD3 ,TX50 ,TX ,MISSING=ON ,$
XML DTD Master File:
FILENAME=BASEAPP/ORDER, SUFFIX=XML , $
SEGMENT=ORDER, SEGTYPE=S0, $
FIELDNAME=ORDER, ALIAS='Order', USAGE=A1, ACTUAL=A1, $
FIELDNAME=KEY, ALIAS='key', USAGE=A10, ACTUAL=A10, $
FIELDNAME=CUSTOMER, ALIAS='Customer', USAGE=A10, ACTUAL=A10, $
FIELDNAME=STATUS, ALIAS='Status', USAGE=A10, ACTUAL=A10, $
FIELDNAME=TOTALPRICE, ALIAS='TotalPrice', USAGE=A10, ACTUAL=A10, $
FIELDNAME=DATE, ALIAS='Date', USAGE=A10, ACTUAL=A10, $
FIELDNAME=PRIORITY, ALIAS='Priority', USAGE=A10, ACTUAL=A10, $
FIELDNAME=CLERK, ALIAS='Clerk', USAGE=A10, ACTUAL=A10, $
FIELDNAME=SHIPPRIORITY, ALIAS='ShipPriority', USAGE=A10, ACTUAL=A10,$
FIELDNAME=COMMENT, ALIAS='Comment', USAGE=A10, ACTUAL=A10, $
FILENAME=BASEAPP/PORDER, SUFFIX=XML , $ SEGMENT=PORDER, SEGTYPE=S0, $
FIELDNAME=PORDER, ALIAS='POrder', USAGE=A1, ACTUAL=A1, $
FIELDNAME=KEY, ALIAS='key', USAGE=A10, ACTUAL=A10, $
FIELDNAME=CUSTOMER, ALIAS='Customer', USAGE=A10, ACTUAL=A10, $
FIELDNAME=ADDRESS, ALIAS='Address', USAGE=A10, ACTUAL=A10, $
FIELDNAME=STATE, ALIAS='State', USAGE=A10, ACTUAL=A10, $
Combined Master file:
FILE=XMLPRO1 ,SUFFIX=SQLPRO ,$
SEGNAME=XMLPRO1 ,SEGTYPE=S0 ,$
FIELD=FLD1 ,FLD1 ,A2 ,A2 ,MISSING=ON ,$
FIELD=FLD2 ,FLD2 ,TX50 ,TX ,MISSING=ON ,$
FIELD=FLD3 ,FLD33,TX50 ,TX ,MISSING=ON ,$
SEGMENT=ORDER, SEGTYPE=S0,POSITION=FLD2,PARENT=XMLPRO1,SEGSUF=XML, $
FIELDNAME=ORDER, ALIAS='Order', USAGE=A1, ACTUAL=A1, $
FIELDNAME=KEY, ALIAS='key', USAGE=A10, ACTUAL=A10, $
FIELDNAME=CUSTOMER, ALIAS='Customer', USAGE=A10, ACTUAL=A10, $
FIELDNAME=STATUS, ALIAS='Status', USAGE=A10, ACTUAL=A10, $
FIELDNAME=TOTALPRICE, ALIAS='TotalPrice', USAGE=A10, ACTUAL=A10, $
FIELDNAME=DATE, ALIAS='Date', USAGE=A10, ACTUAL=A10, $
FIELDNAME=PRIORITY, ALIAS='Priority', USAGE=A10, ACTUAL=A10, $
FIELDNAME=CLERK, ALIAS='Clerk', USAGE=A10, ACTUAL=A10, $
FIELDNAME=SHIPPRIORITY, ALIAS='ShipPriority', USAGE=A10, ACTUAL=A10, $
FIELDNAME=COMMENT, ALIAS='Comment', USAGE=A10, ACTUAL=A10, $
SEGMENT=PORDER, SEGTYPE=S0,POSITION=FLD3,PARENT=XMLPRO1,SEGSUF=XML $
FIELDNAME=PORDER, ALIAS='Order', USAGE=A1, ACTUAL=A1, $
FIELDNAME=KEY, ALIAS='key', USAGE=A10, ACTUAL=A10, $
FIELDNAME=CUSTOMER, ALIAS='Customer', USAGE=A10, ACTUAL=A10, $
FIELDNAME=ADDRESS, ALIAS='Address', USAGE=A10, ACTUAL=A10, $
FIELDNAME=STATE, ALIAS='State', USAGE=A10, ACTUAL=A10, $