Importing a XML doc into relational tables.

15 Dec 2005 4:51 PM

crispin.proctor

HELP! This is driving me mad....

Greetings all.

I am trying to import XML into SQL.
I have the following ingredients:
1 X xml document with 2 levels. Parent > child
1 X xml source object
2 X tables in SQL.

The XML doc is quite simple. Parent element (PLU) and a child element
(DESC)
The SQL tables are PLU (PLU_Code) and PLU_Child (PLU_Code, DESC)
(PLU_Code is a FK.

When setting up the xml, it gives me two outputs (Auto created the xsd)

PLU and DESC.
PLU has PLU_Code and PLU_Id
DESC as PLU_Id and DESC.

As per MS, the _Id field is used to retain the relationship. Fine.

I can populate the main table (PLU) fine. I cannot populate the child
table because of the FK.

If MS provides the PLU_Id, how do I use it?

I do not want to use a lookup (Can't because on the child I do not
have a PLU_Code field).

Any guidance?

Cheers,
Crispin

15 Dec 2005 8:20 PM

fleo

You have to use the @mp:id and @mp:parentid meta-properties to JOIN the
results and get the Parent Key. The problem is you have to use temp tables
due to technological limitations. So it gives something like this:

SELECT * into #ParentTable
FROM OPENXML(@hDOC, 'ParentNode', flag)
WITH (ParentKey, xmlID int '@mp:id')

SELECT * into #ChildTable
FROM OPENXML(@hDOC, 'ChildNode', flag)
WITH (someChildFields ..., xmlParentID int '@mp:parentid')

Then JOIN the temp tables and INSERT:
INSERT INTO childTable (ParentKey, someChildFields)
SELECT ParentKey, someChildFields
FROM #ParentTable T1 INNER JOIN #ChildTable T2 ON
(T1.xmlID = T2.xmlParentID)

Have fun

Show quote

"crispin.proc***@gmail.com" wrote:

> HELP! This is driving me mad....
>
>
> Greetings all.
>
>
> I am trying to import XML into SQL.
> I have the following ingredients:
> 1 X xml document with 2 levels. Parent > child
> 1 X xml source object
> 2 X tables in SQL.
>
>
> The XML doc is quite simple. Parent element (PLU) and a child element
> (DESC)
> The SQL tables are PLU (PLU_Code) and PLU_Child (PLU_Code, DESC)
> (PLU_Code is a FK.
>
>
> When setting up the xml, it gives me two outputs (Auto created the xsd)
>
> PLU and DESC.
> PLU has PLU_Code and PLU_Id
> DESC as PLU_Id and DESC.
>
>
> As per MS, the _Id field is used to retain the relationship. Fine.
>
>
> I can populate the main table (PLU) fine. I cannot populate the child
> table because of the FK.
>
>
> If MS provides the PLU_Id, how do I use it?
>
>
> I do not want to use a lookup (Can't because on the child I do not
> have a PLU_Code field).
>
>
> Any guidance?
>
>
> Cheers,
> Crispin
>
>

16 Dec 2005 9:05 AM

crispin.proctor

Fleo,

This is the way I could get around it but this method detracts from the
new SSIS pipeline method and would be rather slow.
I could have very large documents which I now have to do the following:
1) Load each level into temp tables.
2) Join the temp tables together using the ID's created.
3) Load them into their final resting place. (This cannot be done in
step two as the keys are not unique)

Within that process, I have to do many lookups etc and find all this
would be rather slow.

Could they not be loaded into datasets and manipulated from there on?
I am busy with that but keep getting an error saying "You cannot add an
output column to the output collection" (?????)

Cheers,
Crispin

16 Dec 2005 3:40 PM

fleo

Hi,
Sorry I have no idea, I am fairly new to this. I am trying to do about the
same thing.

Anyone with ideas? Would .xsd schema with annotations help here?

Show quote

"crispin.proc***@gmail.com" wrote:

> Fleo,
>
> This is the way I could get around it but this method detracts from the
> new SSIS pipeline method and would be rather slow.
> I could have very large documents which I now have to do the following:
> 1) Load each level into temp tables.
> 2) Join the temp tables together using the ID's created.
> 3) Load them into their final resting place. (This cannot be done in
> step two as the keys are not unique)
>
> Within that process, I have to do many lookups etc and find all this
> would be rather slow.
>
> Could they not be loaded into datasets and manipulated from there on?
> I am busy with that but keep getting an error saying "You cannot add an
> output column to the output collection" (?????)
>
>
> Cheers,
> Crispin
>
>