Accessing URL

Hi!

I have two assets: a URL that points to an XML file, and a stored procedure that can accept this file as a text variable and store it in a SQL 2005 table.

create procedure [dbo].[insertObjects]

@.availabilityXml text

as

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE Objects

INSERTObjects

SELECT *

FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)
WITH Objects

Now, I need to find a solution to combine the URL with the proc.Does anyone have any suggestions on how I can pass my URL as a text variable to the procedure?SSIS, vb-script, etc. are welcome!

Thank you!

I believe these are your choices:

a) Store the data into a table through the URL and read it, this approach is mentioned here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/dff99404-a002-48ee-910e-f37f013d946d.htm (Bulk Importing and Exporting XML Documents) in the SQL Server BOL.

b) Use OPENXML for loading data into a variable. This approach is given here: http://www.perfectxml.com/articles/xml/importxmlsql.asp#openxml

Hope this helps.

Thanks

Waseem

|||Thanks for the reply! I used the a approach, and it's working!|||Which approach did you use?|||OPENXML (b)