xSkrape for SQL Server
xSkrape for SQL Server offers SQL developers access to some new CLR functions which do simple yet powerful things. In particular, new functions include:
- WebGetSingle - Data returned from the URL address (could be HTML, JSON, CSV, etc.) is queried for a specific scalar value and returned from this scalar user-defined function (UDF).
- WebGetSingleFromTable - Data returned from the URL address (could be HTML, CSV, etc.) is queried for a specific scalar value (extracted from a tabular source, with aggregation) and returned from this scalar UDF.
- WebGetTable - Data returned from the URL address is searched for "tabular data"; if there are multiple possible tabular results, a parameter can be used to define criteria by which the exact table of interest is isolated. Data is returned in a narrow, fixed tabular format with a "row ID" used to associate related data, by row. (Typically you would use the script generator tool or SQL-Hero to create a wrapper view which pivots the data into a result set column per column in the source data.)
- WebGetTableMetadata - Can be combined with WebGetTable to determine details about the columns from the matched source rowset. This is typically not used directly but instead used by the script generator and/or SQL-Hero to assist in building a strongly-typed wrapper view which is the preferred way to access your data (since it looks like your source data).
What are the benefits of using xSkrape for SQL Server?
There's really no comparable functionality we've seen in a competitor, so we can talk about overall benefits:
- "Live data" from external sources can be included in larger queries without requiring potential ETL: fewer steps, less latency, and transformation rules optionally applied at the source.
- Parameterizing the xSkrape function calls can be done using in-line SQL.
- Our query language for locating data of interest is flexible and can withstand many types of change in the source page's structure without requiring code changes (eg. solutions that rely exclusively on XPath queries will break if additional HTML element nesting is added or changed in the document tree below where the data is located). It's also far easier to understand and derive than relying exclusively on XPath queries. (We do, however, offer XPath as one way to reference your data of interest.)
- You can take advantage of both caching and local compute resources to achieve good performance of your solutions.
- Page Explorer is included with the SQL-Hero distribution as a standalone executable. It's a key way to understand the source data to be "scraped" - and it offers possible suggestions on how to use the available xSkrape filters to get exactly what you want.
What's the relationship of SQL-Hero to xSkrape? What's the difference between "self-hosted" and "xSkrape-hosted"?
When you use xSkrape for SQL Server, you're using a component that runs inside of a database, on a SQL Server instance. However, that's not where we're running the majority of xSkrape logic: we'd have to install a lot of additional stuff inside of your databases to do that. Instead, you're communicating with an xSkrape host to do the actual work of grabbing and parsing data.
When you install SQL-Hero server components, you've installed an xSkrape host. You can use the versions of our CLR functions that accept a service URL, pointing at this host's HTTP address. Billing in this case is per machine (with xSkrape enabled host databases), per month. (Your first month is 200 credits which is less then the 250 free credits we offer, per month - afther that it's 500 credits per month.)
For those who don't want to go through the trouble of installing SQL-Hero server components, you can take advantage of xSkrape hosting that's offered by us at xskrape.com. You'll incur per request charges since you're using our compute resources to do the heavy lifting of xSkrape logic.
Some other differences between these models:
- Self-hosting allows you to reference local files, inside your organization (based on security settings).
- xSkrape-hosting requires you pass your xSkrape client key to most function calls (you can optionally set it in an environment variable where your xSkrape-enabled databases reside). With self-hosting, you can set your client key in the web.config file where your SQL-Hero server components are installed (an option available during installation), or store it in an environment variable on the same server, or pass it in the form "key-url" for the URL parameter to the methods that accept one.