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 Tables with Identity properties (uisng Import wizard)

I am using the Import wizard to import a SQL2000 database to SQL2005 and noticed 2 problems:

1. all tables and views were selected; the tables were imported correctly but the views were created as tables, ignoring the "Create view" syntax. The SQL generated contains "Create table" syntax instead of "Create View".

2. when a table contained a column with an "identity" property, the data was successfully imported, but the values for the "identity" column were not preserved, instead they were resquenced from 1 with an increment of 1 (the default values for an identity). When I opened the "Edit" (under "Mapping"), "enable identity insert" was not checked.

A further note: I created all tables in the SQL2005 database before running the Import.

1 - How would you represent a selection of tables, calculations and some view DDL in a text file? SSIS is about data movement, not object migration. This is what happens and is entirely correct. You often use views to expose a limited set of data, perhaps including calculations or such like, and you would then use SSIS to take that data and move it to a new system.

2 The wizard does not expose all options otherwise if would not be a very good wizard, and this is just one of those assumptions. It is in part no doubt based on the difficult of determining an identity column through the OLE-DB provider specifications, and it is the same old issue you had in DTS.

If you want to migrate SQL 2000 to SQL 2005 I suggest you have a look in Books Online it covers several options. A backup and restore is perhaps the easiest method I have found to take a SQL 2000 DB to a new SQL 2005 server.

|||

Darren:

Your reply was typical of someone who believes that Microsoft is incapable of making an error, in this case a grevious error since it MODIFIES data defined as an IDENTITY, instead of COPYING it. The SSIS wizard clearly needs work; it is unacceptable in its present form. (If you need an example of how a data migration wizard SHOULD work, review the one used by SQL SERVER 7 to migrate data from SQL SERVER 6.5).

I have manged to MOVE all of the data from SQL2000 to SQL2005 using a 3-step process: 1) run a SQL script that creates the table structures complete with all constraints. (This is ANOTHER area where the wizard is incomplete; it creates ONLY the table structure, minus all constraints). 2). Use the wizard to copy all tables that DO NOT CONTAIN identity columns. 3). Use the wizard to copy ONLY tables that DO CONTAIN identity columns, but EDIT each table entry checking ON "ENABLE IDENTITY INSERT".Using the 3-step process, I was able to bring ALL of the data over in one session, not following your suggestion of bringing the data across in a piece-meal process, never knowing if I had copied ALL of the data).

So, the task CAN BE DONE, if one is able to understand the failings of the wizard, and work around them appropriately.

|||

Thanks for adding your experiences.

I don't see that I suggested a piece-meal approach, I just stated there is an issue, and whilst not necessary to the answer I do think is big problem, I have hit it myself, and even logged a PPS call for the same issue in DTS. Have you feedback at all?

I recall the migration wizard and yes it was ideal for the job, but personally I am happy to accept the limitations covered with SSIS, because SSIS is really an ETL tool, not a migration tool. A tool designed specifically for SQL to SQL migrations would not be an effective ETL tool. Just because the wizard can be used does not mean it is ideal or even the best tool. There are much better tools for generating scripts and transferring structures for example, but the SSIS limitation of not creating constraints sounds like a bonus. Having constraints in place when loading data increases the complexity as you need to load related tables in order, and tables with circular references just cannot be loaded. Performance will also be much faster will with them as well. I would apply constraints after the load. That would all be me defending MS of course, but I try and think what the role for the product is too.

A non-piecemeal method, in just 1 step, which I did suggest was a backup and restore. It suffers none of the issues you covered, and is in many ways faster and more reliable than anything else. It may of course introduce additional implications, but then we have choices.

importing tables from Acess to SQL

Hello
I am trying to import tables with data from Access to SQL.
The problem occures when the Primary keys and identity are not being saved
and i need to do it manually.
There is a way to import this tables with all the connection regulations
like Primary key will be saved ?
thanks
GalWe have found that the safest way to move from Access to SQL Server is to
convert the Access file to 2002, then use the Upsize Wizard to move the
schema only. Finally we use the Data Transformation Services of SQL Server
to import the data from Access.
Make sure that all the tables have a key index.
Ragnar
"'" <@.discussions.microsoft.com> wrote in message
news:B93E10E0-1C9B-4C46-83AA-32F90A07E96D@.microsoft.com...
> Hello
> I am trying to import tables with data from Access to SQL.
> The problem occures when the Primary keys and identity are not being saved
> and i need to do it manually.
> There is a way to import this tables with all the connection regulations
> like Primary key will be saved ?
> thanks
> Gal

importing tables from Acess to SQL

Hello
I am trying to import tables with data from Access to SQL.
The problem occures when the Primary keys and identity are not being saved
and i need to do it manually.
There is a way to import this tables with all the connection regulations
like Primary key will be saved ?
thanks
Gal
We have found that the safest way to move from Access to SQL Server is to
convert the Access file to 2002, then use the Upsize Wizard to move the
schema only. Finally we use the Data Transformation Services of SQL Server
to import the data from Access.
Make sure that all the tables have a key index.
Ragnar
"?" <@.discussions.microsoft.com> wrote in message
news:B93E10E0-1C9B-4C46-83AA-32F90A07E96D@.microsoft.com...
> Hello
> I am trying to import tables with data from Access to SQL.
> The problem occures when the Primary keys and identity are not being saved
> and i need to do it manually.
> There is a way to import this tables with all the connection regulations
> like Primary key will be saved ?
> thanks
> Gal