| Author |
Topic |
|
VelocityMarty
Starting Member
6 Posts |
 Posted - 2002-06-24 : 16:50:43
|
| Hello All,I've have spent the last 2 days searching for a simple way to import a simple xml file into sql server 2000 and have not been successful. So I'm hear asking the experts. :)I have an xml file that will be uploaded to a directory on the sql server daily and I would like to create a dts package or stored procedure that I can schedule to execute and import the contents of that xml file. I have successfully used a test procedure that does this with the contents of the xml inside the procedure but I have not been able to find or figure out the syntax to have it grab the xml file off the drive.Thank you all very much in advance. I have learned a lot from these forums over the years!-Marty |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
VelocityMarty
Starting Member
6 Posts |
Posted - 2002-06-25 : 09:24:42
|
| Thank you but I've been through every article on that site and still haven't found the syntax to use in a stored procedure to load an xml file. I also haven't found enough documentation on the xml bulk load utility to help.Please help...Thanks,-Marty |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
VelocityMarty
Starting Member
6 Posts |
Posted - 2002-06-25 : 09:53:17
|
| Thank you again. I did read that msdn article but I'm rereading it now just in case I missed something. The problem I found with that article is that it requires you to do some visual basic development. I don't have visual basic nor know how to use it.That book looks great, I'll see if I can find it locally as I'm in a time crunch.Strange, I figured the task of importing data from an xml file would be the most common xml/sql task and readily documented. Hell, Access has a wizard for it. Reminds me of when I was working with Oracle and found simple ways to do what I needed with Sql Server but not Oracle.Again, thank you very much. I appreciate your help. Any other ideas?-Marty |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-25 : 10:10:19
|
| The code that is listed in the MSDN link can be written in VBScript and put into a SQL job or DTS ActiveX task/step. You don't need any additional software to do that except to install the SQLXML package. It's really not that hard to do, and I'd recommend spending some time on it because it is the most powerful method available.I'm assuming that you've looked at OPENXML too; I've found it hard to work with but if the XML documents are not that large (8000 bytes or less) it's pretty easy to load them into SQL variables and pass them to the XML system procedures and parse them using OPENXML. |
|
|
|
VelocityMarty
Starting Member
6 Posts |
Posted - 2002-06-25 : 11:03:33
|
| I'm going to keep bothering you until you tell me to bug off. ;)I'm trying to run a sample/test procedure and here's what i've got.The stored procedure is:CREATE PROCEDURE sp_Insert_Books_Openxml @strXML ntextAS DECLARE @iDoc int EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML INSERT INTO tblBooks (BookTitle, Publisher, DateOfPurchase) (SELECT * FROM OpenXML(@iDoc, '/Books/Book', 2) WITH (Title VARCHAR(100), Publisher VARCHAR(100), DateOfPurchase DateTime) ) EXECUTE sp_xml_removedocument @iDocGOThis works:EXEC sp_Insert_Books_Openxml @strXML = '<Books> <Book> <Title>XML Application Development with MSXML 4.0</Title> <Publisher>Wrox Press</Publisher> <DateOfPurchase>2/1/2002</DateOfPurchase> </Book> <Book> <Title>Professional SQL Server 2000 XML</Title> <Publisher>Wrox Press</Publisher> <DateOfPurchase>9/10/2001</DateOfPurchase> </Book> <Book> <Title>Professional XML for .NET Developers</Title> <Publisher>Wrox Press</Publisher> <DateOfPurchase>12/20/2001</DateOfPurchase> </Book></Books>'This fails: EXEC sp_Insert_Books_Openxml @strXML = 'C:\books.xml'With the following error:Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 6XML parsing error: Invalid at the top level of the document.The content of c:\books.xml is as follows:<Books> <Book> <Title>XML Application Development with MSXML 4.0</Title> <Publisher>Wrox Press</Publisher> <DateOfPurchase>2/1/2002</DateOfPurchase> </Book> <Book> <Title>Professional SQL Server 2000 XML</Title> <Publisher>Wrox Press</Publisher> <DateOfPurchase>9/10/2001</DateOfPurchase> </Book> <Book> <Title>Professional XML for .NET Developers</Title> <Publisher>Wrox Press</Publisher> <DateOfPurchase>12/20/2001</DateOfPurchase> </Book></Books>What am I doing wrong?THANK YOU! :)-Marty |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-25 : 12:01:16
|
| You can't pass it a file name because it won't open the file. It treats the value you pass as the actual XML. You'd have to open the file through another means and put the contents into the variable.Do a thorough search of SQL Team for XML, both regular and forum search, there are a number of articles that cover these kinds of operations. Also look very closely in Books Online, there is an example somewhere on loading an XML file. Don't forget to play with the VB code also, if you know someone in the office who's dabbled with it they can help you convert the code, it's not that hard.Spend some time with it before you post again, maybe a day or two, you'll learn it faster than if you take one baby step, post a question, another baby step, post a question, etc. |
|
|
|
|
How to easily Import XML