# how to import xml from a website, parse it and save the results into mssql 2k8 r2

OK...

I have an external XML web site (http://rss.timegenie.com/forex2.xml) that I need to import into MSSQL Server 2008 R2.

1. is this even possible?

2. how would this be done?

Thanks,
John
###### Who is Participating?

Well, can do it all within a single SQL Script, but, need to use xp_cmdshell

You can turn it on at the start and turn it off at the end if needed. I have included turning on the option which allows xp_cmdshell.

Anyway, check out below, I have tried it and it works...

You just need to change the physical file path and server names (etc)

-- To allow changes to XP_CMDSHELL advanced options to be available.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
-- if needed, turn off the advanced options again

-- Now, lets create a script within the database then we can export that and use that script to grab the page and then load the XML

-- right, now export that script -- remember to check and change that path.

-- and then execute the script

-- or create a generic script file externally - just a text file edited in notepad (between the /* ... */ below) - then use that passing in the params
/*
var fso = new ActiveXObject('Scripting.FileSystemObject');
var args = WScript.Arguments;
var url = args(0);
var fileName = args(1);
var xmlhttp=new ActiveXObject("microsoft.xmlhttp");
xmlhttp.open("GET", url, false);
xmlhttp.send();
var data=xmlhttp.responsetext;
var file = fso.CreateTextFile(fileName, 2);
file.writeLine(data);
file.close();
*/

-- then your execute sql becomes even easier :

-- now we have the XML file locally, we can then set about importing it...

-- first we will need a staging table
if object_id('tempdb..#my_xml_staging','U') is not null drop table #my_xml_staging
create table #my_xml_staging(id int identity, xml_col xml)

-- now bulk load the file into a staging table
INSERT INTO #my_xml_staging(Xml_Col)
SELECT * FROM OPENROWSET(BULK 'c:\ee\forex2.xml',SINGLE_BLOB) AS x

-- now lets see what we have
select R.C.value('@code','char(3)') as code,
R.C.value('@description','varchar(100)') as description,
R.C.value('@rate','decimal(12,4)') as rate
from #my_xml_staging X
cross apply X.xml_col.nodes('/forex/data/currency') as R(C)

-- and we can add the "insert my_real_table (code, description, rate)" as the first line above the select to insert into the real home.

-- If needed, turn off xp_cmdshell by using the sp_configure and setting to 0 instead of 1 as per above

0

Author Commented:
I tried the example found at http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_26631018.html with no luck, it only returns an empty rowset.
0

SD-WAN SimplifiedCommented:
Here are some good examples
http://stackoverflow.com/questions/3628846/insert-xml-into-sql-server-2008-database

Get the insert working outside of your site, them get a simple insert working, and only then put it all together. Too many things can be a problem when doing it all at once.
0

Author Commented:
getting the following error:
Msg 4860, Level 16, State 1, Line 51
Cannot bulk load. The file "c:\ee\forex2.xml" does not exist.

I created the ee directory. The script works only if I manually download the xml file myself.
0

Author Commented:
Disregard :), forgot to create the js script :D.

AWESOME SCRIPT btw.
0

Author Commented:
Awesome Script
0

Thanks, I have been using it for a while now, and (so far) it stands the test of time...

Cheers,
Mark
0

Senior Technical architecture (Data)Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.