Reading XML files - Repetition elements

aluthra48 · Post by **aluthra48** » Mon Jun 15, 2009 4:53 pm

Can anyone explain what the following error means:

Error: No repetition element specified for link datastage

and perhaps provide an example of a repetition element (occurs) and how to interpret it in the xml input stage design?

Thanks

lstsaur · Post by **lstsaur** » Mon Jun 15, 2009 5:46 pm

Go to XMLInput stage's Output-->Columns-->Key, then set "yes" on one of the columns.

aluthra48 · Post by **aluthra48** » Mon Jun 15, 2009 5:52 pm

Correct, but I am trying to understand what column to set the key to yes, and why. Thanks

eostic · Post by **eostic** » Mon Jun 15, 2009 7:44 pm

I try to describe it as "one of the elements at the lowest level node you are interested in retrieving." You can pick any of them.... but it should be a column defined by an element at the lowest level repeating group --- the repeating node for which you want "n" rows to be returned.

The reason you want it at the "lowest level you are interested in" will become more clear once you have a situation where the node may not actually exist (imagine a purchase order with no line items, and the repeating element in the metadata is the line item number). Then the "Repeating Element Required" setting comes into play. If you have it checked, then the repeating element has to be in the current node being retrieved or you won't receive the parent or grandparent nodes. If you have it un-checked, then everything at the level of the "repeating element" will be returned with nulls.

Ernie

aluthra48 · Post by **aluthra48** » Tue Jun 16, 2009 2:38 pm

Thanks Ernie,

I have checked the "Repeating Element required" in the transformation settings of the output tab. What I am trying to figure out is the Yes/No option in the Columns tab for the Key value of the element listed.
Here is an example:
The source is <QVResponse><Response><Error><ErrorCode>value</ErrorCode></Error></Response></QVResponse>
where the Error node is unbounded. Similarly there are other lower level nodes below QVResponse along a different path where one of the nodes is also unbounded.
How do I translate this information to selecting the above Yes/No option? I have set some of the column names to Yes based on what understanding I have, and it now gives me an error "The link contains more than one repetition rule"
Thanks

eostic · Post by **eostic** » Tue Jun 16, 2009 2:47 pm

ah. Next lesson. DataStage is normalizing your xml into rows and columns... for each set of nodes that is "along a different path" (nested deeply is fine), you will be using a separate Link...each with its own set of elements/columns and particular "lowest level node that you are interested in".......

For any given document, as you are moving it (typically) into a rdbms, you may have many many links....depending on how many normalized relationships you are trying to extract.

Ernie

aluthra48 · Post by **aluthra48** » Tue Jun 16, 2009 3:05 pm

I get it. Right now, I have an external source stage -----> xml stage-----> peek stage. This was set up for purposes of experimentation.

What I would need is an external source stage---->copy stage with several output links, each to an xml input stage, and each of these xml input stages having an output link to an ODBC stage. Each of these ODBC stages and the corresponding setting in the output/columns tab of the xml input stage would reference a single normalized relationship that I am trying to extract.

Correct?

Thanks

ameyvaidya · Post by **ameyvaidya** » Wed Jun 17, 2009 4:37 am

If i remember correctly, You could cascade Multiple XML stages also

Something like this:

Code: Select all

<Cust>
        <Name>

        </Name>
        <AddressList>
                <Address>
                        Address1
                </Address>
        </AddressList>
        <PhoneList>
                <Phone>
                        Phone1
                </Phone>
        </PhoneList>
</Cust>

Code: Select all


external source stage -----> xml stage1---(Get Name/AddressChunk) -----> xml stage(Get Address+Prev name)-----> peek stage
                                    |
                          (Get Name/PhoneChunk)
                                    |
                                    -----> xml stage(Get Phone+Prev name)-----> peek stage

Prev name stands for the previously extracted name information in XMl Stage 1

Based on the number of repeating elements you have, you can use the first stage to extract XML chunks for that repeating element with some of the higher level columns and send each chunk/extracted column down a separate link where a downstream XML Input stage processes the specific chunk.

I've done this around a year ago so may not have described this accurately.

eostic · Post by **eostic** » Wed Jun 17, 2009 4:58 am

You have the right idea....but you can put all the output links on a single XMLInput Stage.......so you don't need the copy....

Ernie

aluthra48 · Post by **aluthra48** » Thu Jun 18, 2009 9:26 am

Thank you all, I will try it. I have been busy with some other issues, and will get back to this and try it out as soon as I can.

Thanks once again

aluthra48 · Post by **aluthra48** » Mon Jun 29, 2009 11:19 am

Thank you, I have it working now.