A dirty question about XML

Question

A dirty question about XML

keymoo

SSCrazy

Points: 2975
More actions
January 11, 2011 at 8:47 am

#232850

I have some data in SQL Server I'd like to join with an XML document on the internet. The document is here http://rates.fxcm.com/RatesXML
I will be calling the stored procedure that contains the query from a C# application. It feels a bit dirty to try and read an XML document from a database server, but is it possible? How would I query the document above - maybe put it into a temp table or something?
I can get the data into a C# DataTable on the front-end - better to do it there?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

keymoo (1/11/2011)
I have some data in SQL Server I'd like to join with an XML document on the internet. The document is here http://rates.fxcm.com/RatesXML
I will be calling the stored procedure that contains the query from a C# application. It feels a bit dirty to try and read an XML document from a database server, but is it possible? How would I query the document above - maybe put it into a temp table or something?
I can get the data into a C# DataTable on the front-end - better to do it there?

This recent article[/url] that I wrote should be able to help you out... it pretty much does exactly what you want to do. Once you have it loaded in to your temp/staging table, just join to your sql tables, and return what you need to your application.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

keymoo SSCrazy Points: 2975 More actions · Answer 2

Thanks Wayne, the problematic part is getting the XML data from the internet. I tried this query and it doesn't work:

select * from openrowset (bulk N'http://rates.fxcm.com/RatesXML', SINGLE_BLOB) rs

I get this error:

Msg 4861, Level 16, State 1, Line 1

Cannot bulk load because the file "http://rates.fxcm.com/RatesXML" could not be opened. Operating system error code 123(failed to retrieve text for this error. Reason: 15105).

If you simply click on the XML link in a browser the XML Data looks like this:

<?xml version="1.0" encoding="UTF-8"?>

<Rates>

<Rate Symbol="EURUSD">

<Bid>1.29963</Bid>

<Ask>1.29988</Ask>

<High>1.3018</High>

<Low>1.29633</Low>

<Direction>0</Direction>

<Last>07:43:33</Last>

</Rate>

<Rate Symbol="USDJPY">

<Bid>83.048</Bid>

<Ask>83.068</Ask>

<High>83.427</High>

<Low>83.026</Low>

<Direction>1</Direction>

<Last>07:43:27</Last>

</Rate>

</Rates>

If I download the XML file to C:\Temp\RatesXML.xml and run this query it's fine:

select * from openrowset (bulk N'C:\Temp\RatesXML.xml', SINGLE_BLOB) rs

Johan Bijnens SSC Guru Points: 135374 More actions · Answer 3

Did you try using CLR proc or function ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

roi.reuven SSCommitted Points: 1700 More actions · Answer 4

I wrote Clr function

Which copies XML

from Web server

to a file in the file system

The way I use the function

select dbo.downloadfile ('http://rates.fxcm.com/RatesXML','C:\Temp\RatesXML.xml')

Johan Bijnens SSC Guru Points: 135374 More actions · Answer 5

If you know the expected file size is very small, you could as well read it directly into an xml object and the also have you clr proc load your table.

Don't forget to check ALL content !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

roi.reuven SSCommitted Points: 1700 More actions · Answer 6

When you want to create a clr function that download the file

to the computer, or a clr function that will return the XML

Notice must be given to clr function

EXTERNAL_ACCESS Permissions

Read this article he deals with this problem

http://footheory.com/blogs/bennie/archive/2006/12/07/invoking-a-web-service-from-a-sqlclr-stored-procedure.aspx

keymoo SSCrazy Points: 2975 More actions · Answer 7

roi.reuven (1/12/2011)
I wrote Clr function
Which copies XML
from Web server
to a file in the file system
The way I use the function
select dbo.downloadfile ('http://rates.fxcm.com/RatesXML','C:\Temp\RatesXML.xml')

Thanks, could you post the function? Is there no way to read the XML from the internet directly into SQL Server memory without using a file?