XML Input stage
Moderators: chulett, rschirm, roy
XML Input stage
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.
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.
-
- Premium Member
- Posts: 258
- Joined: Tue Jul 04, 2006 10:35 pm
- Location: Toronto
Re: XML Input stage
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.
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.
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
Ernie
Ernie Ostic
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
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
Ernie
Ernie Ostic
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
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.
Ernie Ostic
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
....And that you need one output link for EACH repeating child node.
Ernie Ostic
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>