importing xml document to multiple related table with identity column.

Hi,
I am new to XML and looking for some information and suggestion. I need
to import xml document into related table having identity column, could
any one let me know which is the best way to do this. detail
information:
suppose i have 3 xml document call a.xml, b.xml and c.xml. I need to
bring information from these xml document to 3 different table whcih
are related with identity key (primary identity key).
table A: has identity key (A1)which is also a primary key with other
field.
table B: has identity key (B1)which is a primany key, column A1 which
is FK and other field
table C: has identity key (C1) which is a primary key, Column A1 wihic
is FK and other field.
xml document a.xml contain the information or record for table A, b.xml
contain for table B and c.xml contain for table C.
First i would like to bring the information from a.xml to the table A,
withour identity key from xml, it will be generated to the sql server
(this is only one row of data), the identity generated with be the max
of identity. I will like to bring this max of identity or identity
field just generated along with othere infromation from b.xml to table
B, similar to table C.
Could any one let me konw which is the best way to solve this problem.
Thanks in advance.
Indra.Can you post DDL (CREATE TABLE statements) for your tables, an example of
the XML document you're trying to import, and a list of which fields from
the XML correspond to which columns in the tables?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
<itimilsina@.savannaenergy.com> wrote in message
news:1105992192.804216.302410@.z14g2000cwz.googlegroups.com...
> Hi,
> I am new to XML and looking for some information and suggestion. I need
> to import xml document into related table having identity column, could
> any one let me know which is the best way to do this. detail
> information:
> suppose i have 3 xml document call a.xml, b.xml and c.xml. I need to
> bring information from these xml document to 3 different table whcih
> are related with identity key (primary identity key).
> table A: has identity key (A1)which is also a primary key with other
> field.
> table B: has identity key (B1)which is a primany key, column A1 which
> is FK and other field
> table C: has identity key (C1) which is a primary key, Column A1 wihic
> is FK and other field.
> xml document a.xml contain the information or record for table A, b.xml
> contain for table B and c.xml contain for table C.
> First i would like to bring the information from a.xml to the table A,
> withour identity key from xml, it will be generated to the sql server
> (this is only one row of data), the identity generated with be the max
> of identity. I will like to bring this max of identity or identity
> field just generated along with othere infromation from b.xml to table
> B, similar to table C.
> Could any one let me konw which is the best way to solve this problem.
> Thanks in advance.
> Indra.
>|||You can create a stored procedure which takes these XMLs as parameters
nText.
Load the XMLs into XMLDOcuments using sp_xml_preparedocument to read xml
contents.
you can check how to use this extended stored procedure here..
http://msdn.microsoft.com/library/d...r />
_267o.asp
First prepare the insert statement to Insert Into Table A. Get identity
column with Select Scope_Identity()
use this to insert into Table B and Table C by preparing the insert
statements using XML B and C.
you will have to loop through the records. The best way would be to Insert
all records into Table A. Then write statements insert into
TabeB and C. it can be done simply with just 3 insert statements. but you
need to map tTable A records with B and C.
--
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
<itimilsina@.savannaenergy.com> wrote in message
news:1105992192.804216.302410@.z14g2000cwz.googlegroups.com...
> Hi,
> I am new to XML and looking for some information and suggestion. I need
> to import xml document into related table having identity column, could
> any one let me know which is the best way to do this. detail
> information:
> suppose i have 3 xml document call a.xml, b.xml and c.xml. I need to
> bring information from these xml document to 3 different table whcih
> are related with identity key (primary identity key).
> table A: has identity key (A1)which is also a primary key with other
> field.
> table B: has identity key (B1)which is a primany key, column A1 which
> is FK and other field
> table C: has identity key (C1) which is a primary key, Column A1 wihic
> is FK and other field.
> xml document a.xml contain the information or record for table A, b.xml
> contain for table B and c.xml contain for table C.
> First i would like to bring the information from a.xml to the table A,
> withour identity key from xml, it will be generated to the sql server
> (this is only one row of data), the identity generated with be the max
> of identity. I will like to bring this max of identity or identity
> field just generated along with othere infromation from b.xml to table
> B, similar to table C.
> Could any one let me konw which is the best way to solve this problem.
> Thanks in advance.
> Indra.
>|||"avnrao" <avn@.newsgroups.com> wrote in message
news:uTIecHT$EHA.612@.TK2MSFTNGP09.phx.gbl...
> First prepare the insert statement to Insert Into Table A. Get identity
> column with Select Scope_Identity()
> use this to insert into Table B and Table C by preparing the insert
> statements using XML B and C.
> you will have to loop through the records. The best way would be to Insert
> all records into Table A. Then write statements insert into
> TabeB and C. it can be done simply with just 3 insert statements. but you
> need to map tTable A records with B and C.
If there are multiple rows inserted, SCOPE_IDENTITY() will not do the
job. And there is no reason to do a loop of any kind. The entire thing
should be able to be done in a set-based manner.|||Hit send too soon on the last message.
Something like this... no loops necessary:
use tempdb
go
create table abc(somedata char(10), abcID int identity(1,1) primary key)
go
create table def(somedata char(10), abcID int references abc(abcid))
go
declare @.xmldoc varchar(8000)
set @.xmldoc =
'<anode>
<another somedata="something">
<blah someother="else"/>
</another>
<another somedata="something2">
<blah someother="else2"/>
</another>
</anode>'
DECLARE @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.xmldoc
insert abc (somedata)
SELECT somedata
FROM OPENXML (@.hdoc, '/anode/another')
with (somedata char(10) '@.somedata')
insert def (somedata, abcid)
SELECT x.someother, abc.abcid
FROM
(SELECT somedata, someother
FROM OPENXML (@.hdoc, '/anode/another')
with (
somedata char(10) '@.somedata',
someother char(10) 'blah/@.someother')) x (somedata, someother)
JOIN abc ON abc.somedata = x.somedata
EXEC sp_xml_removedocument @.hdoc
go
select *
from abc
go
select *
from def
go
drop table def
drop table abc
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

