SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 xml into sql - path to xml file???
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

jimmyfingers
Starting Member

2 Posts

Posted - 09/21/2006 :  11:30:13  Show Profile
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
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 09/21/2006 :  14:27:13  Show Profile
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 - 09/21/2006 :  15:09:58  Show Profile
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
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000