DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Extract Data Into Multiple Tables From One XML File

wowlMar 13 2019 — edited Apr 21 2019

Hi,

I'm sure this has been asked before and apologise if it has but i couldnt find anything.

I have an xml file that contains data for multiple tables and I need to extract it. I've loaded the table into a clob and plan to use code below but i'm having trouble with the structure of the XML

I've put the xml below so if anyone could tell how how to extract the data i would appreaciate it.

declare

v_clob clob;

v_xml XMLType;

BEGIN

select clob_col into v_clob from temp_tab;

v_xml := XMLTYPE.createXML(v_clob);

FOR r IN

(SELECT ExtractValue(Value(p),'/Table/Record/Field()') AS Product_Group

FROM TABLE(XMLSequence(Extract(v_xml,'/XmlInterfaceRequest/AddPolicyTableData/PolicyTableData/Table'))) p

)

LOOP

insert into temp_tab2 (col_a) values (r.Product_Group);

END LOOP;

commit;

end;

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<Name>Business_Domestic_P_Enforcement_V1.0</Name>

<Table>

<Field MatchingOperationAlgorithm="Equivalence" MatchingDelimiterValue="" MatchingDelimiterKey="false" Type="csv" Key="true" Field="Product_Group">User.Custom14</Field>

</Header>

<Field Field="Quota_P_Rules">DataAccessGroup</Field>

<Field Field="Out_of_quota_P_Rules">MBBRunOutOfDataGroup</Field>

</Record>

<Field Field="Quota_P_Rules">DataAccessGroup</Field>

<Field Field="Out_of_quota_P_Rules">RunOutOfDataGroup</Field>

</Record>

<Field Field="Quota_P_Rules">DataAccessGroup</Field>

<Field Field="Out_of_quota_P_Rules">DataAccessGroup</Field>

</Record>

<Field Field="Quota_P_Rules">DataAccessGroup</Field>

<Field Field="Out_of_quota_P_Rules">ZeroBandWidth</Field>

</Record>

</Table>

</PolicyTableData>

<Name>Business.Monitored_Product_Groups</Name>

<Table>

<Field MatchingOperationAlgorithm="Equivalence" MatchingDelimiterValue="," MatchingDelimiterKey="true" Type="csv" Key="true" Field="Domestic_Groups">User.Custom14</Field>

</Header>

</Record>

</Record>

</Table>

</PolicyTableData>

<Name>Business.X_S_V1</Name>

<Table>

<Field MatchingOperationAlgorithm="Wildcard" MatchingDelimiterValue="," MatchingDelimiterKey="true" Type="csv" Key="true" Field="X_Quota">User.Custom7</Field>

</Header>

<Field Field="S_BASE_80">Notes</Field>

<Field Field="S_BASE_100">Notes</Field>

</Record>

<Field Field="S_BASE_80">Notes</Field>

<Field Field="S_BASE_100">Notes</Field>

</Record>

</Table>

</PolicyTableData>

</AddPolicyTableData>

</XmlInterfaceRequest>

This post has been answered by cormaco on Mar 21 2019

Jump to Answer

SriniVEERAVALLI

Sandra,

I dont see that table in that mapping. For more info look at Lineage doc, link you may find in http://www.cool-bi.com/Ref/Ref.php

SELECT DISTINCT $$Hint1
DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,
DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,
AELINE.ACCOUNTING_CLASS_CODE,
GLIMPREF.JE_HEADER_ID JE_HEADER_ID,
GLIMPREF.JE_LINE_NUM JE_LINE_NUM,
AELINE.AE_HEADER_ID AE_HEADER_ID,
AELINE.AE_LINE_NUM AE_LINE_NUM,
T.LEDGER_ID LEDGER_ID,
T.LEDGER_CATEGORY_CODE LEDGER_TYPE,
JBATCH.NAME BATCH_NAME,
JHEADER.NAME HEADER_NAME,
PER.END_DATE,
AELINE.CODE_COMBINATION_ID
FROM XLA_DISTRIBUTION_LINKS DLINK
, GL_IMPORT_REFERENCES GLIMPREF
, XLA_AE_LINES AELINE
, GL_JE_HEADERS JHEADER
, GL_JE_BATCHES JBATCH
, GL_LEDGERS T
, GL_PERIODS PER
WHERE DLINK.SOURCE_DISTRIBUTION_TYPE IN
( 'AR_DISTRIBUTIONS_ALL'
, 'RA_CUST_TRX_LINE_GL_DIST_ALL')
AND DLINK.APPLICATION_ID = 222
AND AELINE.APPLICATION_ID = 222
AND AELINE.GL_SL_LINK_TABLE = GLIMPREF.GL_SL_LINK_TABLE
AND AELINE.GL_SL_LINK_ID = GLIMPREF.GL_SL_LINK_ID
AND AELINE.AE_HEADER_ID = DLINK.AE_HEADER_ID
AND AELINE.AE_LINE_NUM = DLINK.AE_LINE_NUM
AND GLIMPREF.JE_HEADER_ID = JHEADER.JE_HEADER_ID
AND JHEADER.JE_BATCH_ID = JBATCH.JE_BATCH_ID
AND JHEADER.LEDGER_ID = T.LEDGER_ID
AND JHEADER.STATUS = 'P'
AND T.PERIOD_SET_NAME = PER.PERIOD_SET_NAME
AND JHEADER.PERIOD_NAME = PER.PERIOD_NAME
AND JHEADER.CREATION_DATE >=
TO_DATE('$$INITIAL_EXTRACT_DATE'
, 'MM/DD/YYYY HH24:MI:SS' )
AND DECODE($$FILTER_BY_LEDGER_ID, 'Y', T.LEDGER_ID, 1) IN ($$LEDGER_ID_LIST)
AND DECODE($$FILTER_BY_LEDGER_TYPE, 'Y', T.LEDGER_CATEGORY_CODE, 'NONE') IN ($$LEDGER_TYPE_LIST)