importing xml document to multiple related table with identity column.

Hi,
I am new to XML and looking for some information and suggestion. I need
to import xml document into related table having identity column, could
any one let me know which is the best way to do this. detail
information:
suppose i have 3 xml document call a.xml, b.xml and c.xml. I need to
bring information from these xml document to 3 different table whcih
are related with identity key (primary identity key).
table A: has identity key (A1)which is also a primary key with other
field.
table B: has identity key (B1)which is a primany key, column A1 which
is FK and other field
table C: has identity key (C1) which is a primary key, Column A1 wihic
is FK and other field.
xml document a.xml contain the information or record for table A, b.xml
contain for table B and c.xml contain for table C.
First i would like to bring the information from a.xml to the table A,
withour identity key from xml, it will be generated to the sql server
(this is only one row of data), the identity generated with be the max
of identity. I will like to bring this max of identity or identity
field just generated along with othere infromation from b.xml to table
B, similar to table C.
Could any one let me konw which is the best way to solve this problem.
Thanks in advance.
Indra.
Can you post DDL (CREATE TABLE statements) for your tables, an example of
the XML document you're trying to import, and a list of which fields from
the XML correspond to which columns in the tables?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
<itimilsina@.savannaenergy.com> wrote in message
news:1105992192.804216.302410@.z14g2000cwz.googlegr oups.com...
> Hi,
> I am new to XML and looking for some information and suggestion. I need
> to import xml document into related table having identity column, could
> any one let me know which is the best way to do this. detail
> information:
> suppose i have 3 xml document call a.xml, b.xml and c.xml. I need to
> bring information from these xml document to 3 different table whcih
> are related with identity key (primary identity key).
> table A: has identity key (A1)which is also a primary key with other
> field.
> table B: has identity key (B1)which is a primany key, column A1 which
> is FK and other field
> table C: has identity key (C1) which is a primary key, Column A1 wihic
> is FK and other field.
> xml document a.xml contain the information or record for table A, b.xml
> contain for table B and c.xml contain for table C.
> First i would like to bring the information from a.xml to the table A,
> withour identity key from xml, it will be generated to the sql server
> (this is only one row of data), the identity generated with be the max
> of identity. I will like to bring this max of identity or identity
> field just generated along with othere infromation from b.xml to table
> B, similar to table C.
> Could any one let me konw which is the best way to solve this problem.
> Thanks in advance.
> Indra.
>
|||You can create a stored procedure which takes these XMLs as parameters
nText.
Load the XMLs into XMLDOcuments using sp_xml_preparedocument to read xml
contents.
you can check how to use this extended stored procedure here..
http://msdn.microsoft.com/library/de...p_xml_267o.asp
First prepare the insert statement to Insert Into Table A. Get identity
column with Select Scope_Identity()
use this to insert into Table B and Table C by preparing the insert
statements using XML B and C.
you will have to loop through the records. The best way would be to Insert
all records into Table A. Then write statements insert into
TabeB and C. it can be done simply with just 3 insert statements. but you
need to map tTable A records with B and C.
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
<itimilsina@.savannaenergy.com> wrote in message
news:1105992192.804216.302410@.z14g2000cwz.googlegr oups.com...
> Hi,
> I am new to XML and looking for some information and suggestion. I need
> to import xml document into related table having identity column, could
> any one let me know which is the best way to do this. detail
> information:
> suppose i have 3 xml document call a.xml, b.xml and c.xml. I need to
> bring information from these xml document to 3 different table whcih
> are related with identity key (primary identity key).
> table A: has identity key (A1)which is also a primary key with other
> field.
> table B: has identity key (B1)which is a primany key, column A1 which
> is FK and other field
> table C: has identity key (C1) which is a primary key, Column A1 wihic
> is FK and other field.
> xml document a.xml contain the information or record for table A, b.xml
> contain for table B and c.xml contain for table C.
> First i would like to bring the information from a.xml to the table A,
> withour identity key from xml, it will be generated to the sql server
> (this is only one row of data), the identity generated with be the max
> of identity. I will like to bring this max of identity or identity
> field just generated along with othere infromation from b.xml to table
> B, similar to table C.
> Could any one let me konw which is the best way to solve this problem.
> Thanks in advance.
> Indra.
>
|||"avnrao" <avn@.newsgroups.com> wrote in message
news:uTIecHT$EHA.612@.TK2MSFTNGP09.phx.gbl...
> First prepare the insert statement to Insert Into Table A. Get identity
> column with Select Scope_Identity()
> use this to insert into Table B and Table C by preparing the insert
> statements using XML B and C.
> you will have to loop through the records. The best way would be to Insert
> all records into Table A. Then write statements insert into
> TabeB and C. it can be done simply with just 3 insert statements. but you
> need to map tTable A records with B and C.
If there are multiple rows inserted, SCOPE_IDENTITY() will not do the
job. And there is no reason to do a loop of any kind. The entire thing
should be able to be done in a set-based manner.
|||Hit send too soon on the last message.
Something like this... no loops necessary:
use tempdb
go
create table abc(somedata char(10), abcID int identity(1,1) primary key)
go
create table def(somedata char(10), abcID int references abc(abcid))
go
declare @.xmldoc varchar(8000)
set @.xmldoc =
'<anode>
<another somedata="something">
<blah someother="else"/>
</another>
<another somedata="something2">
<blah someother="else2"/>
</another>
</anode>'
DECLARE @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.xmldoc
insert abc (somedata)
SELECT somedata
FROM OPENXML (@.hdoc, '/anode/another')
with (somedata char(10) '@.somedata')
insert def (somedata, abcid)
SELECT x.someother, abc.abcid
FROM
(SELECT somedata, someother
FROM OPENXML (@.hdoc, '/anode/another')
with (
somedata char(10) '@.somedata',
someother char(10) 'blah/@.someother')) x (somedata, someother)
JOIN abc ON abc.somedata = x.somedata
EXEC sp_xml_removedocument @.hdoc
go
select *
from abc
go
select *
from def
go
drop table def
drop table abc
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic

importing txt file to multiple table in sql 2000

Hi There,

I am looking for information on how to import the txt or csv file to
the multiple table in sql 2000. If you have any kind of inf. please
let me know wheather we can do this and how.

below is the detail information.

I received txt file every day which contain the information from 3
different related table in my sql 2000 database. Right now we are
keyin the information from the web site (which is link to the txt
file) to our database, but i am wondering if we can import those
record in the tables.

the header of the file goes to table1 and when we insert the record in
table1, it should generate the autoidentityrecord (PK), and that PK is
link to other table2 and table3 where rest of the information from txt
file goes. For table2 and table3 there are multiple record per txt
files.

in our txt file each row is separated with row header, like HTC100
WITH ROW NO. 1,2,3.., which indecate this information goes to table
and 1,2...are the different row number.

Please let me know whether we can achive this task or not.

Thanks for all you help in advance.

Indra.

I have pasted my txt file below:
========

"FHS000",20041029,0900,,"10",1,"TRAILB10_20041029_1B",3,"2.20","Pason-DataHub",,"#
Well 1098831406 Tour 2004/10/29 Trailblazer 10 148",1,"EDR_3-0-10_HF2
ETS 2.2"
"CON000",1,0000,0759
"CON000",2,0800,1559
"CON000",3,1600,2359
"HWI010","0312857","COMPTON BRANT 15-7-18-24","COMPTON PETROLEUM
CORP.","TRAILBLAZER DRILLING
CORP.","15-07-018-24W4","100/15-07-018-24W4/00","HANK
PARANYCH","CURTIS FIESEL",20041029,,,"10",20041027,0600,,,"148","DD04485","VERT.","NO",,
"HCO030",1,"Daily Walk Around Inspection","HP","CF"
"HCO030",2,"Detailed Inspection - Weekly (using checklist)","HP","CF"
"HCO030",3,"H2S Signs Posted (if required)",,
"HCO030",4,"Well License & Stick Diagram Posted","HP","CF"
"HCO030",5,"Flare Lines Staked","HP","CF"
"HCO030",6,"BOP Drills Performed","HP","CF"
"HCO030",7,"Visually Inspect BOP's - Flarelines and Degasser
Lines","HP","CF"
"HDC040",1,"Rig Site Health and Safety Meeting (one/crew/month)","CF"
"HDC040",2,"C.A.O.D.C. Rig Safety Inspection Checklist
(one/rig/month)","CF"
"HDC040",3,"Mast Inspection Before Raising or Lowering","CF"
"HDC040",4,"Crown Saver Checked","CF"
"HDC040",5,"Motor Kills Checked","CF"
"HFU050",2300,2100,,
"HWE060",-5,"Deg C","COOL","WEST","SLIPPERY",,
"HCS070",1,177.8,,"mm",25.3,"STELCO","J-55",8,108.44,3.84,108.44,
"HCS070",2,114.3,,"mm",14.14,"STELCO","J-55",72,979.50,3.84,979.0,
"HDP080",1,127,79.4,"kg/m","E",57,127,"mm","3 1/2 IF",10,"DC",
"HDP080",2,89,19.7,"kg/m","E",68,120,"mm","3 1/2 IF",15,"DP",
"HPU090",1,"F-800","EMSCO",254,"mm",,,,
"HPU090",2,"F-800","EMSCO",254,"mm",,,,
"HTC100",1,"Rig up and tear down"
"HTC100",2,"Drill Actual"
"HTC100",3,"Reaming"
"HTC100",4,"Coring"
"HTC100",5,"Condition Mud & Circulate"
"HTC100",6,"Trips"
"HTC100",7,"Rig Service"
"HTC100",8,"Repair Rig"
"HTC100",9,"Cut off drilling line"
"HTC100",10,"Deviation Survey"
"HTC100",11,"Wire Line Logs"
"HTC100",12,"Run Case & Cement"
"HTC100",13,"Wait on Cement"
"HTC100",14,"Nipple up B.O.P."
"HTC100",15,"Test B.O.P."
"HTC100",16,"Drill Stem Test"
"HTC100",17,"Plug Back"
"HTC100",18,"Squeeze Cement"
"HTC100",19,"Fishing"
"HTC100",20,"Directional Work"
"HTC100",21,"Safety Meeting"
"HTC100",24,"WOD"
"HSS110",1,1,"SWACO","N","110",,"84",,
"HPA130","COMPTON BRANT 15-7-18-24",20041029,"COMPTON PETROLEUM
CORP.","TRAILBLAZER DRILLING CORP.","CURTIS
FIESEL","10","ALBERTA","N",253
"TCP130",1,,,,"kPa",140,,,,"mm",,
"TCP130",2,,,,"kPa",140,,,,"mm",,
"TCP130",3,,,,"kPa",140,,,,"mm",,
"TTL160",1,1,0.00,0.25,0.25,21,"SAFETY MEETING WITH TONG HAND"
"TTL160",1,2,0.25,1.75,1.50,12,"RIG TO AND RUN CASING"
"TTL160",1,3,1.75,2.00,0.25,7,"RIG SERVICE"
"TTL160",1,4,2.00,2.50,0.50,5,"CONDITION MUD & CIRC."
"TTL160",1,5,2.50,2.75,0.25,21,"SAFETY MEETING WITH CEMENTERS"
"TTL160",1,6,2.75,3.50,0.75,12,"RIG TO AND CEMENT CASING"
"TTL160",1,7,3.50,6.00,2.50,1,"SET SLIPS, TEAR OUT RIG, CLEAN TANKS"
"TTL160",1,8,6.00,8.00,2.00,24,"WAIT ON DAYLIGHT/TRUCKS"
"TTL160",1,9,,,,,"CEMENT WITH BJ USING 13 TONNES OF BVF-1500 NP + .7%
FL-5,GIVING 15.5 m3 OF GOOD"
"TTL160",1,10,,,,,"SLURRY @. 1718 kg/m3,PLUG BUMPED & HELD @. 03:30 HRS
OCT 29/04."
"TTL160",1,11,,,,,"RIG RELEASED @. 08:00 HRS OCT 29/04"
"TTL160",1,12,,,,,"MOVE TO 12-3-18-25W4"
"TDI170",1,"JEFF CASE",8,10,475,"Deg C",,,"RUNNING CASING",,,,,
"TLN175",1,"VISUALLY INSPECT PINS, RAMS AND STOOLS PRIOR TO LAYING
OVER DERRICK"
"TPA180",1,1,"DRILLER",647172865,"JEFF CASE",8,,,"JC"
"TPA180",1,2,"DERRICK HAND",648519056,"BRYAN VANHAM",8,,,"BV"
"TPA180",1,3,"MOTOR HAND",651056533,"NEIL WILLIAMS",8,,,"NW"
"TPA180",1,4,"FLOOR HAND",640352662,"TARAS WOITAS",8,,,"TW"
"TPI190",1,"REG",25,,,,,,
"TPI190",2,"REG",25,,,,,,
"TPI190",3,"REG",25,,,,,,
=====[posted and mailed, please reply in news]

