I have the following issue. running the sql below on our server it returns the expected results. Running the same on another server it returns no values.
Did the following:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
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://exampleURL' -- + @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
declare @input XML=(
SELECT
yourXML
from
#xml)
SELECT
Item.value('(Code)[1]', 'nvarchar(max)') as Code,
Item.value('(Description)[1]', 'varchar(max)') as Description,
Item.value('(ImageUrl)[1]', 'nvarchar(max)') as ImageUrl
from
@input.nodes('//product') AS T(Item)
Within the second server the @input returns null. There is a proxy to access the site on the server and it operates with sql server 2008.
Any ideas why the null values?
So, it seems that my issue was that I had no access over the internet from my SQL server, so I had to use the below line to set the proxy.
exec @Result = sp_OAMethod @Obj, 'setProxy', NULL, '2', 'http://myProxy'
Once this was sorted, I managed to get my results.