Lets Today we will take a trip on xml data .I faced many of insurance company having data from different source system . Even still people are using foxpro database ( the first database for Microsoft) , for these kind of source or for some other you can install a driver to pull data from different source system . Point is coming you are working in a stock market company and there is a API (Application Program Interface )which is having all update stock market data in web . I remember I used to have a scenario where data was coming from web even data is updating in web and we do not have any control to work on it . then I came with a solution for below one which is dynamically retrieve data from web using a stored procedure and insert to a sql table .There is so many ways . Its always depend on your node of XML how the node is define .
Lets take a below example
Here i have my Yahoo Finance value site . Where data is always updating under time .Basically its Stock market site and its a free API.any body can use these data .
so this link having data in the XML format .
lets check with the stored procedure
declare @doccount INT;
declare @XMLString XML;
declare @SourceURL NVARCHAR(255);
declare @FILE_Path NVARCHAR(255);
declare @Command NVARCHAR(255);
declare @SQL_Script NVARCHAR(255);
declare @DestinationTable TABLE(DATA XML);
SET @SourceURL = 'http://finance.yahoo.com/webservice/v1/symbols/allcurrencies/quote';---Url
SET @FILE_Path = 'C:\Users\sitakanta\Downloads\New folder\quotes.xml';---File Patha Define
SET @Command = '
C:\Windows\System32\WindowsPowerShell\v1.0\powershell (new-object System.Net.WebClient).DownloadFile( ''' +
@SourceURL + ''',''' + @FILE_Path + ''' )'
---Downloaded
--print @Command
EXEC master.dbo.xp_cmdshell @Command, no_output -----Query to download
-- Loading the Downloaded File into the XML variable
SET @SQL_Script = 'SELECT BulkColumn FROM OPENROWSET( BULK ''' + @FILE_Path + ''', SINGLE_BLOB ) AS a'
INSERT @DestinationTable EXEC(@SQL_Script);
SELECT @XMLString = data from @DestinationTable
EXEC sp_xml_preparedocument @doccount OUTPUT, @XMLString;
-----Output Data
SELECT * FROM OPENXML(@doccount, N'//list/resources/resource')
WITH ( name VARCHAR(10) 'field[@name="name"]',
price VARCHAR(10) 'field[@name="price"]',
symbol VARCHAR(10) 'field[@name="symbol"]',
ts VARCHAR(10) 'field[@name="ts"]',
type VARCHAR(10) 'field[@name="type"]',
volume VARCHAR(10) 'field[@name="volume"]');
EXEC sp_xml_removedocument @doccount;
And here is the output :
Let me know for any info.
Regards,
Sita