Indra (itimilsina@.savannaenergy.com) writes:
> I am looking for information on how to import the txt or csv file to
> the multiple table in sql 2000. If you have any kind of inf. please
> let me know wheather we can do this and how.
> below is the detail information.
> I received txt file every day which contain the information from 3
> different related table in my sql 2000 database. Right now we are
> keyin the information from the web site (which is link to the txt
> file) to our database, but i am wondering if we can import those
> record in the tables.
> the header of the file goes to table1 and when we insert the record in
> table1, it should generate the autoidentityrecord (PK), and that PK is
> link to other table2 and table3 where rest of the information from txt
> file goes. For table2 and table3 there are multiple record per txt
> files.
> in our txt file each row is separated with row header, like HTC100
> WITH ROW NO. 1,2,3.., which indecate this information goes to table
> and 1,2...are the different row number.
> Please let me know whether we can achive this task or not.

Of course, it is possible. However, as far as I can see only by means of
writing a program that reads and parses the file. The standard tools for
loading files into SQL Server are BCP and DTS. BCP cannot cope with your
file, because there is a mix of record formats. BCP can only import files
with uniform records.

DTS is more versatile than BCP, but I as far as can see, you will still
have to write code to have DTS to import the file. I need to add the
disclaimer that I have zero experience of DTS.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I agree with Erland. Could it be done with BCP and TSQL? Yes. Would it be
an efficient, maintainable, error resilient code? No.

It would be far better to write a piece of code or better get the sender to
supply X number of files, one for each table. The original creator of the
file obviously has control over the file format.

Danny

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95998D7498D77Yazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> Indra (itimilsina@.savannaenergy.com) writes:
>> I am looking for information on how to import the txt or csv file to
>> the multiple table in sql 2000. If you have any kind of inf. please
>> let me know wheather we can do this and how.
>>
>> below is the detail information.
>>
>> I received txt file every day which contain the information from 3
>> different related table in my sql 2000 database. Right now we are
>> keyin the information from the web site (which is link to the txt
>> file) to our database, but i am wondering if we can import those
>> record in the tables.
>>
>> the header of the file goes to table1 and when we insert the record in
>> table1, it should generate the autoidentityrecord (PK), and that PK is
>> link to other table2 and table3 where rest of the information from txt
>> file goes. For table2 and table3 there are multiple record per txt
>> files.
>>
>> in our txt file each row is separated with row header, like HTC100
>> WITH ROW NO. 1,2,3.., which indecate this information goes to table
>> and 1,2...are the different row number.
>>
>> Please let me know whether we can achive this task or not.
> Of course, it is possible. However, as far as I can see only by means of
> writing a program that reads and parses the file. The standard tools for
> loading files into SQL Server are BCP and DTS. BCP cannot cope with your
> file, because there is a mix of record formats. BCP can only import files
> with uniform records.
> DTS is more versatile than BCP, but I as far as can see, you will still
> have to write code to have DTS to import the file. I need to add the
> disclaimer that I have zero experience of DTS.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Danny (istdrs@.flash.net) writes:
> I agree with Erland. Could it be done with BCP and TSQL? Yes.

No. :-) Since there is a variable number of fields on each row, BCP would
be lost.

> It would be far better to write a piece of code or better get the sender
> to supply X number of files, one for each table. The original creator
> of the file obviously has control over the file format.

Yes, fixing the file format may very well be a good idea.

Interesting enough, it could still be one file, if the shorter rows were
padded with extra fields. Note that just adding ,,,,, would not be enough,
since the " too are delimiters as far as BCP is concerned, so the usage of
" must be consistent from record to record. (Which does not seem to be the
case in the current file.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

