DSXchange

Hi All,

I am trying to read the XML file using Datastage PX. Design goes like this :

External source ->XML input-> Sequential file.

While trying to read the file, I noticed that some of data elements (tags) are repeating in the parent tag. We should have a seperate row for each repeating data elements in parent tag. But the result is coming in one row only like :

Let us assume this value tag is repeating 4 times in parent tag..its coming like this :
<Value>U</Value><Value>D</Value><Value>U</Value><Value>D</Value>


But we should have a seperate row for each repeating value of value tag. I already defined the repetition element to one of the primary key tag in XML input stage.

Can anyone help me to read this type of XML file?

Thanks!

Which 'primary key tag'? Try moving it lower, like whatever 'D' is.

Exactly. The end of your xpath (look in the Description property for the column you want to have the "value" of <Value>. It should have /text(). Sounds like you have only .../.../<parent of Value>/ or similar.

Ernie

Hi,

Thanks for your reply...
Here primary key tag i mean the main tag that is repeating in the XML file.
Here is the example ;

we have XML file with various customer data set info. Customer id is the primary key in XML file which is repeating for each customer data set.
This value tag is the child tag in that customer tag.
for eg;
<AcisXML>
<Customers>
<Customer Customer_ID="356843">
<Data>
<Entry EffDt="2007-01-01" TermDt="9999-12-31" TID="136030">
<CES_Customer_Number>0710984</CES_Customer_Number>
><Value>U</Value>
<Value>A</Value>
<Value>C</Value>
</ACIS>

Now the question is How can we generate the 3 rows for same customet id with 3 diffrent values in the value tag?
thanks!

chulett wrote: Which 'primary key tag'? Try moving it lower, like whatever 'D' is. ...

You need to mark the 'lowest' element as the repetition key - try your 'Value' tag.

As you noted, there are many paths in this XML structure, and many relationships. Think of this structure as a whole relational database, not as a single xml document. There are many, many tables, one for each of the repeating meaningful things that a customer may have.

You have to model your DataStage job as you would model this so that you could design a set of tables in DB2, Oracle, MySQL, etc.....

You will have as many Table Definitions, and as many links, and as many repetition elements, as you have repeating nodes that you are intereseted in. Each of those links will have a particular element that will be the repeating element, identifying the "deepest" part of that path that you are interested in.

You have to study and know the XML source, and spend lots of time in the XML Meta Data Importer to generate the entire selection of tables that you find interesting. Do NOT use "autocheck" until you know what it is doing and might consider it for a particular node only. Probably better that you just ignore that option and carefuly pick and choose the elements and attributes you want.

Ernie