Flatten an XML file

[Anonymous]

Good Afternoon,
I have to parse a number of XML files into an Oracle DB. Although they all have the same structure the Xpath loop may be different for each one as the internal nodes may or may not be iterative.
One solution would be to set up a number of passes on each file. However this would seem to be a costly way to do it.
Is there a way of flattening a XML file from its root?.
Regards.
NobbyHall

[Anonymous]

Hi,
There is a way flattening a XML file from its root.
Please see my screenshots
For more component reference, please review tFileInputXML
Best regards
Sabrina

[Anonymous]

Sabrina,
Many thanks for the information. I'll give it a go.
Regards.
NobbyHall

[Anonymous]

Hi,
If you have any problem in your job, feel free to post you issue.
Best regards
Sabrina

[Anonymous]

Hi Sabrina,
You're example works in that it returns a string with all nodes and values in it.
What I need to do is flatten the data from the input file into a Oracle DB table. However, if I specify any mapping, then I only get a single line of data.
So how would I adjust your example if I have multiple levels where each level can be both iterative and multi level?.
Regards.
Nobby

[Anonymous]

Sabrina,
The following is a cut down version of a file I have to import:
<?xml version="1.0" encoding="UTF-8"?>
<data xmlns:tasks="http://abc.comsot.co.uk/tasks/5.5.1">
<fleet companyCode="ABC" companyName="ABigCompany(ABC)" displayCode="A Big Company (ABC Ltd)">
<model major="1" manufacturer="ABC" minor="1"/>
<model major="1" manufacturer="ABC" minor="2"/>
</fleet>
<taskcard chapter="01" section="01" code="01-01" description="This is a desc">
<procedure active="true" customized="false" reference="040-801" type="WI">
<part active="true" description="Compound1" manufacturer="81" number="D06">
<part active="true" description="Compound2" manufacturer="82" number="D16">
<part active="true" description="Compound3" manufacturer="80" number="D23">
<part active="true" description="Compound4" manufacturer="85" number="D34">
<part active="true" description="Compound5" manufacturer="12" number="D45">
<part active="true" description="Compound6" manufacturer="20" number="D56">
<tool active="true" description="Jack1" manufacturer="11" number="568">
<tool active="true" description="Jack2" manufacturer="22" number="158">
<tool active="true" description="Jack3" manufacturer="33" number="683">
<tool active="true" description="Jack4" manufacturer="44" number="822">
<tool active="true" description="Jack5" manufacturer="66" number="682">
<tool active="true" description="Jack6" manufacturer="77" number="123">

<labor effortHrs="1.0" quantity="1" skillDesc="Inspector"/>
<labor effortHrs="5.0" quantity="1" skillDesc="Inspector"/>
<zone code="123417" description="Eng1 - Primary Exhaust Plug"/>
<zone code="123427" description="Eng2 - Primary Exhaust Plug"/>
<condition code="AP" desc="Ele Power - On" setting="ON"/>
<condition code="DP" desc="Ele Power - Off" setting="OFF"/>
<certification code="ddd" description="Required Certification" quantity="1"/>
<certification code="www" description="Required Certification" quantity="1"/>
<certification code="eee" description="Required Certification" quantity="1"/>
<customFields>
<typeBoolean name="Item1" value="true"/>
<typeBoolean name="Item2" value="true"/>

<typeString name="Item3" value="true"/>
<typeString name="Item4" value="false"/>
<typeInt name="Item5" value="1"/>
<typeInt name="Item6" value="0"/>
</customFields>

<relatedDocument title="Additional doc 1" url="http://www.google.com"/>
<relatedDocument title="Additional doc 2" url="http://www.google.com"/>
<relatedDocument title="Appendix a - Bunk layout" url="04.pdf"/>
<relatedDocument title="Appendix b - Door layout" url="05.pdf"/>
<requirementRef code="110-01" id="93ABA"/>
<requirementRef code="110-02" id="C93AB"/>
<requirementRef code="110-03" id="EC93A"/>
</procedure>
<procedure active="true" customized="false" reference="24-00" type="SU">
<part active="true" description="Bill" manufacturer="81" number="D1">
<tool active="true" description="Jack" manufacturer="82" number="C1">
<labor effortHrs="1.0" quantity="1" skillDesc="Inspector"/>
<zone code="5417" description="Flap 1"/>
<condition code="EP" desc="Wheel Unit" setting="ON"/>
<certification code="NICS" description="Certification" quantity="1"/>
<customFields>
<typeBoolean name="RIem" value="true"/>
<typeString name="EIem" value="PUA"/>
<typeInt name="WIem" value="4"/>
</customFields>
<relatedDocument title="Additional doc 3" url="http://www.google.com"/>
<relatedDocument title="Appendix a - Floor layout" url="049.pdf"/>
<requirementRef code="78" id="DEE040CF"/>
</procedure>
</taskcard>
</data>
Regards
Nobby

[Anonymous]

Sabrina,
Having further tested based on various posts I've found I am unable to find a solution with this approach as I am unable to set one node as the definitive xpath. In other words I would need to parse from the root.
The other alternative I believe I would have is to perform multiple parses on each XML file or construct multiple schemas going off to separate Oracle functions. Not only is this costly in terms of CPU and time it also forces me to use multiple tables as opposed to the one I need to use.
Regards.
Nobby

[janhess]

You may have to use the tXSLT component.

[Anonymous]

Janhess,
I tried using the tXSLT component. I'm not sure if I misunderstood it's use but I found two problems. Firstly that the output was directed into another file and secondly that I am unable to get the xsl template to compile against the XML files. They are 3rd party and the tags are all "AAA:BBB" format.
When I changed these to a more standard "abc" form both the xsl and xml compiled ok but no results appeared although blank rows were created for them. This is probably something wrong with my xsl.
I don't have time to improve my xsl/xml knowledge, I can't introduce another file layer into my project and using the several parses approach ends up with TOS throwing errors all over the place.
Think I'm going to give up and just do this in Java.
But thanks to all for your help.
Regards.
Rick.