Duplicate segment loading & creating relationship (XML)

[Kumar1234]

Code: Select all

<XYZDetails> ...root
	<ABC_Segment>  ....parent segment
	     	<AAA>11111</AAA>
		<BBB>222</BBB>
		<CCC>3333</CCC>
	     <GPP_Segment> ....child segment
		<DDD>123<DDD>
		<EEE>1122<EEE>
	     </GPP_Segment>
	</ABC_Segment>
	<ABC_Segment>
	     	<AAA>11111</AAA>
		<BBB>222</BBB>
		<CCC>3333</CCC>
	     <GPP_Segment>
		<DDD>777<DDD>
		<EEE>0<EEE>
	     </GPP_Segment>
	</ABC_Segment>
	<ABC_Segment>
		<AAA>9999</AAA>
		<BBB>0</BBB>
		<CCC>PPPP</CCC>
	</ABC_Segment>
<XYZDetails>

I have to load the above XML into a table. ABC_Segment is a repetition segment. I need to create a key based on AAA,BBB,CCC attribute values. I am concatenating values of all 3 attributes and making as key (eg: 11111|222|3333) and I am able to create keys 1, 2, 3 respectively.
You can see first 2 segment (ABC_Segment) repetitions are duplicate and I have to load the duplicates as as with keys.
So I loaded as:

Code: Select all

Lkp_Key			     Key_Value
11111|222|3333		  1
11111|222|3333		  2
9999|0|PPPP		     3

Problem is, now I have to lookup on this to get the key_value for the child segment (GPP_Segment).
So can anyone help me to how can I create a unique lookup key so as that I can tag the correct child to its respective parent.

I tried with concatenating a @INROWNUM at the end of key but I dont know how the child segment will be able to identify its correct parent.

Hope I am able to explain my question.

Ps : I am doing this on server job.

[ray.wurlod]

What have you tried? That is, how have you programmed the properties of the XML stage, including xpaths?

[Kumar1234]

I tried to use position() instead of text() on xpath and it did provide sequence number for position of ABC_Segment. But this is not working incase of lookup parent (ABC_Segment) for child GPP_Segment because position() can be used in the segment where the repetition element exist.

can we program the description area ? that is Xpath area ?
What kind of program can we do ? and where ?

Pleaes help.

[eostic]

...since the ABC Segment doesn't have any identifying characteristics (a typical xml design where uniqueness is important might have a sub-node that is a sort of header, directly underneath ABC, or else an attribute, like abc_ID="01" directly inside the ABC_Segment element), one suggestion is to parse this xml in chunks.....

...first, pull the ABC Segment with a single large varchar column, whose xpath (description property) just ends with /ABCSegment/ , making it the repeating element (you'll get one row for every ABC Segment) ...then create your unique counters....and then pass it into another xmlInput Stage.....in that one, pull your columns defined as elements directly under ABC, with the repeating element assigned to a column in the <GPP Segment>

Ernie