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:    ALL xSkrape for Excel "Web" Add-in xSkrape for Excel Desktop Add-in xSkrape for SQL Server xSkrape for SSIS xSkrape Web API

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.