XML Job Problem

[ajith]

We are facing an issue while transforming data from XML file to data base with two columns repetition.



Source file has data



<Privilege>

<User id= 10 />

<permission>

<Action="View" />

<Action="Modify" />

</permission>

<Area list>

<Area ="Area1" />

<Area ="Area2" />

<Area ="Area3" />

</Area>

</Privilege>



The desired output in table is



Userid Permission Area code

10 View Area1

10 View Area2

10 View Area3

10 Modify Area1

10 Modify Area2

10 Modify Area3



But we are getting out put of



Userid Permission Area code

10 View Area1

10 View Area2

10 View Area3



We are able to set only one repetition column



Please help us to get the desired output

[ajith]

Somebody please help me out here .....

[chulett]

Ok... but first try to help yourself. Get your hands on an .xsd of the target xml file. Import that using the Manager: Import / Table Definitions / XML Table Definitions. This will generate the XPath expressions you'll need to create your output file. Use them in your job. Let us know what happens or if you've already done this.

Also, since you can only mark one repetition element, make sure it's the 'lowest' one... i.e. the 'Modify' element I would think, not the 'View' element, if that's what you've tried so far.

When you post back, the more details the better.

[ajith]

Thx churlett, for your prompt reply.

But , here I have a problem . I have to populate them to an oracle table, and the table should contain all the values the xml file contains. So the problem is---

in the shema for this xml

<Privilege>

<User id= 10 />

<permission>

<Action="View" />

<Action="Modify" />

</permission>

<Area list>

<Area ="Area1" />

<Area ="Area2" />

<Area ="Area3" />

</Area>

</Privilege>

they have not specified how many areas will be coming, meaning, they kept it as unbounded , so is the the action , it has multiple values coming but not uite sure how many, so the the job has to be designed such that whatever comes should be processed without dataloss, and I need to capture em to one table.

I dont have any idea to try and make the target xml, if you could help me with this , i would go on and do as per your suggestion and pull the data to the oracle table from that xml.

See the problem is both ACTION and AREA repeats and will hold multiple values so I cant keep one of them as repetition element. Hope this makes it more clear.


Currently I am trying out with an xml input file +transformer+oracle stage
If you can suggest any otherways in which I can make a try, that would be great.

Thanks

Ajith

[chulett]

Well... this is where you move beyond my meager ability to help. I'm not following your input xml or exactly what you'll need to do to get that flattened out and into Oracle.

Speaking of which, do you have a target Oracle table defined? Might help to post that DDL so people know what you are shooting for.

Sounds like you may need to dip into the advanced end of the pool. Have you read the XMLPACK_20_Designer.pdf? There is a chapter on the XML Input stage and an advanced appendix (B, I believe) that walks you through what you need to do if you end up having to leverage a custom stylesheet to accomplish your goal.

Hopefully someone else can jump in here and lend a hand...