XML Data Processing

[_(d)IEGO]

Hello everyone. I need help regarding the following:

Given the following table:
CREATE TABLE T1 (C1 nvarchar(10), C2 money)

INSERT INTO T1 VALUES ('A',1)
INSERT INTO T1 VALUES ('B',2)
INSERT INTO T1 VALUES ('C',3)

let's say that i have this table in a local server and i want to upload
it to a remote server and in the remote server upload it to a database
that contains the same table.

the uploading part can be done by another application in the remote
server, but i want i need is a way to transfer the data at the fastest
possible way.

what steps do i need to follow?

tia,

Rey Guerrero

[John Bell]

Hi Rey

The answer will depend on if you want to keep the data synchronised between
the two servers or not? If not then you can have an upload working on the
remote server in parallel to yours. If they have to be syncronised then you
may want to look at replication. It may also depend on the accessibility of
the remote server and the connection between them.

John

"_(d)IEGO" <re**********@hotmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Hello everyone. I need help regarding the following:

Given the following table:
CREATE TABLE T1 (C1 nvarchar(10), C2 money)

INSERT INTO T1 VALUES ('A',1)
INSERT INTO T1 VALUES ('B',2)
INSERT INTO T1 VALUES ('C',3)

let's say that i have this table in a local server and i want to upload
it to a remote server and in the remote server upload it to a database
that contains the same table.

the uploading part can be done by another application in the remote
server, but i want i need is a way to transfer the data at the fastest
possible way.

what steps do i need to follow?

tia,

Rey Guerrero

[_(d)IEGO]

John,

Thank you for your response.

this table contains transaction records so each transaction will be
stored locally and at a pre-defined interval newly added transactions
will be uploaded to the remote server. so it is not that important that
they are synchronized all the time. your first suggestion seems to fit
what I need. How can I achieve this? Do I have to perform data
trasformation before sending the file? If so, how is it done and how do
I process it at the remote side?

tia,

Rey Guerrero

[John Bell]

Hi Rey

Which suggestion was that?

Check out http://www.perfectxml.com/articles/XML/ImportXMLSQL.asp on ways to
import XML data into a database.

You can either use the same process on both servers (the source file would
have to be available to both machines) or create a job that refreshes the
remote server from the uploaded data in your remote database from the table
in the local database. This could be a DTS task or you can set up
replication to do it.

John

"_(d)IEGO" <re**********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

John,

Thank you for your response.

this table contains transaction records so each transaction will be
stored locally and at a pre-defined interval newly added transactions
will be uploaded to the remote server. so it is not that important that
they are synchronized all the time. your first suggestion seems to fit
what I need. How can I achieve this? Do I have to perform data
trasformation before sending the file? If so, how is it done and how do
I process it at the remote side?

tia,

Rey Guerrero

[_(d)IEGO]

John,

Thanks for the link.

Please tell me if I am correct. First I have to export the data to XML
on the local server then do an FTP to the remote server then process
the uploaded XML file there. If so, will this approach be fast if I set
the upload at a 30-minute interval and let's say that there is an
average of 20 transactions per minute?

Rey Guerrero

[John Bell]

Hi Rey

You have not said enough about how the systems are connected and how the
data is stored to give a specific answer.

If you receive an XML file to process you can copy (or have the same file
sent) to the remote server which can upload it using the same application
that is on your local server. The is no need to export the data from the
local database unless there is some processing that means that it would
either require the remote system to do too much work, or the remote system
does not have all the information to process the file correctly.

If you do need to process the data locally and then send the modified data
to your remote system, then your time scales may indicate that replication
would be a suitable option. As the local and remote tables are the same
structure there is no need to use XML as an intermediate format, you may
want to look at creating a DTS (Data Transformation Services) task to
export/import the data. If you only have FTP access to the remote system
then you will need a scheduled DTS task on each system a task on the local
system to export/ftp the data and a task on the remote system to check for
new data files and load them up. Check out books online and
http://www.sqldts.com/default.aspx for more information on DTS.

There are other options such as writing a web service and SQL 2005 makes
this easier to host, but that may be more than you actually need.

John

"_(d)IEGO" <re**********@hotmail.com> wrote in message
news:11********************@g43g2000cwa.googlegrou ps.com...

John,

Thanks for the link.

Please tell me if I am correct. First I have to export the data to XML
on the local server then do an FTP to the remote server then process
the uploaded XML file there. If so, will this approach be fast if I set
the upload at a 30-minute interval and let's say that there is an
average of 20 transactions per minute?

Rey Guerrero

[_(d)IEGO]

John,

Data from the local server has the same structure as the data in the
remote server except that the data in the remote server will have an
extra column for LOCATION_CODE.

Yes, FTP is the access to the remote system.

Thanks.

Rey

[John Bell]

HI Rey

It sounds like DTS and have export/import processes is probably the way to
go then. You can either add the extra location code on export or give it a
default so it will have the value when you import.
John

"_(d)IEGO" <re**********@hotmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

John,

Data from the local server has the same structure as the data in the
remote server except that the data in the remote server will have an
extra column for LOCATION_CODE.

Yes, FTP is the access to the remote system.

Thanks.

Rey

[_(d)IEGO]

John,

Thanks for your patience. But I still have some questions :)

What data format do you suggest I should export the data to? And is DTS
the only way to achieve what I need?

Rey

[John Bell]

Hi

DTS can export into to multiple formats although a text file will probably
be the best for this instance. There are other tools such as BCP, but within
a DTS package you can produce workflow, error handling and conditional
handling within the same package. The packages themselves can be developed
and tested on one server and then transferred to their destination.

http://www.sqldts.com/default.aspx is a great site for resources and the
examples it has will save you time when developing your own solution.

John

"_(d)IEGO" <re**********@hotmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

John,

Thanks for your patience. But I still have some questions :)

What data format do you suggest I should export the data to? And is DTS
the only way to achieve what I need?

Rey

[_(d)IEGO]

John,

I will try the suggested solution.

Thanks.

Rey