Have xml file in url :
<response>
<sum>0</sum>
<result>0</result>
<comment>sel*1.9488|buy*1.9453</comment>
</response>
Now want stored procedure where i can parse this xml file from url and update into columns values which is in <comment>sel*1.9488|buy*1.9453</comment> want add buy*1.9453 to my table. How do it ?
To get the XML from a URL you need to do the following:
Enable Ole Automation Procedures
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Then to get the XML from a url, (answer based on an updated version from here), the following creates a temp table to store the value so you can then process the results using an xpath and substring.
This is a working example using a google maps xml, you will need to update the url and xpath to your specific requirements.
USE tempdb
GO
IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml
CREATE TABLE #xml ( yourXML XML )
GO
DECLARE @URL VARCHAR(8000)
DECLARE @QS varchar(50)
-- & or ? depending if there are other query strings
-- Use this for when there is other query strings:
SELECT @QS = '&date='+convert(varchar(25),getdate(),126)
-- Use this for when there is NO other query strings:
-- SELECT @QS = '?date='+convert(varchar(25),getdate(),126)
SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?latlng=10.247087,-65.598409&sensor=false' + @QS
DECLARE @Response varchar(8000)
DECLARE @XML xml
DECLARE @Obj int
DECLARE @Result int
DECLARE @HTTPStatus int
DECLARE @ErrorMsg varchar(MAX)
EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT
EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT
INSERT #xml ( yourXML )
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT
SELECT yourXML.value('(//GeocodeResponse/status)[1]','VARCHAR(MAX)') from #xml
In order to insert the substring you will need to do something like this to return everything after the pipe and add into your table:
INSERT tableDestination (valueDestination)
SELECT substring(yourXML.value('(//response/comment)[1]','VARCHAR(MAX)'),charindex('|',yourXML.value('(//response/comment)[1]','VARCHAR(MAX)'),1)+1,len(yourXML.value('(//response/comment)','VARCHAR(MAX)'))) from #xml
SELECT yourXML.value('(//GeocodeResponse/status)[1]','VARCHAR(MAX)') from #xml to SELECT yourXML.value('(//response/comment)[1]','VARCHAR(MAX)') from #xml ,when i exec procedur select write Acces denied'MSXML2.XMLHttp' to 'MSXML2.ServerXMLHTTP.6.0' as the error sounds similar to this<comment>sel*1.9588|buy*1.9653</comment> but my procedure parse old comment in XML. Why its not refresh in procedure ?How about somethine like
DECLARE @xml XML =
'<response>
<sum>0</sum>
<result>0</result>
<comment>sel*1.9488|buy*1.9453</comment>
</response>'
SELECT @xml.value('(//response/comment)[1]','VARCHAR(MAX)')
From value() Method (xml Data Type)
Performs an XQuery against the XML and returns a value of SQL type. This method returns a scalar value.
You typically use this method to extract a value from an XML instance stored in an xml type column, parameter, or variable. In this way, you can specify SELECT queries that combine or compare XML data with data in non-XML columns.
