IICS | Hierarchy Builder Transformation | Create XML file from Oracle

[@ETLSTATION]

Hi Friends , in this video , we will learn how to create XML file using Hierarchy Builder Transformation from Oracle . below is DDL for oracle source and sample data : CREATE TABLE BOOKS ( "ID" VARCHAR2(20 BYTE) NOT NULL ENABLE, "AUTHOR" VARCHAR2(100 BYTE), "TITLE" VARCHAR2(100 BYTE), "GENRE" VARCHAR2(100 BYTE), "PRICE" NUMBER(16,2), "PUBLISH_DAT" DATE, "DESCRIPTION" VARCHAR2(200 BYTE), CONSTRAINT "BOOKS_PK" PRIMARY KEY ("ID") ) data : Insert into BOOKS (ID,AUTHOR,TITLE,GENRE,PRICE,PUBLISH_DAT,DESCRIPTION) values ('bk102','Ralls, Kim','Midnight Rain','Fantasy',5.95,to_date('12-DEC-00','DD-MON-RR'),'A former architect battles corporate zombies,an evil sorceress, and her own childhood to become queen of the world.'); Insert into SRC.BOOKS (ID,AUTHOR,TITLE,GENRE,PRICE,PUBLISH_DAT,DESCRIPTION) values ('bk101','Gambardella, Matt','XML Developer''s Guide','Computer',44.95,to_date('01-OCT-00','DD-MON-RR'),'An in-depth look at creating applications with XML. '); Insert into SRC.BOOKS (ID,AUTHOR,TITLE,GENRE,PRICE,PUBLISH_DAT,DESCRIPTION) values ('bk103','Corets, Eva','Maeve Ascendant','Fantasy',5.95,to_date('17-NOV-00','DD-MON-RR'),'After the collapse of a nanotechnology society in England, the young survivors lay the foundation for a new society.'); the sample XML is present in mircorsoft site : learn.microsoft.com/en-us/pre...) you can watch my videos for hierarchy parser or XML to snowflake on how to load xml to relation tables

[@vinothrajr1221]

Could you please explain, what is secure agent and what is the purpose of using secure agent?

[@ETLSTATION]

its your runtime environment, since iics is completely managed by informatica , you dont have access to metadata information so secure agent is like communication between your machine and informatica or any other cloud or relational dbs other than your machine , secure agent takes care of it communication

[@vinothrajr1221]

Quick response thank you.

[@StevenEngel-b8j]

Thank you for actually explaining the PK/FK solution as to why it was needed. Struggled for a few days before finding this. Cheers!

[@ETLSTATION]

you are welcome

[@hritikpandey8884]

I'm able to generate the xml file from the data coming from the database but just want to know how we can split it into multiple XML files dynamically on the basis of record count or size...

[@ETLSTATION]

i have not tried that , but you can give a try based by using transaction control transformation , it may work or try after generating xml , split using shell script

[@hritikpandey8884]

@@ETLSTATION please create one video on how we can generate xml file of size more than 100 MB using hierarchy builder on IICS....

[@peterpesch]

Thanks for the clear explanation for how to fill an attribute for an element which contains other elements (and an attribute). But how do you fill an element which has an attribute plus text? - Mapping the attribute value to the attribute is no problem. - But when I try to map the text value to the element, it keeps saying that I can only map it to a simple type ...

[@ETLSTATION]

please give sample example of what you are trying

[@peterpesch]

@@ETLSTATION As far as I know, I cannot post picture here, so I'll try to describe it. I'm trying to fill a TEAPS xml with invoice data. For several AMOUNT values in the target data structure I'll have to fill in the amount plus 2 of the attributes. I'm using a Hierarchy Builder, based on a Hierarchical Structure based on the XSD of the TEAPS xml structure. However, in the fieldmapping sheet of the Hierarchy Builder, I can only fill in the 2 attributes, but I cannot fill in the Amount value. (Currently I can only fill in the 3 fields (Amount plus the 2 attributes) if I tweak the XSD by changing the definition of the Amount common type - fortunately, that tweak will correct the situation for all AMOUNT structures throughout the TEAP xml data structure - but of course, that is not the correct way to do it, as this would mean that for every future version of the TEAPS xml structure we would have to apply that same tweak to the new xsd ...)

[@hritikpandey8884]

Thank you...I was facing same issue the content in xml file was not in proper sequence... followed your steps and issue is resolved Please suggest me how to generate dynamic xml files using IICS...

[@ETLSTATION]

replied in other comment on same issue

[@GoodMindGoodFind]

awesome video 👏🏻👏🏻👏🏻

[@ETLSTATION]

thank you . keep watching

[@nitinraj1584]

Can we generate complex Xml ( heirarchy inside heirarchy ) files using iics?

[@yuvarajraghunapu7339]

Bro. Can we create individual files each book?

[@venkateshmangamolla]

Hi bro please make one video on most asked scenario based interview questions... thanks

[@ETLSTATION]

i already have interview series , 5 videos , did you already saw it ? all are scenarios based only on