Parsing xml file from url using sql server

Question

I am parsings XML file from url, when I parse first time it take correct response , but when in url xml file changed , sql server parse old response (for example in first time something=5 sql server parse 5, then when in url something = 6 sql server parse 5 again). But I think there is caching problem because when I delete in IE history include cookies and etc.SQL server parse correct response. How solve it ? I parse like this :

Declare @Object as Int; 
Declare @ResponseText as Varchar(8000); 
Declare @Url as Varchar(MAX); 

    select @Url = 'http://myurl'

    Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; 
    Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false' 
    Exec sp_OAMethod @Object, 'send' 
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT      
    Exec sp_OADestroy @Object 

    --load into Xml 
    Declare @XmlResponse as xml; 
    select @XmlResponse = CAST(@ResponseText as xml)  
    select @XmlResponse.value('(//response/comment)[1]','varchar(50)')

Edit: I solve it, change IE setting and delete temporary internet fails on every time i visit page

Community · Accepted Answer · 2017-05-23 12:11:09Z

1

Do you have control over the content coming form the other end? you could maybe set it not to cache..

e.g. asp.net(https://stackoverflow.com/a/4080188):

Response.AddHeader("Cache-Control", "no-cache, no-store, max-age=0, must-revalidate");

or classic asp (https://stackoverflow.com/a/17390837):

Response.Expires = 0
Response.Expiresabsolute = Now() - 1
Response.AddHeader "pragma","no-cache"
Response.AddHeader "cache-control","private"
Response.CacheControl = "no-cache"

If you don't have access to the other end a fudge that you can do is add an extra parameter to your request that always changes - e.g. the time.

select @Url = 'http://myurl?time='+CONVERT(NVARCHAR(30),GETDATE(),120)

this assumes that the server at the other end ignores this extra parameter.. they often do

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Sep 24, 2013 at 14:30

user359135

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parsing xml file from url using sql server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related