I am still trying to solve this problem of inserting txt file and
manupulating data to insert in differet table in sql 2000. I have
problem of inserting nvarchar which is coming from txt file to datetime
field in sql table.

If you look through my txt file there is a row start with TTL160 AND WE
HAVE COL4 AND COL5 WITH DATA LIKE 1.00 AND 12.23 ETC,(WHICH IS IN
NVARCHAR), I NEED TO INSERT THIS VALUE AS 1:00 OR 12:23 IN MY ANOTHER
TABEL IN DATETIME FIEDL. I COULD USED REPLACE(COLUMN, '.',':') TO
REPLACE FROM 12.23 TO 12:23 BUT WHEN I INSERT THIS TO SQL DATETIME FIELD
ITS GIVING ME ERROR " Arithmetic overflow error converting expression
to data type datetime".I try to use Cast function, still same error,
could MICROSOFT have a look please.

Thanks for help.

Indra.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Importing Multiple XML Field values

Hi,
Does anyone know how to make SQLXMLBulkLoad accept a SQL file if it has a
field repeated with more than 1 value. For example
<ROOT>
<Cust>
<details>
<Val1>23</Val1>
<Val2>44</Val2>
<Val3>16</Val3>
<Val3>77</Val3>
<Val4>47</Val4>
</details>
</Cust>
</ROOT>
As Val3 appears twice in the file, SQLXMLBulkLoad fails because it already
has a mapping for this field. Is there a way to modify the schema definition
to allow for this?
I don't actually care which of the values for Val3 the import process saves,
although I would like one or the other.
The schema currently looks something like:
<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<element name="Cust" sql:relation="CustTable" >
<complexType>
<sequence>
<element name="Val1" sql:field="CusVal1" type="integer" />
<element name="Val2" sql:field="CusVal2" type="integer" />
<element name="Val3" sql:field="CusVal3" type="integer" />
<element name="Val4" sql:field="CusVal4" type="integer" />
<sequence>
<complexType>
<element>
<schema>
Thanks
Bod
There is no way to get bulkload to work this way.
Your options would be to:
1. clean the data ahead of time using XSLT, or
2. do the inserts using the Server's nodes() method
This article has examples of using nodes()
http://msdn.microsoft.com/library/de.../forxml2k5.asp
|||Thanks Todd. Not my favourite answer but your confirmation that I can't do
it saves me wasting hours of trying...
Bod
"Todd Pfleiger [MSFT]" wrote:

> There is no way to get bulkload to work this way.
> Your options would be to:
> 1. clean the data ahead of time using XSLT, or
> 2. do the inserts using the Server's nodes() method
> This article has examples of using nodes()
> http://msdn.microsoft.com/library/de.../forxml2k5.asp
>
sql

Importing Multiple XML Field values

Hi,
Does anyone know how to make SQLXMLBulkLoad accept a SQL file if it has a
field repeated with more than 1 value. For example
<ROOT>
<Cust>
<details>
<Val1>23</Val1>
<Val2>44</Val2>
<Val3>16</Val3>
<Val3>77</Val3>
<Val4>47</Val4>
</details>
</Cust>
</ROOT>
As Val3 appears twice in the file, SQLXMLBulkLoad fails because it already
has a mapping for this field. Is there a way to modify the schema definitio
n
to allow for this?
I don't actually care which of the values for Val3 the import process saves,
although I would like one or the other.
The schema currently looks something like:
<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<element name="Cust" sql:relation="CustTable" >
<complexType>
<sequence>
<element name="Val1" sql:field="CusVal1" type="integer" />
<element name="Val2" sql:field="CusVal2" type="integer" />
<element name="Val3" sql:field="CusVal3" type="integer" />
<element name="Val4" sql:field="CusVal4" type="integer" />
<sequence>
<complexType>
<element>
<schema>
Thanks
BodThere is no way to get bulkload to work this way.
Your options would be to:
1. clean the data ahead of time using XSLT, or
2. do the inserts using the Server's nodes() method
This article has examples of using nodes()
http://msdn.microsoft.com/library/d...r />
ml2k5.asp|||Thanks Todd. Not my favourite answer but your confirmation that I can't do
it saves me wasting hours of trying...
Bod
"Todd Pfleiger [MSFT]" wrote:

> There is no way to get bulkload to work this way.
> Your options would be to:
> 1. clean the data ahead of time using XSLT, or
> 2. do the inserts using the Server's nodes() method
> This article has examples of using nodes()
> http://msdn.microsoft.com/library/d.../>
rxml2k5.asp
>|||Bod-
Can you post your final schema file that you are using for this bulk
load?
Thanks
Brian
briankudera
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message2239821.html

Importing multiple flat files to multiple tables in SSIS

I have a couple of hundred flat files to import into database tables using SSIS.

The files can be divided into groups by the format they use. I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container.

However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table.

Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to make a Data Flow Destination item accept its table name dynamically, which seems to prevent me doing this.

I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?

In an OLEDB destination you can select to use a "Table or view name from variable" -in this way the destination can be dynamic.

|||Thanks, I missed that.|||

Any suggestions on the best way to assign the destination table name variable?

I imagine could do it in a Script item in the Foreach Loop before the Data Flow item is executed, but is there somewhere to do it more in-line, like building it into the Data Flow item's properties somehow?

|||

Use some logic to derive the table name based on the format, which I guess must be derivable from the filename?

You have the filename, so how do you expect to transform this into a table name. I would expect an expression to be used somewhere, the tablename variable for example, but you may need to refer to some logic table. an Execute SQL Task inside the loop could query a SQL table that gave you the destination table from a filename. The Exec SQL Task result could then be assigned to the tablename variable.

