HtmlTable_To_Table
Returns tabular data derived from an HTML Table
- Free Stored Procedure
- SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017
EXEC SQLHTTP.net.HtmlTable_To_Table @HTMLTableAsXMLString, @FirstRowContainsHeaderNames
| Name | Type | Description |
|---|---|---|
| @HTMLTableAsXmlString | nvarchar(MAX) | String containing an HTML Table markup |
| @FirstRowContainsHeaderNames | bit | 0 = First row does NOT contain column names and will therefore be generated automatically (ex. Col1, Col2, Col3, etc.) 1 = First row CONTAINS columns names |
| @StartRow | int | Default: 1 (true) |
| @MaxRows | int | Default: 0 (all rows) |
| @RemoveDoubleQuotes | bit | Default: 1 (true) |
| @DisplayTableDefinition | bit | Optional. Indicates whether to output a table definition of the returned table and an accompanying insert statement. The table definition will be viewable in the Messages tab of SQL Server Management Studio’s Query Results Pane. See the last example below. Default: 0 (false) |
Varies, depending on HTML Table columns and data types
This example produces tabular data from an HTML Table markup with column names defined in the first <tr> block:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
DECLARE @HtmlTableAsXmlString nvarchar(MAX) DECLARE @FirstRowContainsHeaderNames bit SET @HtmlTableAsXmlString = N' <table> <tr> <td>Year</td> <td>Start date</td> <td>Ending date</td> </tr> <tr> <td>2017</td> <td>March 12</td> <td>November 5</td> </tr> <tr> <td>2018</td> <td>March 11</td> <td>November 4</td> </tr> <tr> <td>2019</td> <td>March 10</td> <td>November 3</td> </tr> <tr> <td>2020</td> <td>March 8</td> <td>November 1</td> </tr> </table>' SET @FirstRowContainsHeaderNames = 1 DECLARE @CustomTableBasedOnHtmlTableAsXmlString TABLE ( [Year] int, [Start Date] varchar(50), [End Date] varchar(50), PRIMARY KEY CLUSTERED ([Year])) INSERT INTO @CustomTableBasedOnHtmlTableAsXmlString EXEC SQLHTTP.net.HtmlTable_To_Table @HtmlTableAsXmlString, @FirstRowContainsHeaderNames SELECT * FROM @CustomTableBasedOnHtmlTableAsXmlString |
|
1 2 3 4 5 6 7 8 |
Year Start Date End Date ----------- ---------------------------- ---------------------- 2017 March 12 November 5 2018 March 11 November 4 2019 March 10 November 3 2020 March 8 November 1 |
This example produces tabular data from an HTML Table markup with automatically generated column names. It also limits the results to three rows and includes the resulting table definition and accompanying insert statement in the Query Results pane of SSMS:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
DECLARE @HtmlTableAsXmlString nvarchar(MAX) DECLARE @FirstRowContainsHeaderNames bit SET @HtmlTableAsXmlString = N' <table> <tr> <td>Year</td> <td>Start date</td> <td>Ending date</td> </tr> <tr> <td>2017</td> <td>March 12</td> <td>November 5</td> </tr> <tr> <td>2018</td> <td>March 11</td> <td>November 4</td> </tr> <tr> <td>2019</td> <td>March 10</td> <td>November 3</td> </tr> <tr> <td>2020</td> <td>March 8</td> <td>November 1</td> </tr> </table>' SET @FirstRowContainsHeaderNames = 0 DECLARE @CustomTableBasedOnHtmlTableAsXmlString TABLE ( Col1 nvarchar(MAX), Col2 nvarchar(MAX), Col3 nvarchar(MAX)) INSERT INTO @CustomTableBasedOnHtmlTableAsXmlString EXEC SQLHTTP.net.HtmlTable_To_Table @HtmlTableAsXmlString, @FirstRowContainsHeaderNames, @MaxRows = 3, @DisplayTableDefinition = 1 SELECT * FROM @CustomTableBasedOnHtmlTableAsXmlString |
|
1 2 3 4 5 6 7 8 9 |
Col1 Col2 Col3 ----------- ---------------------------- ---------------------- Year Start Date End Date 2017 March 12 November 5 2018 March 11 November 4 2019 March 10 November 3 2020 March 8 November 1 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @Table TABLE( [Col1] nvarchar(MAX) NULL [Col2] nvarchar(MAX) NULL [Col3] nvarchar(MAX) NULL) INSERT INTO @Table([Col1], [Col2], [Col3]) EXEC SQLHTTP.net.HtmlTable_To_Table @HtmlTable = @HtmlTable, @StartRow = 1, @MaxRows = 3, @RemoveDoubleQuotes = 1 |