Creation XML file with Header ,Detail , Trailer

[BIuser]

Hi All,

We were tasked to create an XML with Header, Detail, Trailer

Our source is text files and we have 3 input files ( Header.txt, Detail.txt, Trailer.txt) , we need to create an single XML file with all the three mentioned

All the three files are having different metadata

We need to create an single XML using these 3 input files

What is the best way to achieve this
( We tried looking at Best Practices document and we couldn't match this requirement )

Please advise

[eostic]

It's in the document. Look at the xmlOutput Section where it talks about writing out multiple independent paths.... Then decide what your document is going to look like....do you already have a sample?

Create a common key for the rows among the three sources...either manufacture one, or maybe there is one...that would be in your header.....learn first how to create each individual repeating unit into an xml document with the header values.....

Then put it all together, as illustrated in that document. Go thru the document in detail...it shows how to create each "section" and put it into a table for later lookup.

Or...get an xsd that already has these put together, and if you are in 8.5 or greater, use the new xml Stage.

Bottom line --- you have a lot of practice and learning to do before you get there. Creating a multi-path xml hierarchical document is a fairly complex task, with any ETL tool or technology that is mostly "relational" (rows and columns) in its run time architecture. ...but once you get the hang of it, the process is staightforward.

Ernie

[BIuser]

Hi Ernie,

Thanks for your reply

Apologies , my premium membership is expired and i couldn't see your post completely .

Let me tell you what i have done ,

I have created a Dummy column in all the three files ( Header, Detail, Trailer ) and did a left outer join using Details as my left link .

I am able to create the below target XML ,

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<OI>
  <Hdr>
    <HL91_BH_INT_HDR_TAG id="<HDR>"/>
    <HL91_BH_DOC_YYYYMMDD id="20121108"/>
    <HL91_BH_INT_TIME id="233438"/>
    <HL91_BH_INT_HDR_HASH id="#"/>
  </Hdr>
  <DTL>
    <HL91_DT_TAG id="<DRC>"/>
    <HL91_DT_STANCOY id="SNIC"/>
    <HL91_DT_COSTCEN id="320000"/>
    <HL91_DT_ACCNO_X id="955701"/>
    <HL91_DT_AMOUNT id="1067139150"/>
    <HL91_DT_SIGN id="-"/>
  </DTL>
  <DTL>
    <HL91_DT_TAG id="<DRC>"/>
    <HL91_DT_STANCOY id="SNIC"/>
    <HL91_DT_COSTCEN id="860000"/>
    <HL91_DT_ACCNO_X id="955701"/>
    <HL91_DT_AMOUNT id="0"/>
    <HL91_DT_SIGN id="+"/>
  </DTL>
<TRL>
    <HL91_TR_TAG id="<TRL>"/>
    <HL91_TR_HASH id="#"/>
    <HL91_TR_REC_CNT_TAG id="<TRC>"/>
    <HL91_TR_REC_CNT_VAL id="945"/>
    <HL91_TR_REC_CNT_HASH id="#"/>
    <HL91_TR_DR_BAL_TAG id="<CR1>"/>
    <HL91_TR_DR_BAL_VAL id="6411405230080"/>
    <HL91_TR_DR_BAL_HASH id="#"/>
    <HL91_TR_CR_BAL_TAG id="<CR1>"/>
    <HL91_TR_CR_BAL_VAL id="908899581952"/>
    <HL91_TR_CR_BAL_HASH id="#"/>
  </TRL>
</OI>

Please confirm if this approach is right ?


Do i need to make any change to the above XML


also as mentioned i couldn't see your post completely , can you please mention at which place i can find this sort of requiremnt in Best pratcies doc

[chulett]

Please confirm if this approach is right ?

Is the output what you need it to be? That would go a long way towards saying your approach was fine.

[BIuser]

Hi Chullet,

We don't have any Pre defined output

We were asked to come up with a optimum design to create an XML ( from text files ) with Header, detail, Trailer in it

[eostic]

hmm...

If your header and trailer information has no repeating rows, then what you've done looks great, and in fact, you don't have to do all the lookup stuff as described in the document.

Ernie

[BIuser]

Hi Ernie,

Thanks for this

you don't have to do all the lookup stuff

If there is any other easy way, can you please let me know

Regards
Ram

[eostic]

based on what you pasted above, I'd say that you are done. Multiple rows for detail and one for header and trailer....?

[BIuser]

Hi Ernie,

I am not getting single row for Header and Trailor

If I just load the Header fields to a text file, I am same header reords that is equal to the count of detail records ( I think this happening because I took detail file as main while doing left outer join )

Is there any way ( at XML / XSD level ) to make the Header record to occur only once ?

[ray.wurlod]

How about using a Funnel stage running sequentially to assemble Header, Details, Trailer?

[BIuser]

Hi Ray ,

All the three files ( Header,Detail,Trailer ) has different meta data

Will it be possible to use funnel in this case

[eostic]

At the moment I am "assuming" that you have the following....rows that contain some header info, some trailer info, and lots of detail. If you had ten rows after your joins and data collection, the header info is identical for all ten rows, and the trailer information is also identical on all ten rows...

...and then unique data for each of the ten detail rows. Is that what you have?

If so (which is what your cut/pasted example looks like), then you should be able to get what you are looking for by defining the detailed metadata as its own section (under OI) and marking it as a key. The other two sections (also under OI) should work ok because they only occur once for all of your ten rows.

The description property on the output link would look something like this:

/OI/HDR/<a header element name>/text()
...
/OI/Detail/<a detail element name>/text() --- mark this one as "key"!
...
/OI/Trailer/<a detail element name>/text()


If the header information or trailer information also changes values for any of the 10 rows that you have, then it is a different solution as you would have multiple repeating groups.

Ernie

[BIuser]

Hi Ernie,

I am not able to view your post completely ( I am waiting for my premium membership to be renewed)

Is it possible for you to allow me to read your full post