Open XML from website or consume webservice, URGENT!!!

Question

Open XML from website or consume webservice, URGENT!!!

ramadesai108

SSChampion

Points: 10609
More actions
September 27, 2009 at 2:29 pm

#138347

Hi All,
Is there a way to open an XML file from a website directly from SQL Server? For example, http://someSite.com/someXML.XML.
Or is ther a way to consume a webservice directly from SQL Server?
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 1

I'll post the reply here, rather than in the lots of times you've asked this question! 🙂

You haven't really been very specific about what you need, but probably the best way to consume a web service from within SQL Server is to use an assembly stored procedure / function / whatever is most appropriate for you.

It really depends what you need to do, though.

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

ramadesai108 SSChampion Points: 10609 More actions · Answer 2

Hi Matt,

Finally I got a response. Thank you. I want to open an xml file form a website directly from sql server 2005. Is this posible? If so, then how?

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 3

Are you opening an XML file, or consuming a web service?

What you want to do is program it in C# or VB.NET as a CLR Stored Procedure.

There's lots of help on the net on how to do that.

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

ramadesai108 SSChampion Points: 10609 More actions · Answer 4

Matt,

I do not want to use anything other than sql to open an xml from a website. Is this possible?

Thanks.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

ramadesai108 (9/29/2009)
Matt,
I do not want to use anything other than sql to open an xml from a website. Is this possible?
Thanks.

... to do what esactly? Knowing what you mean by "open" determines what answer you get. Why no other tool (although CLR support is "baked into" SQL Server, so in a sense that IS "SQL")?

SSMS isn't Internet Explorer, so it really isn't geared to be used to open web pages/XML from foreign sources if you are alluding to that.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ramadesai108 SSChampion Points: 10609 More actions · Answer 6

Matt,

Let me clarify. I have a website "http://someSite.com/someXML.xml". This site contains an XML file. I want to open that xml file and dump the data into sql table. I do not want to use C# or VB. I just want to use T-SQL to open that xml file directly from the site, read it and dump the data into sql table. I know how to read the xml data and insert it into sql table using T-SQL, but I do not know how to access/open that xml file directly from the website using T-SQL. Is there a way?

Thanks for your time.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

As far as I know - with an external file coming from a web site (i.e. somewhere you cannot get to via a drive mapping, etc....), you would need some kind of method to retrieve the file FIRST. This is where the "extra stuff" comes in as useful instead of using something rudimentray to pull the file (thus the previous recommendation to pull using a CLR stored proc for example).

You can use the DOS "type" command to dump the contents of a local file into an XML variable, but that tends to be a VERY weak approach to the scenario.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 8

I'd have to agree, I must say. CLR is the way to go with this one. 🙂

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Ed Sanford SSC Journeyman Points: 83 More actions · Answer 9

When giving examples please refrain from using some site dot com.

It is on the URI Black list, and causes e-mailed articles to be rejected as $pam.

Probably the simplest is to use an existing real site (like msdn.com, google.com, etc.) in the example (even if the specific path is made up) or a fake domain extension like .test, .dev or .local.

ramadesai108 SSChampion Points: 10609 More actions · Answer 10

Thanks to Matt Miller and Matt Whitfield. It has helped me tremendously.