Importing multiple files to SQL

I have over three hundred text files that I need to import to SQL
Server.
Each is in the exact same format.
I want to import tham as seperate tables.
Is there any way to do it in one process?

Regards,
CiarnYou can use DTS:
http://www.sqldts.com/default.aspx?246

If all the files are the same format then why not import them to a
single table with an extra column to identify the source file? That
should be much more convenient than creating 300 separate tables.

--
David Portas
SQL Server MVP
--|||I use the above method, but if you want seperate tables:

You could add in dynamic properties and some scripts to the workflow of
the DTS. A script task sets a source filename dynamic property. A
create table task uses the dynamic property as the table name. Then
import into the new table from the file(again using the dynamic
properties to set the source filename and destination table name).

Using only one table will make some things much easier, and other
things harder. Consider that with multiple tables you will have to
either have a copy of all your queries for each table, or have a stored
procedure that allows the table name to be specified as a parameter. I
think it would be easier to put all the data in one table and have a
sproc that takes the code for the special column as one of the
filtering criteria. I think the syntax for specifying the criteria for
that column is no harder than specifying a table name.|||Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from
http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\CiaranHudson\My Documents and all its sub
directories?|||Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from
http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\CiaranHudson\My Documents and all its sub
directories?|||Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from
http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\CiaranHudson\My Documents and all its sub
directories?

Importing multiple Excel files.

Hello, I have a SQL Server 2000 database that I want to load with Excel
files. All the Excel files are in one folder and have the same column names.
What is the best way to get them into a table in the database? While
looping though the folder can you just do a bulk insert on the excel files?
"Don" <Don@.discussions.microsoft.com> wrote in message
news:510DB3BC-CAFD-42BC-8262-9D43B577A82D@.microsoft.com...
> Hello, I have a SQL Server 2000 database that I want to load with Excel
> files. All the Excel files are in one folder and have the same column
> names.
> What is the best way to get them into a table in the database? While
> looping though the folder can you just do a bulk insert on the excel
> files?
Use DTS. The following example uses a text file but it could equally be
Excel:
http://www.sqldts.com/default.aspx?246
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Importing multiple Excel files.

Hello, I have a SQL Server 2000 database that I want to load with Excel
files. All the Excel files are in one folder and have the same column names.
What is the best way to get them into a table in the database? While
looping though the folder can you just do a bulk insert on the excel files?"Don" <Don@.discussions.microsoft.com> wrote in message
news:510DB3BC-CAFD-42BC-8262-9D43B577A82D@.microsoft.com...
> Hello, I have a SQL Server 2000 database that I want to load with Excel
> files. All the Excel files are in one folder and have the same column
> names.
> What is the best way to get them into a table in the database? While
> looping though the folder can you just do a bulk insert on the excel
> files?
Use DTS. The following example uses a text file but it could equally be
Excel:
http://www.sqldts.com/default.aspx?246
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Importing multiple Excel files.

Hello, I have a SQL Server 2000 database that I want to load with Excel
files. All the Excel files are in one folder and have the same column names
.
What is the best way to get them into a table in the database? While
looping though the folder can you just do a bulk insert on the excel files?"Don" <Don@.discussions.microsoft.com> wrote in message
news:510DB3BC-CAFD-42BC-8262-9D43B577A82D@.microsoft.com...
> Hello, I have a SQL Server 2000 database that I want to load with Excel
> files. All the Excel files are in one folder and have the same column
> names.
> What is the best way to get them into a table in the database? While
> looping though the folder can you just do a bulk insert on the excel
> files?
Use DTS. The following example uses a text file but it could equally be
Excel:
http://www.sqldts.com/default.aspx?246
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Importing multiple Excel Files

I have a client who is sending me 800+ excel files each month with sales data. Each of the files is identical in structure, but has sales data for different stores. I receive all these files at the same time.

Is there a method with Data Transformation Services where I can have it work off of all the files in a given directory. I can set up DTS to work off of specific Excel files with no problem, but what I would like to do is set up a DTS so it could pull from each of the 800+ files.

Is this possible, or do I need to look at a solution outside of SQL to consolidate the Excel files first?

The Excel file would have columns similar to the following: store_id, zip_code, sales, transactions.There are lots of different ways to handle this kind of problem.

By far my largest concern with 800+ Excel files is GIGO (Garbage In, Garbage Out). Even one sheet of undetected junque data, and you can't get a correct answer! Before I can even suggest a technical solution, I need to understand what the risks of bad data are for you, and how much manpower you are willing to commit to either detecting or correcting the bad data... That answer will strongly influence what I suggest for a solution.

-PatP|||In terms of the quality of the data, it comes directly from my client, so as long as what I have stored in our database matches what they gave me, I am in good shape. I run some additional reports from our application to spot anomalies in the data quality.

In terms of confidence in the file structure integrity: the client isn't very sophisticated (i.e. they cannot generate one file with all the stores in it), but their result file is very simple and routine. Last month was the first time I had gone through this process with them, and all 843 files were clean.

In terms of manpower... I'm a one man technical staff for a company with 50--70 employees, and several clients, and the database management / design is only one part of my responsibilities. Considering I would be doing this particular task once a month, 30 min - 45 min of effort would hopefully be the max I would have to spend.

Having done manipulation of customer / client files for several years now, I know to expect the unexpected; and GIGO is always one of my biggest concerns. Having no support staff to help, limits the effort I can make, but I need to know that what I received is what I loaded. If something cannot be loaded, identifying that would be great.

I don't mind loading the data into a staging area... and then doing some post-load validations in SQL before moving the data to the intended tables in the database. From my perspective, the key is getting it out of the Excel format, which is terribly unfriendly for me to validate against, into a format / structure that I can.