Edited by: Srini VEERAVALLI on Jun 4, 2013 7:53 AM

1 person found this helpful

SandraAgustina14

Thanks Srini for your reply.
You don't see the table in that mapping, hm...
Ok, I will refer to the link you gave me, If I compare the query you gave and query from the workflow task, the last 2 field in my workflow task doesn't exists in your query and those are : AEHEADER.EVENT_TYPE_CODE,
NVL(XLA_EVENTS.UPG_BATCH_ID,0) UPG_BATCH_ID

Thanks again Srini for your help.

Regards,

Sandra

SriniVEERAVALLI

Given query is from Mapping.

If helps mark :)

1 person found this helpful

SandraAgustina14

Dear Srini,

I open the link and download Excel Oracle Business Intelligence Applications ETL Data Lineage Guide Release 7.9.6.4, and then open the sheet SQL Overrides.
For SQL_Override_ID : SDE_ORAR1211_Adaptor.SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full, the SQL Override is like this :

SELECT DISTINCT $$Hint1 DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID, DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE, AELINE.ACCOUNTING_CLASS_CODE, GLIMPREF.JE_HEADER_ID JE_HEADER_ID, GLIMPREF.JE_LINE_NUM JE_LINE_NUM, AELINE.AE_HEADER_ID AE_HEADER_ID, AELINE.AE_LINE_NUM AE_LINE_NUM, T.LEDGER_ID LEDGER_ID, T.LEDGER_CATEGORY_CODE LEDGER_TYPE, JBATCH.NAME BATCH_NAME, JHEADER.NAME HEADER_NAME, PER.END_DATE, AELINE.CODE_COMBINATION_ID,

AEHEADER.EVENT_TYPE_CODE, NVL(XLA_EVENTS.UPG_BATCH_ID,0) UPG_BATCH_ID

FROM XLA_DISTRIBUTION_LINKS DLINK , GL_IMPORT_REFERENCES GLIMPREF , XLA_AE_LINES AELINE , GL_JE_HEADERS JHEADER , GL_JE_BATCHES JBATCH , GL_LEDGERS T , GL_PERIODS PER
WHERE DLINK.SOURCE_DISTRIBUTION_TYPE IN ( 'AR_DISTRIBUTIONS_ALL' , 'RA_CUST_TRX_LINE_GL_DIST_ALL') AND DLINK.APPLICATION_ID = 222 AND AELINE.APPLICATION_ID = 222 AND AELINE.GL_SL_LINK_TABLE = GLIMPREF.GL_SL_LINK_TABLE AND AELINE.GL_SL_LINK_ID = GLIMPREF.GL_SL_LINK_ID AND AELINE.AE_HEADER_ID = DLINK.AE_HEADER_ID AND AELINE.AE_LINE_NUM = DLINK.AE_LINE_NUM AND GLIMPREF.JE_HEADER_ID = JHEADER.JE_HEADER_ID AND JHEADER.JE_BATCH_ID = JBATCH.JE_BATCH_ID AND JHEADER.LEDGER_ID = T.LEDGER_ID AND JHEADER.STATUS = 'P' AND T.PERIOD_SET_NAME = PER.PERIOD_SET_NAME AND JHEADER.PERIOD_NAME = PER.PERIOD_NAME AND JHEADER.CREATION_DATE >= TO_DATE('$$INITIAL_EXTRACT_DATE' , 'MM/DD/YYYY HH24:MI:SS' ) AND DECODE($$FILTER_BY_LEDGER_ID, 'Y', T.LEDGER_ID, 1) IN ($$LEDGER_ID_LIST) AND DECODE($$FILTER_BY_LEDGER_TYPE, 'Y', T.LEDGER_CATEGORY_CODE, 'NONE') IN ($$LEDGER_TYPE_LIST)

You can see the last two fields in select clause, that's gonna be my problem. So what I am trying to do is remove these 2 fields.
But it didn't solve, but return another error : SQL Error [ORA-01007: variable not in select list]
So I am guess I just have to stay with the SQL from SQ Mapping, make sure it same with Source Qualifier in the mapping.
But If I did that, this Workflow Full Version will be the same just like the original. Actually I don't understand the reason for this Full Version. Can you give me some highlight?
Thanks

Regards,

Sandra

SriniVEERAVALLI

Can you double check the workflow name and folder, I didnt get the given code mapping/workflow from both folder SDE_ORAR1211_Adaptor and SDE_ORAR1213_Adaptor

SDE_ORAR1211_Adaptor.SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full

can you send me the session log to my email?

SandraAgustina14

Dear Srini,

I have changed the query in workflow task, and now it runs successfull, but I don't have any idea if the query is just like it supposed to be :D
I send you an email, please check. Thank you.

Regards,

Sandra

Abaid ur Rehman

Me also facing same problem, can you give me also query as well let me know how to fix this query.

Thanks,

Abaid

Mohamed Sabry 18

dear Sandra
could you please elaborate the solution to this problem?

Mohamed Sabry 18

Dear SriniVEERAVALLI

could you please elaborate the solution to this issue?
i'm having the exact same problem

Best regards,

SriniVEERAVALLI

Open new post with error details

Mohamed Sabry 18

i did ,here is the link : https://forums.oracle.com/thread/2584315
thank you very much

1 - 11

Added on Mar 13 2019

#pl-sql-xml-programming, #xml

3 comments

2,843 views

DevOps, CI/CD and Automation

Extract Data Into Multiple Tables From One XML File

Comments

Post Details