How to load XML from filename and path
-
Hi,
I have inherited a database that has a table called "Resources" that contains a field called "FilePath". FilePath is a VarChar(100) and contains the server path and filename of an XML file on the server hard disk.
I need to join the data in the XML with rows in the "Resources" table (and other tables).
Is it possible to get a Stored Procedure to load the XML file into a temporary table?
Is loading it into a table the best way to do it - or can I join directly to the XML file somehow?
What is the best approach? (I know nothing about XML in SQL Server yet)
Thanks in advance,
Chiz.
-
If your data access layer is built on the .NET technologies you can load the XML file into a strongly typed dataset, then load your other data in from your database with a second query. A strongly typed dataset will be able to enforce the join through PK-FK relationships that you define when you initially type the dataset.
-
Chance,
Thanks for you reply. I'm primarily a .NET programmer (and your right it could be done the way you're suggesting) but I'm still really interested to know how (if) SQL Server can load an XML file from hard disk into a (temporary?) table.
I wonder....
I'd have thought that DTS could do this. I don't know a lot about DTS but I'd imagine this is the sort of thing it should be able to do. I wonder if its possible to call a DTS 'function'(?) from within a stored proc (and be able to pass the 'FilePath' field as a parameter to the DTS 'function'?).
Help - now I'm guessing. Any help appreciated!
Chiz.
-
Hi again,
I thought I'd dump my research here to help any poor souls stuck with the same problem as me.
I looks like it can be done but gets a bit horrible when the XML is file is larger than 8000 chars (mine is!) and contains lines that are longer than 255 chars (mine is!):
Have a look here. This is a long article but scroll about 3/4 of the way down and you'll find a Q & A section that asks
Q: Is there any way to use an external XML document file with OPENXML? Essentially, what I would like to do is import (insert) data into the database table using OPENXML; but instead of specifying the XML string in the T-SQL code, I would like to pass an XML file to OPENXML?
A
PENXML T-SQL function uses the XML document handle created using the sp_xml_preparedocument - that accepts (char, nchar, varchar, nvarchar, text, or ntext) - and can only work on string. It does not accept stream or physical file name. The maximum characters that you can pass while working in query analyzer is 8000 characters (varchar limit). In summary, OPENXML (yet) does not accept XML input from a file/stream.
http://www.perfectxml.com/articles/xml/importxmlsql.asp
Drop it in the too hard basket - I'm going to do it another way....
Good luck!!!
If anyone finds an easier way let me know by posting here.
Thanks,
Chiz.
-
Maybe a bit off track, but we're looking at how to get a series of XML files to update information in our SQL database. Came across a thing called DiffGrams. Here's a few links I dug up during my hunt.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/dotnet_8704.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/dotnet_990h.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconDiffGrams.asp
--------------------
Colt 45 - the original point and click interface
-
Ian, a simple example is at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_6bos.asp
Doesn't require diffgrams - very simple to just insert to a temporary table. I use it that way myself.
Cheers
Stephen
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply