XQueryHelper
Generates Transact-SQL XQuery statements directly from XML data
- Stored Procedure
- SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017
EXEC SQLHTTP.net.XQueryHelper @Xml, [@RootPath], [@includeColumnCount]
| Name | Type | Description |
|---|---|---|
| @Xml | xml | Required. An XML to be analyzed. |
| @RootPath | varchar(MAX) | Optional. XPath Root Path value. If not provided, all possible valid paths will be outputted with additional helpful information. See examples below. |
| @IncludeColumnCount | bit | Optional. A boolean indicator whether to output the number of columns in each possible output when the @RootPath is NULL. See example below. |
Calling XQueryHelper WITHOUT a @RootPath parameter:
|
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 |
DECLARE @X xml SET @X = '<?xml version="1.0" encoding="UTF-8"?> <response id="7b80be33:136982a9fda:-5598"> <quotes> <quote> <datetime>2012-04-17T09:25:00-04:00</datetime> <hi>579.0000</hi> <last>578.8700</last> <lo>578.8500</lo> <opn>578.9400</opn> </quote> <quote> <datetime>2012-04-17T09:30:00-04:00</datetime> <hi>582.1336</hi> <last>575.7000</last> <lo>574.0000</lo> <opn>578.8600</opn> </quote> </quotes> </response>' EXEC SQLHTTP.net.XQueryHelper @X |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Usage Name Rows Columns --------------------------------------------------------------------- ---------- ------ ------------------------------------------------------------------------ EXEC SQLHTTP.net.XQueryHelper @X, 'response' -- response 1 (to view column count (slower!), set parameter @IncludeColumnCount = 1) EXEC SQLHTTP.net.XQueryHelper @X, 'response/quotes' -- quotes 1 (to view column count (slower!), set parameter @IncludeColumnCount = 1) EXEC SQLHTTP.net.XQueryHelper @X, 'response/quotes/quote' -- quote 2 (to view column count (slower!), set parameter @IncludeColumnCount = 1) EXEC SQLHTTP.net.XQueryHelper @X, 'response/quotes/quote/datetime' -- datetime 2 (to view column count (slower!), set parameter @IncludeColumnCount = 1) EXEC SQLHTTP.net.XQueryHelper @X, 'response/quotes/quote/hi' -- hi 2 (to view column count (slower!), set parameter @IncludeColumnCount = 1) EXEC SQLHTTP.net.XQueryHelper @X, 'response/quotes/quote/last' -- last 2 (to view column count (slower!), set parameter @IncludeColumnCount = 1) EXEC SQLHTTP.net.XQueryHelper @X, 'response/quotes/quote/lo' -- lo 2 (to view column count (slower!), set parameter @IncludeColumnCount = 1) EXEC SQLHTTP.net.XQueryHelper @X, 'response/quotes/quote/opn' -- opn 2 (to view column count (slower!), set parameter @IncludeColumnCount = 1) |
Calling XQueryHelper WITH a @RootPath parameter:
|
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 |
DECLARE @X xml SET @X = '<?xml version="1.0" encoding="UTF-8"?> <response id="7b80be33:136982a9fda:-5598"> <quotes> <quote> <datetime>2012-04-17T09:25:00-04:00</datetime> <hi>579.0000</hi> <last>578.8700</last> <lo>578.8500</lo> <opn>578.9400</opn> </quote> <quote> <datetime>2012-04-17T09:30:00-04:00</datetime> <hi>582.1336</hi> <last>575.7000</last> <lo>574.0000</lo> <opn>578.8600</opn> </quote> </quotes> </response>' EXEC SQLHTTP.net.XQueryHelper @X, 'response/quotes/quote' |
|
1 2 3 4 5 6 7 8 9 |
SELECT T.C.value(N'datetime[1]', N'nvarchar(MAX)') AS [datetime] ,T.C.value(N'hi[1]', N'nvarchar(MAX)') AS [hi] ,T.C.value(N'last[1]', N'nvarchar(MAX)') AS [last] ,T.C.value(N'lo[1]', N'nvarchar(MAX)') AS [lo] ,T.C.value(N'opn[1]', N'nvarchar(MAX)') AS [opn] FROM @X.nodes(N'/response/quotes/quote') T(C) |
Result of generated XQuery:
|
1 2 3 4 5 6 |
datetime hi last lo opn --------------------------- --------- --------- --------- -------- 2012-04-17T09:25:00-04:00 579.0000 578.8700 578.8500 578.9400 2012-04-17T09:30:00-04:00 582.1336 575.7000 574.0000 578.8600 |