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.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 xml into sql - path to xml file???

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 MyDatabase

DECLARE @idoc int
DECLARE @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 @idoc

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

I'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?
Go to Top of Page

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

I'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
Go to Top of Page
   

- Advertisement -