XML Input stage

[Harini]

Hello All,

I facing problem in laoding data into multiple tables. I have a single xml file and it looks like,

<Item>
<Item Number>001</Item Number>
<Item Name>Rod</Item Name>
<Item Code> P001</Item Code>
<Item Desc> Iron type </Item Desc>
<Item Desc> Red Colored</Item Desc>
<Item Desc> 0.2 dia <Item Desc>
<Item shipment Details>
<Item Shipment Id> 10001<Item Shipment Id>
<Item Shipment date>20100513<Item Shipment Data>
<Item Shipment Place>
<Icountry> India </Icountry>
<IState> TN </IState>
<ICity> Chennai </ICity>
<ItemGrade Name> G test <Item Grade Name>
<ItemGrade Name> G test old <Item Grade Name>
<ItemGrade Name> G test trial <Item Grade Name>
</Item Shipment Place>

<Item shipment Details>
</Item>

Here..
I have to load in the Item,Item Number, Item code in a particular table. (Item Number is the primary key)

Simillarly I have to load, Item Number, and Item description(could be repetitive, not necessarily 3) in another table. (Typically 3 rows with the same Item Number)

And ItemNumber,Item Shipment Id, Item Shipment Date, Item Shipement country, State and City in another table. Here, Item Number(FK), and Item Shipment Id (PK)

And Item Number, Item Shipment Id, and Item Grade in another table . (grade is repetitive as noted)

I was able to load the parent node details into a table, and i am not able to load the other details into their respective table. When i just drag and drop another link from xml inout stage it throws me the following error:

XML_Input_1,0: Error: The link contains more than one repetition rule

My design is as follows:
Esternal Source Stage -> XML Input stage -> Transformer-> Table !
|
Transformer -> table 2

Thanks.

[samyamkrishna]

Why dont you put everything in the transformer first and then split according to what you want.

So make the design like.

|--------------------------table
ExternalSrcStage-----XMLInput-------transformer----------------table
|_________________table

or

Load everything from the XML into a dataset and then from the dataset you can load it to the Tables.


Any way the error is regarding the key.( I guess)
In the input tab have you given any column as key.
try giving only one key and running the job.

[eostic]

Well....you're on the right track if you have been able to retrieve the parent info ok. Ultimately you will need a minimum of two links to retrieve this data, because there are at least two independent repeating nodes....Item Desc and Item Grade Name. I'm a little concerned about the lack of a containment element around each of them (a classic design would usually include another element such as "ItemDescriptions"), but it still might fly.... Each will be on its own link, possibly with parent details also, and each be the repeating element of those links....

Ernie

[eostic]

It works. I just did a test with your snippet of xml (btw...it is very invalid xml... where did you get this snippet from? I'll assume for the moment that you typed it in? It's missing proper close tags and the spaces in the element names are a problem too...perhaps they are underscores in your actual document?). Just make Item_Desc the key of one of your links, along with things like the Item_Code, and then have the Item_Grade retrieved on another link. Classic "multi-path" hierarchy.

Ernie

[Harini]

Thanks ernie.. Two links from xml input stage?? Thats where am getting this error...

yes, this is a sample xml... I tried to replicate the original one..Since i cannot post it..

Thanks

[eostic]

do you have two columns checked as a "key"....? each link can only have one column marked as a key...it should be one of the columns "in" the repeating element.

[Harini]

Thanks ernie.. It works.. I had checked two key columns in the xml input stage..

[vamsi.4a6]

I also have similar requirement but not sure what are the key columns i need to mention on XML output page.Any body input is really appreciated?

[chulett]

Key column. Singular. The whole point of this post is there can only be one. If you still need help, start a new post with your details.

[eostic]

....And that you need one output link for EACH repeating child node.