xSkrape Reference Guide

This page includes documentation for all versions of xSkrape: for Excel (Add-in), for SQL Server and for SSIS.

There are different views of XS.QL documentation: at a summary level here, for URL's here, and for queries here.

You can pick a specific version to filter results:

Product Filter:   

Common to all versions of xSkrape is our xSkrape Fundamentals document (PDF). We suggest you start here to get familiar with common concepts that help when reading examples and reference documentation.


Function Reference
Function Overview Product / Version
WebGetSingle

Extract a single value (number, text, etc.) from a source reference, using query conditions that identify the value of interest.

For Excel (2.0)

For SQL Server (2.0)

WebGetSingleFromTable

Extract a single value (number, text, etc.) from a source reference, using query conditions that identify both a table and the row(s) and column that contain the specific data value of interest.

For Excel (2.0)

For SQL Server (2.0)

WebGetMultiple

Extract multiple values (numbers, text, etc.) from a source reference, using a list of query conditions that identify the values of interest.

For Excel (2.0) only

WebGetTable

Extract tabular data (rows and columns) from a source reference, using query conditions to identify the characteristics of the data in the event there are multiple tables that could be identified in the source.

For Excel (2.0)

For SQL Server (2.0)

WebGetDynamicTable

Extract tabular data (rows and columns) from a source reference, using query conditions to identify the characteristics of the data in the event there are multiple tables that could be identified in the source. Results are returned in adjacent cells, relative to the function and the number of rows and columns returned is determined by the size of the source data (i.e. as opposed to a fixed cell range offered by WebGetTable).

For Excel (2.0) only

WebGetTableMetadata

This is primarily intended to be an internal function used by xSkrape Script Generator, as described in the fundamentals white-paper. It describes the characteristics of the source data table so that code generation can construct a strongly-typed wrapper for the corresponding tabular data.

For SQL Server (2.0) only

Send SMS/EMail

Send an E-Mail or SMS message to a fixed distribution list, with text content coming from text in your worksheet. One could for example dynamically create a message that includes specific metrics that can be shared without sending or sharing the entire workbook.

For Excel "Web" Add-in (3.0) only


Page Explorer Reference
Task Overview

Load Data From a Source

Load Raw - only the raw data from the source is shown, no attempt to resolve it as XML

Load Raw, also Launch Browser - only the raw data from the source is shown, plus the default browser is opened side-by-side to help visually identify elements of interest

Load as XML - data from the source is shown, resolved as XML where possible

Load as XML, also Launch Browser - data from the source is shown, resolved as XML where possible, plus the default browser is opened side-by-side to help visually identify elements of interest

Using Tables Available for Preview

After loading source data, identified tables are shown in tabs on the lower part of the screen. Each tab includes a preview of what the unfiltered / untransformed table would look like if retrieved using WebGetTable or WebGetDynamicTable. It's also useful when constructing value queries when using WebGetSingleFromTable. Each tab may include some suggestions about possible table identification criteria that would uniquely identify the table (among all available tables).

Finding a Value Expression to Retrieve a Known Value

After loading source data "as XML", we can provide a value that exists on the page in the "Search For" text box and click on "Search". If possible, one or more "xpath" suggestions could be made if the value can be identified using XPath. If some "Related Text" can be provided - typically a label that closely precedes the value of interest - more value suggestions might be offered such as "followinginnertext", "numberfollowsnear" or "numberwithsuffixfollowsnear". These suggestions appear on a special tab: "Value Suggestions".


Web API Reference
Function Overview Available
WebGetSingle

Extract a single value (number, text, etc.) from a source reference, using query conditions that identify the value of interest.

6/1/2016

WebGetSingleFromTable

Extract a single value (number, text, etc.) from a source reference, using query conditions that identify both a table and the row(s) and column that contain the specific data value of interest.

6/1/2016

WebGetTable

Extract tabular data (rows and columns) from a source reference, using query conditions to identify the characteristics of the data in the event there are multiple tables that could be identified in the source.

6/1/2016

SendMessageDirect

Send an E-Mail or SMS message to a distribution list.

6/1/2016

SendRSSDirect

