The idea of being able to write a SQL query against the contents of a web page or the output of a web API that returns JSON (or XML) may seem far-fetched: but it's not only possible, it's easy using xSkrape for SQL Server.
In fact, as of late August 2016, we've released a new version that offers important benefits including:
We've also written a new blog article that discusses this new implementation, some use cases, and an honest look at pros and cons.
In this series of steps, we show how you can be up-and-working with xSkrape for SQL Server in next to no time. You can watch our demo video to see these steps in action, or review them here. (Clicking on any of the images enlarges them.)
System Requirements: These tools work with SQL Server 2012 and up, on Windows where .NET 4.6.1 is installed.
If you have any questions, comments or suggestions, please don't hesitate to contact us!
We've "webified" the Script Generator Tool that was previously included in the SQL-Hero installation. You can now run the same tool here, directly from the web. You'll find that the general process to use it is the same as we've described in our documentation, except you'll end up using the "Hosted" versions of our SQL CLR functions.
Once you start the Script Generator, click the Set Connection button and provide connection details for the database you wish to "enable" for xSkrape use. Databases enabled in this way have two CLR assemblies added, which requires you to have the TRUSTWORTHY setting enabled for your database and CLR enabled at an instance level.
With a connection established, pick the "Enable xSkrape for Database" template and click the Create Script button. The necessary T-SQL to add the CLR assemblies and the xSkrape CLR functions is generated. You can click Execute to apply these changes to the selected database.
Be sure you've registered an xSkrape.com account. You'll need this in order to track your credit usage, and a client key is made available that you'll need to provide as the first parameter to the new "Hosted" CLR user-defined functions. When you register an account, we'll provision it with a query endpoint dedicated for SQL queries, and you can find the client key by going to My Account -> Queries once logged into your xSkrape.com account.
Now you can start using xSkrape functions immediately. For example HostedWebGetSingle has no "wrapper" and as such you could write something like "SELECT dbo.HostedWebGetSingle('your_own_key_goes_here', 'http://regsho.finra.org/FNSQshvol20160817.txt', 'regex=(?<=\|GOOG\|)\d+', null, null)" and expect a result when executed in the "xSkrape enabled" database.
For retrieval of tabular results, we strongly recommend creating wrapper views. These strongly-type and pivot columns so you can write queries using human-readable column names. This process is greatly simplified using the Script Generator Tool, picking either the "HOSTED - Create Wrapper View From Url" or the "HOSTED - Create Wrapper Parameterized View From Url" template. For these templates, provide parameters to identify the source URL in a way that would capture the expected results so a proper wrapper can be generated (with valid data types). After you generate and execute the creation of your view, feel free to customize the generated code as necessary.
You might not have necessary pre-requisite components installed. You can do so by running the full setup program.
Run Script Generator Application
If the above launch button does not work, you can try running the full setup program.
If the applicaiton does not start automatically, watch for a download that needs to be run to start the script generator tool: