XML source to splitted tables

dataservices job design
1

Hello,
I’m trying to read a simple XML file and then load the data in different tables.
ex: main tag : enterprise with it s own info (name/boss)
then 2nd level is a list of employee with their own info (name/age).

In Data Services I am able to scan this file (with DTD) and then split the info into 2 tables (enterprise/employee) but I don’t see how to link these two tables. What I saw in some tutorial is when ID (of the enterprise) is written in the XML. But in my case I don’t have any IDs and so I don’t know how to do it in DS.
In SSIS when using XML files, there is new IDs automaticaly generated to link parent - soon of the xml structure.

thanks for your help.

Cheers

mathieuBO (BOB member since 2011-01-06)

2

You can add an extra sequence field in the parent node then propagate that to the child schema when you unnest and load it.

jlynn73 :us: (BOB member since 2009-10-27)

3

Great it worked!
In the Query transform I added a new output column at the enterprise level with the formula “gen_row_num()”.
Still didn’t understood what to nest/unest but unested every thing seems to do be what I needed.

Thanks Jlynn73

mathieuBO (BOB member since 2011-01-06)