Send a message to a hosted RSS data feed. You can share the RSS URL with others to effectively create a public broadcast distribution for messages you generate, over any device type that has an RSS reader app (most do!).

6/24/2016


Script Generator Reference
Step Overview

Step 1 - Establish your connection

Click on "Set Connection". In the pop-up window, enter SQL instance name and database name. Provide credentials (default is to use Windows Authentication). With a valid connection, the template drop-down becomes enabled.

Step 2 - Pick your template

Enable xSkrape for Database - every database in which you expect to use xSkrape functions needs to have xSkrape "enabled". This process enables SQL CLR at the server instance level and applies SET TRUSTWORTHY ON to the target database. It also adds CLR assemblies to the database and creates the xSkrape functions in an existing schema of your choice. This only needs to be run once for your database, or potentially if there's an xSkrape version update that adds new functionality.

Create Wrapper View from Url - by providing parameters to the WebGetTable function, Script Generator can create a SQL view that returns data by calling WebGetTable, with columns that have names and data types inferred from the source data.

Create Wrapper Parameterized View from Url - similar to Create Wrapper View from Url, but an inline UDF is generated, offering the opportunity to include strongly-typed parameters that can be used to change the URL used, or other elements of the WebGetTable function call.

Objects such as views and UDF's created by the Script Generator can be adjusted "by hand" as desired. For example, the "parameterized view" template includes no parameters by default but provides a "TODO" note where you'd normally add them.

Picking a template enables the "Create Script" button.

Step 3 - Generate script (enter template parameters)

Enable xSkrape for Database

Parameter Description
Schema for xSkrape Objects Pick an existing schema that will have xSkrape functions added to it. Default is "dbo".

Create Wrapper View from Url and Create Wrapper Parameterized View from Url

Parameter Description
View Schema Name Pick an existing schema that will have the view added to it. Default is "dbo". (Required)
View Name Give a name for the view that will be generated. (Required)
SQL-Hero web server URL Provide the URL that was mentioned during installation of SQL-Hero server components as the "service address". This is typically "http://hostname:46837" where "hostname" is the name of the server where you installed your SQL-Hero server components. Learn more. (Required)
Sample data URL This URL becomes the @parseUrl parameter for the call to WebGetTable. This does not need to be the "final" URL (you may parameterize it, for example) but it does need to produce "sample" data so the code generator can inspect metadata related to the query. (Required)
Sample data table query This value becomes the @tableCriteriaQuery parameter for the call to WebGetTable. This does not need to be the "final" table query (you may parameterize it, for example) but it does need to produce "sample" data so the code generator can inspect metadata related to the query. (Required)
Optional configuration This value becomes the @additionalConfig parameter for the call to WebGetTable. (Optional)
xSkrape object schema name Provide the schema name in which xSkrape objects were added at the time of running the "Enable xSkrape for Database" template. Default is "dbo". (Required)
Use ISNUMERIC checks When checked, additional checks are added to the generated code to validate that source data conforms to the expected data type. Default is checked.

Clicking "Ok" on the collected parameters causes your script to be generated and placed in the script text box. It also enables the "Copy to clipboard" and "Execute" buttons.

Step 4a - Copy to clipboard

If you'd like to change the script prior to execution, you can copy it to the clipboard.

Step 4b - Execute script

The generated code is executed against the selected database.


SSIS Component Documentation
Component Overview

     Dynamic Data Parser Task

Offers xSkrape parsing functionality against data sources including HTTP, HTTPS, FILE based. Understands formats such as HTML, CSV, TSV, etc. Import data through truncate or append.

     Dynamic Data Parser Data Source

Offers xSkrape parsing functionality against data sources including HTTP, HTTPS, FILE based. Understands formats such as HTML, CSV, TSV, etc. Data streamed as an SSIS data source.

E-Mail Watcher Source

SQL-Hero Parallel Template Transform

Smart Merge Dataflow Target

Smart Merge Task

SQL-Hero Command Task

SQL-Hero SQL Execution Task

SQL-Hero Template Data Source

SQL-Hero Template Task

Documentation pending for other (non-xSkrape) SSIS components.