The audience this is targeted to are experienced Data Services Developers. You run this at your own risk!
NOTE: to all developers interested in this post. It would be helpful if you can provide or assist with further input on to the equivalent Microsoft SQL, also if you are familiar with XPATH any contribution you can make would be appreciated in reference to other transforms etc that you manage to pull.
XPATH - Cribsheet
How to Harvest Code from AL_LANGXMLTEXT
Why?
See where Variables are being used
See where bottle necks may be – Dataflows with too many Queryes
See all transform settings used for all DataFlows
Build a more logical BO Universe on the data that’s extracted
Etc….
Oracle was used for this example
Oracle = 11g
Data Services = 4
Approach:
1.Consolidate the data objects (XML construct) and place into a CLOB table
i. In Data Services Create a Dataflow with SQL Transform as a source and use the following sql expression. Ensure to change “NAME_OF_DATAFLOW” to the dataflow you want to test
select DBMS_XMLGEN.CONVERT(EXTRACT(xmltype('<?xml version="1.0"?><document>'||XMLAGG(XMLTYPE('<V>'|| DBMS_XMLGEN.CONVERT(text_value)|| '</V>')ORDER BY seqnum).getclobval()||'</document>'), '/document/V/text()').getclobval(),1) AS data_value
FROM AL_LANGXMLTEXT
WHERE OBJECT_NORMNAME = 'NAME_OF_DATAFLOW'
Ensure to use LONG datatype in the query as part of the process to push the data in to your Target table i.e. ZALXEXTRACT
2.Migrate the CLOB item into an Oracle XMLTYPE field
CREATE TABLE "DSD_NA_STG_SVCRM"."XMLTESTS"
( "ID" NUMBER,
"DATA" "XMLTYPE" )
insert into xmltests select 1,xmltype(main) from ZALXEXTRACT
3.Use XPATH tables / queries to extract the attributes that are required.
For ease of use. Copy the contents of the CLOB field in ZALXEXTRACT paste into Notepad, save as HTML and open in internet explorer.
These are some starter examples. Refer to the following if you want try these yourselves.
Example of a query to pull the attributes of all Source Tables in a data flow
SELECT t.id,
rownum,w.DFNAME,w.DFGUID,x.TGUID,x.TYPE,x.TABLENAME,x.DSNAME,x.OWNNAME,y.attr,y.value
FROM xmltests t,
XMLTABLE ('./*'
PASSING t.data
COLUMNS DFNAME VARCHAR2(30) PATH '@name',
DFGUID VARCHAR2(30) PATH '@id'
)W,
XMLTABLE ('//DIDatabaseTableSource'
PASSING t.data
COLUMNS TGUID VARCHAR2(30) PATH '@GUID',
DSNAME varchar2(30) path '@datastoreName',
TYPE varchar2(30) path 'name()',
OWNNAME varchar2(30) path '@ownerName',
TABLENAME varchar2(30) path '@tableName',
XPATH XMLTYPE PATH '//*')X,
XMLTABLE ('//DIDatabaseTableSource/DIAttributes/*'
PASSING x.XPATH
COLUMNS ATTR VARCHAR2(30) PATH '@name',
VALUE VARCHAR2(30) PATH '@value'
)Y
Example of a query to pull all Target Tables in a Dataflow
select * from (SELECT t.id,
rownum,w.DFNAME,w.DFGUID,x.TGUID,x.TYPE,x.TABLENAME,x.DSNAME,x.OWNNAME,x.BULKLOAD,y.ATTR,y.VALUE
FROM xmltests t,
XMLTABLE ('./*'
PASSING t.data
COLUMNS DFNAME VARCHAR2(30) PATH '@name',
DFGUID VARCHAR2(30) PATH '@id'
)W,
XMLTABLE ('//DIDatabaseTableTarget'
PASSING t.data
COLUMNS TGUID VARCHAR2(30) PATH '@GUID',
DSNAME varchar2(30) path '@datastoreName',
TYPE varchar2(30) path 'name()',
OWNNAME varchar2(30) path '@ownerName',
TABLENAME varchar2(30) path '@tableName',
BULKLOAD varchar2(30) path '@bulkLoader',
XPATH XMLTYPE PATH '//*')X,
XMLTABLE ('//DIDatabaseTableTarget/DIAttributes/*'
PASSING x.XPATH
COLUMNS ATTR VARCHAR2(30) PATH '@name',
VALUE VARCHAR2(30) PATH '@value'
)Y
union all
SELECT t.id,
rownum,w.DFNAME,w.DFGUID,x.TGUID,x.TYPE,x.TABLENAME,x.DSNAME,x.OWNNAME,x.BULKLOAD,z.*
FROM xmltests t,
XMLTABLE ('./*'
PASSING t.data
COLUMNS DFNAME VARCHAR2(30) PATH '@name',
DFGUID VARCHAR2(30) PATH '@id'
)W,
XMLTABLE ('//DIDatabaseTableTarget'
PASSING t.data
COLUMNS TGUID VARCHAR2(30) PATH '@GUID',
DSNAME varchar2(30) path '@datastoreName',
TYPE varchar2(30) path 'name()',
OWNNAME varchar2(30) path '@ownerName',
TABLENAME varchar2(30) path '@tableName',
BULKLOAD varchar2(30) path '@bulkLoader',
XPATH XMLTYPE PATH '//*')X,
XMLTABLE ('//DIDatabaseTableTarget//LDRConfigurations/LDRConfiguration/*'
PASSING x.XPATH
COLUMNS ATTR VARCHAR2(30) PATH 'name()',
VALUE VARCHAR2(30) PATH '//text()'
)Z
)order by TGUID,rownum
If the response is good more will follow…
DISCLAIMER
All data and information provided on this site is for informational purposes only. http://allangxmltextharvest.blogspot.co.uk makes no representations as to accuracy, completeness, currentness, suitability, or validity of any information on this site and will not be liable for any errors, omissions, or delays in this information or any losses, injuries, or damages arising from its display or use. All information is provided on an as-is basis.