Does that help guide you?|||I am not sure about DTS, because scripting is my preference, so there's gotta be someone that would come up with a DTS solution (I've seen a couple of samples on the net that demonstrate ennumeration of files in a directory from a DTS/SSIS package).

But in scripting world you can have a batch file that would dump a list of files into a file queue table, and then extract a script-like output into a TSQL script that you can run in the next step with osql/sqlcmd.

When I had to deal with Excel files in the past, I ended up building linked servers on the fly for each file in order to be able to handle everything in TSQL (plenty of references on the net as to how to create a linked server to an excel spreadsheet).|||You can do this in DTS by defining a filename variable and using VB to cycle through the files. So what you do depends upon whether your comfort zone is in VB or in scripting.

Importing multiple Access databases

Hi,
I have 30 remote sites that each day send an access
database of sales figures for that day, how can I automate
the process of importing these into an SQL database. I run
SQL server 2000.
As simple a solution as possible please as I am new to SQL
server.
Thanks Dave
Data Transform Services (DTS) would typically be used to import Access data in to SQL server tables, and packages can be scheduled. There is coverage of DTS in BOL and there are several DTS books (Professional SQL Server 2000 DTS is good).
A way to get started is to use the DTS Import/Export Wizard (see tools/Data Transformation Services) with one of the Access databases as the source and the SQL Server database as the destination. Check the Save Package box (select SQL Server for storage
) to save your package to SQL Server (you might want to uncheck the execute imediate box) and use it as a template.

> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave
>
|||Assuming the access db's always have the same names, and are located in the
same way, you should be able to simply import the data into your SQL
database, by right clicking the database, selecting import, and then going
through the wizard which is reasonably self explanatory.
At the end of the wizard you will be asked if you want to run the import
now, save it, or schedule it. If you choose the schedule option you can then
select a time for that data to be imported, and how often it should be done,
and from there it should go through without a hitch. To test it, go to
Management, SQL Server Agent, Jobs, and you should see your DTS job in the
right hand side. Right click on it and tell it to start, and it will go
through and perform the import. Once finished you can see the status of the
job, and if necessary view the job history and details of what happened if
it fails for some reason.
Hope that helps
Keith
"Dave Tordoff" <david.tordoff@.companioncare.co.uk> wrote in message
news:167d01c46f3f$5c33fbf0$a601280a@.phx.gbl...
> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave

Importing multiple Access databases

Hi,
I have 30 remote sites that each day send an access
database of sales figures for that day, how can I automate
the process of importing these into an SQL database. I run
SQL server 2000.
As simple a solution as possible please as I am new to SQL
server.
Thanks DaveAssuming the access db's always have the same names, and are located in the
same way, you should be able to simply import the data into your SQL
database, by right clicking the database, selecting import, and then going
through the wizard which is reasonably self explanatory.
At the end of the wizard you will be asked if you want to run the import
now, save it, or schedule it. If you choose the schedule option you can then
select a time for that data to be imported, and how often it should be done,
and from there it should go through without a hitch. To test it, go to
Management, SQL Server Agent, Jobs, and you should see your DTS job in the
right hand side. Right click on it and tell it to start, and it will go
through and perform the import. Once finished you can see the status of the
job, and if necessary view the job history and details of what happened if
it fails for some reason.
Hope that helps
Keith
"Dave Tordoff" <david.tordoff@.companioncare.co.uk> wrote in message
news:167d01c46f3f$5c33fbf0$a601280a@.phx.gbl...
> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave

Importing multiple Access databases

Hi,
I have 30 remote sites that each day send an access
database of sales figures for that day, how can I automate
the process of importing these into an SQL database. I run
SQL server 2000.
As simple a solution as possible please as I am new to SQL
server.
Thanks DaveData Transform Services (DTS) would typically be used to import Access data
in to SQL server tables, and packages can be scheduled. There is coverage o
f DTS in BOL and there are several DTS books (Professional SQL Server 2000
DTS is good).
A way to get started is to use the DTS Import/Export Wizard (see tools/Data
Transformation Services) with one of the Access databases as the source and
the SQL Server database as the destination. Check the Save Package box (se
lect SQL Server for storage
) to save your package to SQL Server (you might want to uncheck the execute
imediate box) and use it as a template.

> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave
>|||Assuming the access db's always have the same names, and are located in the
same way, you should be able to simply import the data into your SQL
database, by right clicking the database, selecting import, and then going
through the wizard which is reasonably self explanatory.
At the end of the wizard you will be asked if you want to run the import
now, save it, or schedule it. If you choose the schedule option you can then
select a time for that data to be imported, and how often it should be done,
and from there it should go through without a hitch. To test it, go to
Management, SQL Server Agent, Jobs, and you should see your DTS job in the
right hand side. Right click on it and tell it to start, and it will go
through and perform the import. Once finished you can see the status of the
job, and if necessary view the job history and details of what happened if
it fails for some reason.
Hope that helps
Keith
"Dave Tordoff" <david.tordoff@.companioncare.co.uk> wrote in message
news:167d01c46f3f$5c33fbf0$a601280a@.phx.gbl...
> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Davesql

Importing from access

I have an access table with multiple data fields. When I add the fields into my crystal report all of the data fields show data except the date field.

I then imported the same table into a ms querry and the date field pulled the data in excel.

So there is data when I pull it into excel, but none when I pull it into crystal.

Any suggestions?Check the settings for --> File / Options / Fields / DateTimesql

importing excel column with multiple values separated by '/'

