SQL Server web XML to table

Question

I want to get a data from a web XML URL and convert the result to a standard table with the values.

I have the following code:

IF OBJECT_ID('tempdb..#xml') IS NOT NULL 
      DROP TABLE #xml

CREATE TABLE #xml (yourXML XML)
GO

DECLARE @URL NVARCHAR(max) 

SELECT @URL = 'https://www.boi.org.il/currency.xml' 

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 x.*, y.c.query('.')
FROM #xml x
    CROSS APPLY x.yourXML.nodes('/CURRENCIES/CURRENCY') y(c)

Right now the result of this code is a XML "hyperlink" query and I would like to get it as a standard values query result that in the end I can work with the data.

The query result structure columns should be:

LAST_UPDATE | NAME | UNIT | CURRENCYCODE | RATE

Charlieface · Accepted Answer · 2021-02-28 12:12:55Z

2

I strongly advise you not to make web requests from T-SQL, it's not the place for it. Do it in Powershell, C# or Java, external to SQL Server.

SQL Server wasn't designed to have extra threads waiting on web requests. It is also very difficult to debug errors in sp_oa functions.
Furthermore, these functions have many issues, not least that they have not been updated in many years and are suspected to have security issues. It also seems that one of the worst things you can with a database server that you want to keep secure is make it "surf the web".

See also these for more info:
How to avoid sp_OACreate limits?
sp_OACreate error on SQL Server 2005
When did sp_OACreate et. al. become deprecated?

You can still parse the XML in SQL once you have loaded it, here's how:

Use /text() to get the inner text of a node, it can be faster than using just the node name. Make sure the data types are what you require.

SELECT
    x.yourXML.value('(/CURRENCIES/LAST_UPDATE/text())[1]','date') Last_Update,
    n.c.value('(/NAME/text())[1]','nvarchar(50)') Name,
    n.c.value('(/UNIT/text())[1]','decimal(18,10)') Unit,
    n.c.value('(/CURRENCYCODE/text())[1]','nchar(3)') CurrencyCode,
    n.c.value('(/RATE/text())[1]','decimal(18,10)') Rate
FROM #xml x
    CROSS APPLY x.yourXML.nodes('/CURRENCIES/CURRENCY') n(c);

edited Feb 28, 2021 at 12:12

answered Feb 28, 2021 at 8:47

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

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

1 Comment

xl0911 Over a year ago

Why do you not recommend on this approach ?

SQLpro · Accepted Answer · 2021-02-28 08:58:33Z

For your result :

EXEC @Result = sp_OAMethod @Obj, 'close';--, NULL, 'GET', @URL, false

SELECT d.value('(./LAST_UPDATE)[1]', 'DATETIME') AS LAST_UPDATE,
       c.value('(./NAME)[1]', 'NVARCHAR(64)') AS NAME,
       c.value('(./UNIT)[1]', 'SMALLINT') AS UNIT,
       c.value('(./CURRENCYCODE )[1]', 'NVARCHAR(64)') AS CURRENCYCODE, 
       c.value('(./RATE)[1]', 'DECIMAL(38,2)') AS RATE
FROM #xml x
    CROSS APPLY x.yourXML.nodes('/CURRENCIES/CURRENCY') y(c)
    CROSS APPLY x.yourXML.nodes('/CURRENCIES') z(d);

Do not forget to close your OA object. And eventually to disable the possibility to use it :

EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;

Of course I would prefer this version with a table variable :

DECLARE @vxml TABLE (an_xml XML);
DECLARE @URL NVARCHAR(max) = 'https://www.boi.org.il/currency.xml',
        @Obj int, 
        @Result int,
        @HTTPStatus int; 

EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;

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 @vxml ( an_xml )
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml';

EXEC @Result = sp_OAMethod @Obj, 'close';

EXEC sp_configure 'Ole Automation Procedures',0;
RECONFIGURE;

SELECT d.value('(./LAST_UPDATE)[1]', 'DATETIME') AS LAST_UPDATE,
       c.value('(./NAME)[1]', 'NVARCHAR(64)') AS NAME,
       c.value('(./UNIT)[1]', 'SMALLINT') AS UNIT,
       c.value('(./CURRENCYCODE )[1]', 'NVARCHAR(64)') AS CURRENCYCODE, 
       c.value('(./RATE)[1]', 'DECIMAL(38,2)') AS RATE
FROM @vxml AS x
    CROSS APPLY x.an_xml.nodes('/CURRENCIES/CURRENCY') y(c)
    CROSS APPLY x.an_xml.nodes('/CURRENCIES') z(d);

Collectives™ on Stack Overflow

SQL Server web XML to table

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related