|
 |
asp_databases thread: XML Insert Import Help needed
Message #1 by "Sloan Thrasher" <cst2000@a...> on Mon, 21 Jan 2002 09:26:29 -0500
|
|
Hi all!
I have an XML file I need to be able to import. It contains multiple parent
child relationships. A simplified version looks something like:
<root>
<rpt_period>
<period_name>1st qtr</period_name>
<period_date>01/01/2001</period_date>
<publication>
<pub_name>FFS</pub_name>
<fund>
<fund_name>Growth</fund_name>
</fund>
<fund>
<fund_name>Income</fund_name>
</fund>
<fund>
<fund_name>Bonds</fund_name>
</fund>
</publication>
<publication>
<pub_name>BDG</pub_name>
<fund>
<fund_name>Growth</fund_name>
</fund>
<fund>
<fund_name>Income</fund_name>
</fund>
<fund>
<fund_name>Bonds</fund_name>
</fund>
</publication>
</rpt_period>
</root>
My question is, How can I setup an import routine that will place the parent
keys in the child record foriegn keys? The parent keys are automatically
generated when a record is added.
For example, the data above might create the following records in SQL
server:
Reporting_Periods (from the rpt_period entity above)
Rpt_Period_Key* Period_Name Period_Date
5 1st qtr 01/01/2001
Publications (from the publication entity above)
Pub_Key* Pub_Name Pub_RptPeriod_Key**
231 FFS 5
232 BDG 5
Funds (from the fund entity above)
Fund_Key* Fund_Name Fund_Pub_Key** Fund_RptPeriod_Key**
543 Growth 231 5
544 Income 231 5
545 Bonds 231 5
546 Growth 232 5
547 Income 232 5
548 Bonds 232 5
* = auto generated key
** = Foriegn keys
Any ideas? I have already coded ASP scripts that will do the import, but
they are much too slow, and will timeout. I've also written a VB version
that will run in the background, but it eats up to much ram on the server. I
was hoping that DTS might to the job, but haven't found a ay to do that.
Any help would be very much appreciated!
Sloan Thrasher
Message #2 by "Dallas Martin" <dmartin@z...> on Mon, 21 Jan 2002 10:57:11 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0006_01C1A26A.60B8EE50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
How large would a typical file be?
How often does the import occur?
What is the source of the XML file
shown below?
Do the same publication and fund names repeat
for every session?
Dallas
----- Original Message -----
From: Sloan Thrasher
Newsgroups: asp_databases,sql_server_asp,sql_server_xml
To: ASP Databases
Sent: Monday, January 21, 2002 9:26 AM
Subject: [asp_databases] XML Insert Import Help needed
Hi all!
I have an XML file I need to be able to import. It contains multiple
parent
child relationships. A simplified version looks something like:
<root>
<rpt_period>
<period_name>1st qtr</period_name>
<period_date>01/01/2001</period_date>
<publication>
<pub_name>FFS</pub_name>
<fund>
<fund_name>Growth</fund_name>
</fund>
<fund>
<fund_name>Income</fund_name>
</fund>
<fund>
<fund_name>Bonds</fund_name>
</fund>
</publication>
<publication>
<pub_name>BDG</pub_name>
<fund>
<fund_name>Growth</fund_name>
</fund>
<fund>
<fund_name>Income</fund_name>
</fund>
<fund>
<fund_name>Bonds</fund_name>
</fund>
</publication>
</rpt_period>
</root>
My question is, How can I setup an import routine that will place the
parent
keys in the child record foriegn keys? The parent keys are
automatically
generated when a record is added.
For example, the data above might create the following records in SQL
server:
Reporting_Periods (from the rpt_period entity above)
Rpt_Period_Key* Period_Name Period_Date
5 1st qtr 01/01/2001
Publications (from the publication entity above)
Pub_Key* Pub_Name Pub_RptPeriod_Key**
231 FFS 5
232 BDG 5
Funds (from the fund entity above)
Fund_Key* Fund_Name Fund_Pub_Key** Fund_RptPeriod_Key**
543 Growth 231
5
544 Income 231
5
545 Bonds 231
5
546 Growth 232 5
547 Income 232 5
548 Bonds 232
5
* =3D auto generated key
** =3D Foriegn keys
Any ideas? I have already coded ASP scripts that will do the import,
but
they are much too slow, and will timeout. I've also written a VB
version
that will run in the background, but it eats up to much ram on the
server. I
was hoping that DTS might to the job, but haven't found a ay to do
that.
Any help would be very much appreciated!
Sloan Thrasher
$subst('Email.Unsub').
Message #3 by "Sloan Thrasher" <cst2000@a...> on Wed, 23 Jan 2002 01:38:04 -0500
|
|
Hi Dallas!
I thought I had replied earlier, but email problems (lost my C drive today)
and such...
Anyway, see below:
Sloan
"Dallas Martin" <dmartin@z...> wrote in message
news:137940@a..._databases...
How large would a typical file be?
--5-6meg
How often does the import occur?
--Monthly or Quarterly
What is the source of the XML file
shown below?
--Hand typed as an example. The real XML will come from either SQL Server
web access or a file on disk
Do the same publication and fund names repeat
for every session?
--No, they are generally different and there can be multiple publications.
Actually, there are seven levels of nesting in the real file, and two of the
levels have two type of child tables.
Dallas
----- Original Message -----
From: Sloan Thrasher
Newsgroups: asp_databases,sql_server_asp,sql_server_xml
To: ASP Databases
Sent: Monday, January 21, 2002 9:26 AM
Subject: [asp_databases] XML Insert Import Help needed
Hi all!
I have an XML file I need to be able to import. It contains multiple parent
child relationships. A simplified version looks something like:
<root>
<rpt_period>
<period_name>1st qtr</period_name>
<period_date>01/01/2001</period_date>
<publication>
<pub_name>FFS</pub_name>
<fund>
<fund_name>Growth</fund_name>
</fund>
<fund>
<fund_name>Income</fund_name>
</fund>
<fund>
<fund_name>Bonds</fund_name>
</fund>
</publication>
<publication>
<pub_name>BDG</pub_name>
<fund>
<fund_name>Growth</fund_name>
</fund>
<fund>
<fund_name>Income</fund_name>
</fund>
<fund>
<fund_name>Bonds</fund_name>
</fund>
</publication>
</rpt_period>
</root>
My question is, How can I setup an import routine that will place the parent
keys in the child record foriegn keys? The parent keys are automatically
generated when a record is added.
For example, the data above might create the following records in SQL
server:
Reporting_Periods (from the rpt_period entity above)
Rpt_Period_Key* Period_Name Period_Date
5 1st qtr 01/01/2001
Publications (from the publication entity above)
Pub_Key* Pub_Name Pub_RptPeriod_Key**
231 FFS 5
232 BDG 5
Funds (from the fund entity above)
Fund_Key* Fund_Name Fund_Pub_Key** Fund_RptPeriod_Key**
543 Growth 231 5
544 Income 231 5
545 Bonds 231 5
546 Growth 232 5
547 Income 232 5
548 Bonds 232 5
* = auto generated key
** = Foriegn keys
Any ideas? I have already coded ASP scripts that will do the import, but
they are much too slow, and will timeout. I've also written a VB version
that will run in the background, but it eats up to much ram on the server. I
was hoping that DTS might to the job, but haven't found a ay to do that.
Any help would be very much appreciated!
Sloan Thrasher
$subst('Email.Unsub').
|
|
|
View Cart