I would like to import two columns from an excel file into a sql server
table as an area code - time zone look up.
The area code column sometimes has multiple area codes in the area code
cell. eg. 207/208/209.
What is a good way to import those two columns so that 3 table rows are
created for each of those Excel rows that contain these multiple values
separated by the '/' character?
Thank you,
GregOn Wed, 1 Mar 2006 07:32:52 -0800, hazz wrote:

>I would like to import two columns from an excel file into a sql server
>table as an area code - time zone look up.
>The area code column sometimes has multiple area codes in the area code
>cell. eg. 207/208/209.
>What is a good way to import those two columns so that 3 table rows are
>created for each of those Excel rows that contain these multiple values
>separated by the '/' character?
Hi Greg,
Some useful techniques are disccussed at
http://www.sommarskog.se/arrays-in-sql.html
Hugo Kornelis, SQL Server MVP|||Thank you Hugo, I'll take a look !
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:spdc0210aq8daomldhm1lmbpviqf7hp4gi@.
4ax.com...
> On Wed, 1 Mar 2006 07:32:52 -0800, hazz wrote:
>
> Hi Greg,
> Some useful techniques are disccussed at
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Hugo Kornelis, SQL Server MVP

importing data issue

Hello,
We are moving our data from one sql2k to another. The initial db has all
of the tables in a primary filegroup. We have created multiple filegroups
for the new server from scripts. When we try to import the data from the old
sql to the new we get a bunch of FK and PK violations. Is this a way to shut
all of these off for the import and then turn them all back on or do we have
to do each table separately? Thanks in advance.
JohnThe PK violations implies that there is something wrong
with the import. You need to make sure the data added is
correct. Something like a different collation could cause
this.
FK - you just need to import the tables in the correct
order. ANother option is to remove all the FKs and add
them after the import.
Given that you seem to have problems wit hthe data I
would make sure the data is checked when the FKs are
added.

Importing data from Oracle

All,

I am working on a proof of concept, and basically trying to import multiple tables of data from an Oracle 8i server to SQL 2000.

The initial import is of all rows in about 7 tables. However, from then on, the business rule would be to import only changed and added rows, let's say every hour. The Oracle tables all have a last modified time on them, so that a query can determine whether the row is changed or added when compared to meta data stored in SQL 2000 if needed.

My questions:

1) What is the best way to do the first import. Currently, I'm thinking DTS?

2) What is the best way of doing the changed/updated row import? So far, I've tried heterogeneous queries, but I'm running into data type problems (meaning that SQL 2000 doesn't recognize some of the data types in the Oracle tables.

Any help here would be greatly appreciated. I've done work with SQL 2000 before, but not much with importing data.

Thanks in advance,

JeremyI'd say I'd just use a linked table, but I might be missing something in what you are trying to accomplish. Hope this helps.|||I would use DTS for that task, Microsoft OLE DB Provider for Oracle - Microsoft OLE DB Provider for SQL Server connections with Transform Data Task where you can run SQL query against Provider for Oracle (in Oracle SQL dialect). I think no problem with that. martin

Importing data from Excel file w/ multiple Excel sheets

Hi,
I want to import an Excel file that has many Excel sheets within it. I need
to get this into an SQL database using the DTS. I have set the DTS task to
import the Excel sheet successfully, but I ONLY get the 1st sheet imported.
What have I forgotten? Can someone assist in getting all sheets to be
imported. I am sure all of you knwo what I mean when I say "all sheets"
imported. However, just in case . . . these are the sheets that have the tabs
at the bottom of the Excel sheet.
I appreciate any assistance rendered.
Thanks.
In SQL2000, you can use the DTS Import Export Wizard. Once you selected the
appropriate file you want to load, it will show all the sheets in the
workbook on the "Select Source Tables and Views". You would be able to edit
the destination name for the tables where the data will be loaded.
If the package is already created you can create the connections and select
the sheet you want in the "Transform Data Task"
"4pcd" wrote:

> Hi,
> I want to import an Excel file that has many Excel sheets within it. I need
> to get this into an SQL database using the DTS. I have set the DTS task to
> import the Excel sheet successfully, but I ONLY get the 1st sheet imported.
> What have I forgotten? Can someone assist in getting all sheets to be
> imported. I am sure all of you knwo what I mean when I say "all sheets"
> imported. However, just in case . . . these are the sheets that have the tabs
> at the bottom of the Excel sheet.
> I appreciate any assistance rendered.
> Thanks.

Importing data from Excel file w/ multiple Excel sheets

Hi,
I want to import an Excel file that has many Excel sheets within it. I need
to get this into an SQL database using the DTS. I have set the DTS task to
import the Excel sheet successfully, but I ONLY get the 1st sheet imported.
What have I forgotten? Can someone assist in getting all sheets to be
imported. I am sure all of you knwo what I mean when I say "all sheets"
imported. However, just in case . . . these are the sheets that have the tabs
at the bottom of the Excel sheet.
I appreciate any assistance rendered.
Thanks.In SQL2000, you can use the DTS Import Export Wizard. Once you selected the
appropriate file you want to load, it will show all the sheets in the
workbook on the "Select Source Tables and Views". You would be able to edit
the destination name for the tables where the data will be loaded.
If the package is already created you can create the connections and select
the sheet you want in the "Transform Data Task"
"4pcd" wrote:
> Hi,
> I want to import an Excel file that has many Excel sheets within it. I need
> to get this into an SQL database using the DTS. I have set the DTS task to
> import the Excel sheet successfully, but I ONLY get the 1st sheet imported.
> What have I forgotten? Can someone assist in getting all sheets to be
> imported. I am sure all of you knwo what I mean when I say "all sheets"
> imported. However, just in case . . . these are the sheets that have the tabs
> at the bottom of the Excel sheet.
> I appreciate any assistance rendered.
> Thanks.