Loading XML file - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Loading XML file

Author

Topic

yyy
Starting Member

5 Posts

Posted - 2011-11-24 : 05:03:57

Hi,

I want to load an XML file from the internet to my computer,
or just temporary to my stored procedure,
through a stored procedure.

How can I load the file?

Thanks,
Yael

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-11-28 : 04:13:02

Let me know what all have you tried for this first.

Thanks,
Jim

rams

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 04:36:19

quote:
Originally posted by yyy

Hi,

I want to load an XML file from the internet to my computer,
or just temporary to my stored procedure,
through a stored procedure.

How can I load the file?

Thanks,
Yael

you can use OPENROWSET bulk

see

http://msdn.microsoft.com/en-us/library/ms191184.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yyy
Starting Member

5 Posts

Posted - 2011-11-28 : 05:49:41

Hi,

I used the OPENROWSET after I already had the XML file in my machine:

SET @xmlDoc = (SELECT * FROM OPENROWSET (BULK '\\...\indices.xml', SINGLE_BLOB) AS xmlData)

But, I need one step before: I need a way to load the XML file from the internet to my machine, and the following didn't work:

SET @xmlDoc = (SELECT * FROM OPENROWSET (BULK 'http://www1.cbs.gov.il/reader/pirsum_madad/indices.xml', SINGLE_BLOB) AS xmlData)

Thanks ahead,
Yael

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 07:18:26

wheres the XML currently? is there a ftp site through which you can get it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yyy
Starting Member

5 Posts

Posted - 2011-11-28 : 07:43:31

Hi,

The XML is in the following address:
http://www1.cbs.gov.il/reader/pirsum_madad/indices.xml

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 07:46:42

do you've a webservice method through which you can call and get this xml to db?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yyy
Starting Member

5 Posts

Posted - 2011-11-28 : 08:08:19

No... Is there a way to do it directly throgh the SQL stored procedure?
I don't need to save the file in my machine, just to use its data in the SQL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 08:19:00

try this

http://www.mandsconsulting.com/request-xml-from-url-in-ssis-c-script-task

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yyy
Starting Member

5 Posts

Posted - 2011-11-28 : 08:41:29

First, Thanks a lot for your help!!

I see that this explains how to load the XML in C#, but I need this in SQL
(I need to do a task that will be done automatically once per day, so I don't think that I can do it in C#).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 08:43:01

quote:
Originally posted by yyy

First, Thanks a lot for your help!!

I see that this explains how to load the XML in C#, but I need this in SQL
(I need to do a task that will be done automatically once per day, so I don't think that I can do it in C#).

nope. that C # code can be called inside SSIS package which can be called via SQL job for automatic execution

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources