Importing Relational Xml

Hi
"Normal" xml, I can import a file, I'm no guru but I can get it working.
Relational files seems to be a bit of a tough cookie for me. I read some
articles but I would like to stay as SQL as possible(most articles use 3rd
party apps). I was wondering if anyone can help me out either with the
know-how of how to upload these files , a link or a yes or no if SQL can
perform this task.
I don't mind using SQL webservices etc. Just no third party tools.
Help will really be appreciated.
ThanksCheck out SQLXML
<http://msdn.microsoft.com/library/d...
ch_SQLXML.asp>.
This includes an XML bulk load utility that can map XML into your relational
database schema.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:EFEFC852-0415-4110-86B4-3945D5C50C32@.microsoft.com...
> Hi
> "Normal" xml, I can import a file, I'm no guru but I can get it working.
> Relational files seems to be a bit of a tough cookie for me. I read some
> articles but I would like to stay as SQL as possible(most articles use 3rd
> party apps). I was wondering if anyone can help me out either with the
> know-how of how to upload these files , a link or a yes or no if SQL can
> perform this task.
> I don't mind using SQL webservices etc. Just no third party tools.
> Help will really be appreciated.
> Thanks
>

Importing a XML doc into relational tables.

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,
CrispinYou 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
"crispin.proctor@.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
>|||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|||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?
"crispin.proctor@.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
>