XML file to SQL Server table - best way?

Background:
We have some systems that perform tests on piece-parts and modules to see if
they work and meet spec. The testers (boxes not people) record data during
the testing process and dump that data into an XML file stored local to the
tester.

We'd like to get the results out of the XML file and get them into a table
in SQL server. As far as I know the software on the testers does not have
the ability to directly transfer the data into SQL server.

As I look around for options, I think I see several but have no clue of the
costs or benefits to each. Here's some of what I've run across.

Option 1: Create our own system to start up the tester, watch for the
generation of the XML file and have that system push the data into SQL server
via something like ODBC (ie our custom system parses the XML file into an
'Insert...' statement and handles the data transfer. This has the advantage
that we can keep the file local to the user and get the data to the server
without exposing the user to the server. We can also do post-upload
processing and move the file into some kind of 'done' directory for long term
storage.

Option 2: Write code on the SQL server to pull the data in. I'm not clear
on what tools are available for this so pointers and tips are appreciated.
I'm assuming we'd need to move the XML file from the initial PC to a network
location the SQL server can 'see' so it can access and parse the file. We'd
need to provide the user with a way to launch the code - what types of
options are available?

Option 3: Could we take option 2 and convert that into a stored procedure
and have that executed automagically by the user? Can SQL server watch
network directories and just parse anything that shows up in the directory or
would this take other code to implement?

Option yous: If you had this problem, what would you do?

I am NOT looking for complete examples or code, just some pointers to
options and ideas. I'm new to the SQL server world and the amount of
information available is pretty large. As I look around for information on
XML, most of it seems tied to using XML on the web which is not what we're
doing - we just want to take a text file and stuff the data into something we
can ask questions to later...

Thanks for any tips.

Doug

.

Relevant Pages

  • Re: 1.5 MB input file generates 940MB of data in BizTalkMsgBoxDb
    ... I ran the following interchange on an XML file I worked on a while back: ... Initial Size of Msgbox DB Sql Data File -> 35 MB ... Size of files read from Sql Server Management Studio (Database properties ... Look at the Sql Server Agent jobs set up by Biztalk. ...
    (microsoft.public.biztalk.general)
  • Re: extracting results of XML stored proc to file
    ... extended Stored Proc or something, but it'd be a lot of effort.) ... Agent job and execute it from within SQL Server. ... 'Assign the output stream. ... extract them from the results into an XML file ...
    (microsoft.public.sqlserver.xml)
  • Re: Securing a web DB
    ... >name, uid, password) from an XML file and establish connection to the DB. ... >We are planning to host the site with as ISP. ... >integrated login) for the database. ... >get to know the SQL server password. ...
    (comp.security.misc)
  • Re: How to use content of file for xml input?
    ... I used the bulk load mechanism. ... With these mechasim i'm able to read the file into our SQL Server. ... So thanks for your interest in my problem and best regards, ... how a external xml file can be prepared to use ...
    (microsoft.public.sqlserver.xml)
  • Re: XML file to SQL Server table - best way?
    ... We'd like to get the results out of the XML file and get them into a table ... As far as I know the software on the testers does not have ... the ability to directly transfer the data into SQL server. ... regularly scheduled SQL Agent job that kicks off a SQLCLR SP or T-SQL SP. ...
    (microsoft.public.sqlserver.xml)