xSkrape for SQL Server


Sample xSkrape for SQL

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:

  • You no longer need to install SQL-Hero! No need to concern yourself with client licenses or configuring IIS, etc. We're offering a hosted version of xSkrape for SQL Server.
  • To prove how easy this is, we've provided a video below that demonstrates going from "zero" to actively querying web sources in less than 10 minutes!
  • You can use the same approach as described in our fundamentals whitepaper to create view wrappers and get their benefits.
  • You get all the benefits of xSkrape.com including free usage up to the free usage tier limits, after which you can top-up your account with credits on an as-needed basis.
  • Adds a unique capability to Microsoft SQL Server: we're not aware of any other product that can do what we do with xSkrape for SQL Server.

We've also written a new blog article that discusses this new implementation, some use cases, and an honest look at pros and cons.


Let's explore some new ideas...




Script Generator Tool & Walkthrough

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!



1

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.




2

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.




3

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.




4

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.




5

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.




6

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:

Warning message
If you find either of these appearning at the bottom of your browser window, continue with their download and run them.
Warning message