Re: XML file to SQL Server table - best way?


"Doug_F" <DougF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6324CDEB-8110-4445-989F-BABEFDEDF550@xxxxxxxxxxxxxxxx
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 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?

You might look into SQLCLR SPs, SQL Agent, and the OPENROWSET rowset
provider.

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?

See above.

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

It depends on some factors, but I could go with either an external app with
a file-watcher that loads the data in when the file is updated, or a
regularly scheduled SQL Agent job that kicks off a SQLCLR SP or T-SQL SP.
You can also use SQLCLR to easily grab the XML from an internal web server
or FTP, like an IIS, which sometimes makes more sense than creating file
shares.

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...

Once you grab the XML data, it's a relatively simple matter to store, query,
shred, and manipulate it within SQL Server using the XML data type methods.


.

Relevant Pages
Loading