January 15, 2007 at 8:17 am
Hi all,
I have an XML feed that I need to connect to giving updated Exchange Rate data. The link is:-
http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml
A simple question, at least I thought is was before I started Googling, how do I get this data into my SQL Server 2005 Database?
Ideally I would like to have this XML file as a Linked Server using SQLXMLOLEDB maybe? But I have no idea what to put in the Product Name, Provider String etc fields in the New Linked server Dialogue or how I would use OPENQUERY to query the data.
It seems that I can produce a Web Service from SQL Server 2005 but every obstacle is put in your way to consume one.
Is this possible?
Cheers Tony
January 16, 2007 at 10:43 am
There is a TON of data in Books Online about XML under the heading "XML". The very first thing I thought of when I saw your post was mapping the data to a SQL Server XML datatype and processing from there.
There's also sp_xml_preparedocument and the OpenXML command which might help you out. There's also a subject called XML Bulk Load which might help.
January 17, 2007 at 2:28 am
Cheers for your response Brandie,
Have already explored these avenues. After several hours of effort, eventually settled on the following solution. Note, I had to WGet the file to a local directory to work on it as I have not yet found a way to load the file directly from the web site. (I have disabled command shell on the server for security.) Come on Microsoft, what is the point of XML if you cannot get SQL Server to do a simple HTTP GET?
Declare @idoc int
Declare @doc varchar(4000)
--http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml
SET
@doc =(
SELECT
* FROM OPENROWSET(
BULK 'D:\WWW\releasing\Data\eurofxref-daily.xml',
SINGLE_BLOB
)
AS x
)
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref"/>'
TRUNCATE
TABLE tblEuroFXRates_RAW
INSERT
INTO tblEuroFXRates_RAW
SELECT
*
FROM
OPENXML (@idoc, '/gesmes:Envelope',1)
exec
sp_xml_removedocument @idoc
TRUNCATE
TABLE dbo.tblEuroFXRates
INSERT
INTO dbo.tblEuroFXRates
SELECT
a.text currency, b.text rate
FROM
(
SELECT b.parentid, a.text, b.localname FROM tblEuroFXRates_RAW a
LEFT
OUTER JOIN tblEuroFXRates_RAW b
ON
a.parentid = b.id
WHERE
a.nodetype = 3 AND b.nodetype = 2
AND
b.localname = 'currency') a
LEFT
OUTER JOIN
(
SELECT b.parentid, a.text, b.localname FROM tblEuroFXRates_RAW a
LEFT
OUTER JOIN tblEuroFXRates_RAW b
ON
a.parentid = b.id
WHERE
a.nodetype = 3 AND b.nodetype = 2
AND
b.localname = 'rate') b
ON
a.parentid = b.parentid
January 17, 2007 at 4:52 am
Am I reading your code right? You actually had to join the document to itself to get it in SQL Server properly?
January 17, 2007 at 4:53 am
I almost forgot...
You should be able to use a Script task in an SSIS package to move the XML file over to your server from the website.
January 17, 2007 at 7:13 am
The table join was just a neat way to pivot the table. If you look at the output from OPENXML you will see it is just a tag order linked list of tags and values. I could not use WITH parameters as OPENXML does not seem to understand multi-dimensional cubes. (This being a time series cube.)
January 17, 2007 at 7:23 am
Not happy with using SSIS packages, too much admin. Why bother anyway when the good old guys from the Linux world provide such excellent GNU utilities like WGet? (Open source rules!) In any case, which would you choose, a one line command which does the job brilliantly or a week messing around in SSIS with no guarantee you can do it at the end of it all?
January 18, 2007 at 3:10 am
I would still go with SSIS instead of spending some time every week on download and upload!
January 18, 2007 at 3:25 am
I use a batch file that runs as a Sheduled Task ... remember the good old days of DOS? I used to pride myself on getting every application onto a single 3.5" floppy, now we are using Terrabyte arrays.
I think that this thread is wandering somewhat.
January 18, 2007 at 4:49 am
I think Microsoft stuck the XML stuff in SQL 2k5 because so many people were using it, not because they wanted to. Yes, there's a lot of nifty stuff, but hearing you talk about what you had to do to get it to work, makes me wonder if it isn't an after-thought.
I work with one of the guys who helped write the WROX SSIS book and was going to ask him if there was an easier way to do what you want to do, but if you're happy with your solution and don't want to use SSIS, then I won't. But for those of us who don't have access to opensource stuff, we're still going to have to use the SSIS tools, probably.
January 18, 2007 at 6:26 am
Good on you Brandie,
Please dont let me stop you publishing good stuff to the community just cos I am a stick-in-the-mud. If SSIS has the magic bullet then I for one would be interested is a page reference. I have several WROX books at home gathering dust and good intentions.
Cheers (hate not having the last word) Tony
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply