Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jimmyfingers
Starting Member
2 Posts |
Posted - 2006-09-21 : 11:30:13
|
Hi,I've created a procedure for importing xml into sql but have a slight problem. Its fine when I list the xml in with the sql code, but I really need to show the path to an xml document (3mb) on my hard drive. Here is my code:use MyDatabaseDECLARE @idoc intDECLARE @xmldoc nvarchar(4000)---Define the xml document (***but rather than use this code below where each item is listed I want @xmldoc to be assigned the path to my external xml document***)SET @xmldoc = '<root> <Product listPrice="2.40"> <n>OFFRAY RIBBON</n> <m>Offray</m> <b1>0079856205761</b1> <b2></b2> </Product> <Product listPrice="2.62"> <n>61CM YKK VISLON ZIP</n> <m>YKK</m> <b1>5030121021498</b1> <b2></b2> </Product></root>'---load and parse the xml document---EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc---Perform and insert the new data---INSERT into GandB(ListPrice,ProductCode,ProductName,ProductManu,Scan1,Scan2)SELECT * FROM OPENXML (@idoc, '/root/Product',3) WITH ( listPrice varchar(20), gb varchar(20), n varchar(50), m varchar(50), b1 varchar(20), b2 varchar(20))---Look at table to make sure transfer worked---select * from GandB---remove xml document from memory---EXEC sp_xml_removedocument @idocCan anyone please help?Many thanks |
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-21 : 14:27:13
|
The simple answer is - you can't :-(But as ever there are ways and means. One really nice solution is here:http://www.builderau.com.au/architect/webservices/soa/Reading_an_XML_file_from_SQL_Server/0,339024590,339174685,00.htmI'd say you'd be best served by doing it with a client application or DTS - do you really want your SQL Server directly reading all those external files? |
|
|
jimmyfingers
Starting Member
2 Posts |
Posted - 2006-09-21 : 15:09:58
|
quote: Originally posted by snSQL The simple answer is - you can't :-(But as ever there are ways and means. One really nice solution is here:http://www.builderau.com.au/architect/webservices/soa/Reading_an_XML_file_from_SQL_Server/0,339024590,339174685,00.htmI'd say you'd be best served by doing it with a client application or DTS - do you really want your SQL Server directly reading all those external files?
Yes I'm creating a C# app to do all the work for me.Many thanks,James |
|
|
|
|
|
|
|