XML Read with Different layout

dsdoubt · Post by **dsdoubt** » Wed Apr 16, 2008 3:53 pm

I have the XML file with Different layout. It can be identified by the first field. Sample given as follows.

<HEAD>
<TRANS cd="2520" no="235" sls_amt="8657.75" trans_cnt="1224"/>
<TRANS cd="2521" no="235" dt_of_tx="20080213" sls_typ="MCP"/>
</HEAD>

The metedata importer recognize only the first type of record and not the later one. Like dt_of_tx and sls_typ is not getting detected while importing.

May I know, what is the simple way to handle this kind of scenario.
I know that, preprocessing the file would be one option. May I know the other options?

lstsaur · Post by **lstsaur** » Wed Apr 16, 2008 4:42 pm

So, you want your xml output looks like the following?
<HEAD>
<TRANS cd="2520" no="235" sls_amt="8657.75" trans_cnt="1224"/>
</HEAD>
<HEAD>
<TRANS cd="2521" no="235" dt_of_tx="20080213" sls_typ="MCP"/>
</HEAD>
If that's the case, then just use custom stylesheet.

dsdoubt · Post by **dsdoubt** » Wed Apr 16, 2008 4:54 pm

Hi,
Do you mean to say, if I change the XML as given, I can import different layout at same time?
May I know what is custom Stylesheet?

dsdoubt · Post by **dsdoubt** » Wed Apr 16, 2008 5:07 pm

Even if I try to edit the XML file to the below mode

Code: Select all

<HEAD>
<TRANS cd="2520" no="235" sls_amt="8657.75" trans_cnt="1224"/>
</HEAD>
<HEAD>
<TRANS cd="2521" no="235" dt_of_tx="20080213" sls_typ="MCP"/>
</HEAD>

I am not able to import the two fields dt_of_tx and sls_typ.

lstsaur · Post by **lstsaur** » Wed Apr 16, 2008 5:45 pm

No, I meant that's what you want your xml output looks look?
If that's the case, then using the custom stylesheet (XSLT), you can manipulate those two diffirent layouts based on the value in the "cd".

chulett · Post by **chulett** » Wed Apr 16, 2008 5:51 pm

I get the impression they are trying to read it and failing. Can you do it in two jobs / passes - one for each TRANS types?

eostic · Post by **eostic** » Thu Apr 17, 2008 11:58 am

Is this a metadata problem, or a run-time problem? Just enter those two attributes yourself, manually, or get an xsd instead of an instance document to import the meta data....or just edit the document and copy those two attribtutes into the first Trans element. Because this is not an xsd, the importer does the best it can, reading thru the first instance of the element. Whatever attributes or sub-elements are there, will show up for import. Any others can be entered by hand, using the same pattern. Of course, the real "xsd" will have them all defined...these aren't different records, per se...they are simply additional and optional attributes that are allowed for inclusion in the Trans element.

Ernie

kumar_s · Post by **kumar_s** » Fri Apr 18, 2008 12:25 pm

As our XML specialist Ernie suggested, you can include those two field dt_of_tx and sls_typ in the same TRANS. So that when you read the file, you ll get zeros or empty space for those fields, where data is not available. Later you can consolidate those with RemoveDuplicate or Aggregator stage based on the Key.

dsdoubt · Post by **dsdoubt** » Thu May 15, 2008 1:29 pm

Thanks for all your input.
I am able to read the data using the mentioned method. ie., creating the metadata manually with all the columns that are necessary. And reading the XML file. But now I have one more query.

Code: Select all

<HEAD>
<TRANS cd="2520" no="235" sls_amt="8657.75" trans_cnt="1224" sls_typ="MCP"/>
</HEAD>
<HEAD>
<TRANS cd="2520" no="235" dt_of_tx="20080213" />
</HEAD>

From the above data, the output should be,

CD. No, Sls_Amt, Trns_cnt, Sls_Typ,Dt_Of_Tx

ie., the First two fields CD and NO are the keys. so that rest of all the fields can be added as extra fields.
But how can I mark two fields as key?
If i mark so, Iam getting the following warning.

Code: Select all

Error: The link contains more than one repetition rule

eostic · Post by **eostic** » Thu May 15, 2008 4:13 pm

What I hope you are getting, is two rows from this XML snippet. Each should have the same value for cd and no, and then have four additional columns....which in this example, will be mutually exclusive on the two rows, with the non existent columns carrying blanks. Pass it thru the aggregator or probably Remdup.

They "key" is not a key.. it's merely an indicator for which element is your lowest level "repeating row"..... You can only have one, and in this case it may even be necessary for you to also carry a dummy "TRANS" column and make it the key, because everything else is an attribute.

Ernie