Help > ODBC Drivers > Amazon S3 XML Driver > SQL Query Examples

Driver SQL Syntax

On this page you will find various query examples for Amazon S3 XML ODBC Driver / Data Gateway Connector. This driver offers highly flexible Client Size SQL Query engine which runs on client side to offer rich query language like a traditional RDBMS. Keep in mind that its not same as traditional database SQL so many features might not be supported also this engine is invoked on client side so there might be some performance issue if you have very large dataset. In below section you can see high level SQL Grammer / Syntax for writing SQL query for this driver.

SELECT Syntax / INTO

Below section shows use of SELECT statement. It can output data or store data into temporary table (must be named with prefix # (i.e. #temp_table) ).

SELECT 
 [DISTINCT] [* | [ expression [[AS] column_name_alias] [, ...] ]
 [INTO #temp_table_name]
 [FROM table_name]
 [WHERE condition [, ...] ]
 [GROUP BY expression [, ...] ]
 [HAVING condition [, ...] ]	
 [ORDER BY expression [ASC | DESC] [, ...] ]
 [LIMIT row_count] 
 [WITH (option_name=option_value] [, ...]) ] [;]
 

option_name:=IDENTIFIER
option_value:= NUMBER | STRING

Select using Temp Table

This driver supports storing data into temporary table and then using it later in the same script. You can issue multiple SQL statements in the same script seperated by semicolon (;). For output only last Non-SELECT INTO statement is used. When you query #temp_table you get support for INNER / OUTER JOIN statements.

 SELECT [DISTINCT] [* | [ expression [[AS] column_name_alias] [, ...] ] 
 FROM #temp_table_name
 [[INNER | OUTER | CROSS] JOIN #temp_table_name [alias] ON condition 
   [join_statement] 
   [join_statement] 
   ... 
 ]
 [WHERE condition [, ...] ]
 [GROUP BY expression [, ...] ]
 [HAVING condition [, ...] ]	
 [ORDER BY expression [ASC | DESC] [, ...] ]
 [LIMIT row_count] 

Multiple SQL Statments

This driver supports executing multiple statements in a single command. You can issue multiple SQL statements in the same script seperated by semicolon (;). For output only last Non-SELECT INTO statement is used. When you query #temp_table you get support for INNER / OUTER JOIN statements.

select_into_temp; 
[select_into_temp;] 
[select_into_temp;]
...
select_from_temp_table;

Comment Syntax

ZappySys Driver supports single line comment and block comments anywhere in SQL

--some comment in single line 
|
/* some block comment */ 

UNION / UNION ALL Syntax

You can use UNION or UNION ALL key word to output multiple temp tables with similar structure in a single resultset. UNION ALL keyword doesnt apply DISTINCT operation to eliminate duplicate rows so its faster.

select_from_temp_table_1
   UNION | UNION ALL
select_from_temp_table_2
  [UNION | UNION ALL]
select_from_temp_table_3	
...
...

Stored Procedure Syntax

This driver supports writing Stored Procedure. This is useful when writing reusable parameterized SQL for ease of use. Rather than calling long SQL from application you can call short name with parameters to invoke same logic. Stored procedure support multi-statements using TEMP tables (i.e. SELECT ... INTO #mytemptable clause). To invoke Stored Proc use EXEC keyword before its name. See below Syntax.
CREATE PROCEDURE

CREATE PROCEDURE proc_name 
  [@parameter_name [= 'Some Default Value']  ] 
  [@parameter_name [= 'Some Default Value']  ] 
  ...
  ...
AS 
  sql-statement; 
 [sql-statement | sql-temp-table-usage] ;
 [sql-statement | sql-temp-table-usage] ;
 ...
 ... 

Executing PROCEDURE

EXEC proc_name [value1] [,value2] ... [,valueN]

Deleting PROCEDURE

DROP PROCEDURE proc_name

SQL Examples

Basic Read (API, File, Embedded) Query Single File

This example shows how to query single XML file (For multiple file use wildcard (i.e. *.xml)). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table). In order to see table list you must populate File/URL and other required connection parameters on the parameter grid.

SELECT * FROM $ WITH (SRC='zappysys-public-bucket/cust-1.xml')

Basic Read (API, File, Embedded) Query Multiple Files

This example shows how to query multiple XML files using wildcard (i.e. *.xml). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table).

SELECT * FROM $ 
WITH (
    SRC='zappysys-public-bucket/cust*-?.xml'
    --,RECURSIVE='True' --Include files from sub folder
)

Basic Read (API, File, Embedded) Query direct XML string (embedded inside query)

This example shows how to query direct XML string embedded inside query. This is useful for testing purpose. In real world this may not be the case because data may be coming from File or URL (API call). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table). In order to see table list you must populate File/URL and other required connection parameters on the parameter grid.

SELECT * FROM [data.row] 
WITH (
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/somefile.xml'
--SRC='zappysys-public-bucket/some*.xml'
DATA='
<data>
	<row><id>1</id><name>AAA</name></row>
	<row><id>2</id><name>BBB</name></row>
</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
)

Metadata Query with static Metadata (META option / Override Mode)

This example shows how to supply custom Metadata rather than relying dynamic metadata detection (based on 300 rows scanning). If you run query you can click on [View Metadata] button to generate default metadata. If you dont want to supply all columns in meta clause then use @OverrideMode:1 along with column list you want to alter. Possible Types for META is => <none>, String, Int64, Long, Int, Int32, Short, Byte, Decimal, Double, Float, DateTime, Date, Boolean

SELECT * FROM [data.row] 
WITH 
(DATA='
<data>
	<row><id>1</id><checknumber>0001</checknumber><name>AAA</name></row>
	<row><id>2</id><checknumber>0002</checknumber><name>BBB</name></row>
</data>'
--//Method-1: >>>DIRECT META (compact mode)<<<
,META='id:int;checknumber:string(10);name:string(10)'
-- Add: @OverrideMode:1 in the column list to override meta for only specific column(s) - In below example we are changing default datatype/length for id and checknumber, all other columns are dynamic so dont have to supply in META clause
--,META='@OverrideMode:1;id:int;checknumber:string(10)'  

--//Method-2: >>>DIRECT META (xml mode)<<<
--,META='[{Name:"id",Type:"Int32"},{Name:"checknumber",Type:"String",Length:10},{Name:"name",Type:"String",Length:10}]'
-- Add: @OverrideMode entry in the column list to override meta for only specific column(s). No need to set Type for that entry - In below example we are changing default datatype/length for id and checknumber, all other columns are dynamic so dont have to supply in META clause
--,META='[{Name:"@OverrideMode"}, {Name:"id",Type:"Int32"},{Name:"checknumber",Type:"String",Length:10}]'

--//Method-3: >>>META from File <<<
--,META='c:/data/meta.xml'

--//Method-4: >>>META from Internal Storage - Just refer entry by name <<<
--,META='MY_META_STORAGE_NAME'

)

Basic Read (API, File, Embedded) Escape Sequence (String Literals with new lines, tabs)

This example shows how to use escape sequence in String Literals (Quoted values (single quotes) in WITH, SELECT or some other clause if driver supports). You can use @ symbol before string literal to allow special characters (e.g. /r /n /t /v /u0041 //). /r=carriage return, /n=new line, /t=tab, /uXXXX=any ascii/unicode char by hex number. To produce Unicode character using escape sequence use hex number starting /u e.g. /u0041 will produce A (Uppercase).

SELECT * FROM [data.row] 
WITH (
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/somefile.xml'
--SRC='zappysys-public-bucket/some*.xml'
DATA=@'<data>\r\n<row><id>1</id><name>AA\u0041</name></row>\r\n<row><id>2</id><name>BBB</name></row>\r\n</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
)

URL JOIN Read unstructured data and pass to API call

This example shows how to query unstructured data and parse using Regex, XPath or XMLPath)

Examples of XPaths: https://zappysys.com/blog/ssis-extract-single-xml-node-using-xpath-soap-response/
Examples of XmlPath: https://zappysys.com/blog/xmlpath-examples-expression-cheetsheet/
Examples of Regular Expression: https://zappysys.com/blog/using-regular-expressions-in-ssis/

Also try following Query Examples to extract Raw data.

SELECT * FROM $
WITH(
Src='http://google.com'
,EnableRawOutputModeSingleRow='True'
)

SELECT * FROM $
WITH(
Src='http://google.com'
,EnableRawOutputModeSingleRow='True'
,RawOutputFilterExpr='<title>([^<]*)<//title>{{0,1}}||content="([^"]*)"{{0,1}}' --can be Regex, XmlPath or XPath. Use double pipe to split multiple columns
,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Xml template
,RawOutputExtractMode='Regex' --can be Regex, Xml, Xml or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path)
)

SELECT * FROM $
WITH(
Src='http://httpbin.org/get'
,EnableRawOutputModeSingleRow='True'
,RawOutputFilterExpr='$.origin||$.url' --can be Regex, XmlPath or XPath. Use double pipe to split multiple columns
--,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Xml template
,RawOutputExtractMode='Xml' --can be Regex, Xml, Xml or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path)
)

SELECT c.title,o.url,o.data 
FROM $
WITH(
	 Src='http://google.com'
	,EnableRawOutputModeSingleRow='True'
	,RawOutputFilterExpr='<title>([^<]*)<\/title>{{0,1}}||content="([^"]*)"{{0,1}}' --can be Regex, JsonPath or XPath. Use double pipe to split multiple columns
	,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Json template. Comment this line to generate default columns e.g. col1, col2...If you comment this then use c.col1 and c.col2 instread of c.title and c.content in Select and join1_xxxxx attributes
	,RawOutputExtractMode='Regex' --can be Regex, Xml, Json or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path)
    ,Alias='c'
    ,join1_RequestMethod='GET'
    ,join1_src='mybucket/myservice/[$c.title$]/log.ext'
    ,join1_RequestContentTypeCode = 'TextPlain'
    ,join1_alias='o'
    ,join1_EnableRawOutputModeSingleRow='False'
)

Functions String Manipulation

This example shows how to use string functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.

SELECT 
  substr('ZappySys', 1, 5)
, printf('%s %s %.2f', 'v1', CompanyName, CompanyId)
, ('Str1' || CompanyName || 'Str2') as ConcatExample
, upper('ZappySys')
, lower('ZappySys')
, replace('ZappySys', 'Sys', 'XYZ')
, instr('ZappySys', 'Sys')
, trim('   Zappy Sys  ') trim1
, trim('@@@ZappySys@@@', '@') trim2
, rtrim('    ZappySys   ')
, ltrim('    ZappySys   ')
, length( CompanyName )
FROM $ WITH (
FILTER='$.Info.Rows[*]',
DATA='
<Info>
<Rows><CompanyId>1000</CompanyId><CompanyName>ZappySys</CompanyName></Rows>
<Rows><CompanyId>1001</CompanyId><CompanyName>Microsoft</CompanyName></Rows>
<Rows><CompanyId>1002</CompanyId><CompanyName>Amazon</CompanyName></Rows>
</Info>
')

Functions Control flow

This example shows how to use math functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.

SELECT 
	 COALESCE(10,20) coalesce1 --returns first non-null arg
    ,COALESCE(null,20) coalesce2 --returns first non-null arg
    ,COALESCE(null,20,30,40) coalesce3 --returns first non-null arg
    ,IFNULL(null,20) --returns first non-null arg. Same function as COALESCE but simple (just two arguments allowed)
    ,NULLIF(20,20) nullif1 --returns null if both argument same
    ,NULLIF(10,20) nullif2 --returns null if both argument same
FROM $ WITH (
FILTER='$.Info.Rows[*]',         
DATA='
<Info>
<Rows><CompanyId>1000</CompanyId><CompanyName>ZappySys</CompanyName></Rows>
<Rows><CompanyId>1001</CompanyId><CompanyName>Microsoft</CompanyName></Rows>
</Info>
')

Functions Date/Time

This example shows how to use date/time functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.

XML driver supports following 5 datetime functions. The date and time functions use a subset of IS0-8601 date and time formats. The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS. The datetime() function returns YYYY-MM-DD HH:MM:SS.

date(timestring, modifier, modifier, ...)
time(timestring, modifier, modifier, ...)
datetime(timestring, modifier, modifier, ...)
julianday(timestring, modifier, modifier, ...)
strftime(format, timestring, modifier, modifier, ...)

=======================================
time string (input data)
=======================================
A time string (input) can be in any of the following formats. You can hard code or supply from column.

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

=======================================
format specifier for strftime function
=======================================
Use below format specifiers to output date in desired format. For example to output date in MM-DD-YYYY use below select query

select STRFTIME('%Y-%m-%d','now') formatted_date_time from mytable
select STRFTIME('%Y-%m-%d',[my column]) formatted_date_time from mytable

%d day of month: 00
%f fractional seconds: SS.SSS
%H hour: 00-24
%j day of year: 001-366
%J Julian day number
%m month: 01-12
%M minute: 00-59
%s seconds since 1970-01-01
%S seconds: 00-59
%w day of week 0-6 with Sunday==0
%W week of year: 00-53
%Y year: 0000-9999
%% %

=================
Modifiers
=================
The time string (input) can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows.

For example to add 5 days in current date you call it as below

select DATE('now', '+5 day') from mytable

NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
start of month
start of year
start of day
weekday N
unixepoch
localtime
utc

The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the preceding timestring and modifiers. The 's' character at the end of the modifier names is optional. Note that '±NNN months' works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four.

The 'start of' modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day.

The 'weekday' modifier advances the date forward to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth.

SELECT 
 DATE('now') date_now
,STRFTIME('%Y-%m-%dT%H:%M:%f','now') formatted_Date_time
,DATETIME('now') datetime_now
,DATE('now','localtime') datetime_now_local
,TIME('now') time_now
,JULIANDAY('now')
,DATE('now', 'start of month', '+1 month' , '-1 day' ) dt_modify1
,DATE('now', '+5 day') dt_modify2

FROM $ WITH (
FILTER='$.Info.Rows[*]',
DATA='
<Info>
<Rows><CompanyId>1000</CompanyId><CompanyName>ZappySys</CompanyName></Rows>
<Rows><CompanyId>1001</CompanyId><CompanyName>Microsoft</CompanyName></Rows>
<Rows><CompanyId>1002</CompanyId><CompanyName>Amazon</CompanyName></Rows>
</Info>
')

Functions Math

This example shows how to use math functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.

SELECT 
	 abs(-500)
    ,random()
    ,round(1000.236, 2)
FROM $ WITH (
FILTER='$.Info.Rows[*]',
DATA='
<Info>
<Rows><CompanyId>1000</CompanyId><CompanyName>ZappySys</CompanyName></Rows>
<Rows><CompanyId>1001</CompanyId><CompanyName>Microsoft</CompanyName></Rows>
</Info>
')

Functions String Manipulation

This example shows how to use string functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.

SELECT 
  substr('ZappySys', 1, 5)
, printf('%s %s %.2f', 'v1', CompanyName, CompanyId)
, ('Str1' || CompanyName || 'Str2') as ConcatExample
, upper('ZappySys')
, lower('ZappySys')
, replace('ZappySys', 'Sys', 'XYZ')
, instr('ZappySys', 'Sys')
, trim('   Zappy Sys  ') trim1
, trim('@@@ZappySys@@@', '@') trim2
, rtrim('    ZappySys   ')
, ltrim('    ZappySys   ')
, length( CompanyName )
FROM $ WITH (
FILTER='$.Info.Rows[*]',
DATA='
<Info>
<Rows><CompanyId>1000</CompanyId><CompanyName>ZappySys</CompanyName></Rows>
<Rows><CompanyId>1001</CompanyId><CompanyName>Microsoft</CompanyName></Rows>
<Rows><CompanyId>1002</CompanyId><CompanyName>Amazon</CompanyName></Rows>
</Info>
')

Functions Regular Expression

This example shows how to regular expression functions.

SELECT 
 regex_ismatch(SSN, '\d{3}-\d{3}-\d{4}') IsValidSSN, --check pattern found
 regex_replace(SSN, '\d','*') MaskedSSN, --search/replace pattern
 regex_search(SSN, '\d{4}')  SSNLast4Digits, --extract substring using pattern search
 regex_search(Email, '(?<name>\w+)@(?<domain>\w+.com)',0, 2)  DomainOf1stEmailByIndex, --extract 1st email (zero based index) and get domain name (by group name). For 2nd argument you can use numeric index or named argument. Both arguments are zero based index. 
 regex_search(Email, '(?<name>\w+)@(?<domain>\w+.com)',2, 2)  DomainOf3rdEmailByIndex, --extract 3rd email (zero based index) and get 2nd part of match (i.e. domain name). Both arguments are zero based index.
 regex_search('a@x.com | b@y.com | c@z.com', '(?<name>\w+)@(?<domain>\w+.com)','-0', 2)  DomainOfLastEmail, --extract last email (use negative sign in quote - zero based index) and get 2nd part of match (i.e. domain name). Both arguments are zero based index.
 regex_search(
       'a@x.com | b@y.com | c@z.com'
     , '(?<name>\w+)@(?<domain>\w+.com)'
     , '*' --occurrence match index ( * , N or -N ) negative number will do reverse find (e.g. to get last match use -0 , to get second last use -1)
     , 2  --group index from match (default is full match or -1)
     --, 0  -- ignore case for pattern match (0=strict match, 1=ignore case, default is 0) 
     --, 0  -- throw error if not matched 0=don't throw error, 1=throw error
     --, '#'  -- separator for multi match
    )  GetAllDomains --extract domains from all emails and show as email (zero based index) and get 2nd part of match (i.e. domain name). Both arguments are zero based index.    
FROM $ WITH (Filter='$.data.row[*]', DATA= '<data>
<row><SSN>111-222-4444</SSN><Email>a@x.com | b@y.com | c@z.com</Email></row>
<row><SSN>111-X2Z-AB44</SSN><Email>a@x.com | b@y.com | c@z.com</Email></row>
</data>')

Functions Using Placeholder Functions anywhere in SQL

This example shows how to use placeholders functions anywhere in your SQL Query. Placeholders are replaced in SQL text before Parser so you can use it anywhere in SQL. Another advantage of placeholder function is it doesn't invoke client side query processing engine unlike other advanced SQL constructs (e.g. WHERE, GROUP BY, ORDER BY, UDF). Placeholders are searched and replaced before parser can analyze the SQL query. Here are list of placeholder functions.

<<FUN_NOW>>
<<FUN_TODAY>>
<<today+2d,FUN_TO_DATE>>
<<yearstart,FUN_TO_DATE>>
<<monthstart-1d,FUN_TO_DATE>>
<<now+2h,FUN_TO_DATETIME>>
<<your_file_path,FUN_READFILE>>
<<input_text,FUN_BASE64ENC>>
<<input_text,FUN_BASE64ENC>>
<<input_text,FUN_BASE64DEC>>
<<input_text,FUN_URLENC>>
<<input_text,FUN_URLDEC>>
<<date_format,FUN_TODAY>>
<<date_format,FUN_TO_UTC_DATE>>
<<date_format,FUN_TO_UTC_DATETIME>>
<<date_format,FUN_TO_LOCAL_DATE>>
<<date_format,FUN_TO_LOCAL_DATETIME>>

Full list of functions found here: https://zappysys.com/links/?id=10103

Where date_format can be from any valid format specifiers from here https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

Examples:
select * from $ WITH (SRC='http://httpbin.org/get', HEADER='Authtorization: Basic <<Myuser12:MyP@ssW!,FUN_BASE64ENC>>')
select * from $ WITH (SRC='http://httpbin.org/get?id=<<FUN_NEW_GUID>>')
select * from $ WITH (SRC='<<c:/temp/path.txt,FUN_READFILE>>')
select * from $ WITH (SRC='http://httpbin.org/get?startdate=<<yyyy-MM-dd HH:mm:ss,FUN_TODAY>>')
select * from $ WITH (SRC='http://httpbin.org/get', HEADER='date:<<2012-01-01T00:00:00,FUN_TO_UTC_DATE>>')

SELECT * FROM $ WITH(SRC='mybucket/myservice/myfile_<<yyyy-MM-dd,FUN_TODAY>>.dat')  --read today's file

Functions Use Function(s) without FROM clause

This example shows how to call function(s) without using FROM clause.

SELECT RTRIM('trimmed     ') Col1, 
               DATE('now', '+5 day') Col2

Advanced Options Custom Search / Replace (Remove special characters)

This example shows how to use custom search/replace feature. This function is invoked before parser parse data so its useful for some unique scenario which needs removal of conflicting characters, renaming columns etc.

SELECT * FROM [data.row] 
WITH (
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/somefile.xml'
--SRC='zappysys-public-bucket/some*.xml'
DATA='
<data>
	<row><_id>1</_id><_name>AAA</_name></row>
	<row><_id>2</_id><_name>BBB</_name></row>
</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
,EnableCustomReplace=1
,SearchFor='<(/)?_(\w+)>--regex' --//or use --regex-ic (case-insensitive) or no suffix for plain text search.
,ReplaceWith='<$1$2>'
)

Hierarchy Filter / Flattening Flatten Hierarchy with Filter (XMLPath / Regular Expression / Regex)

This example shows how to query data using direct Filter expression and some other filter related settings. It also shows how to use regular expressions to filter nodes. <?xml version="1.0"?>
<!--
===========================
Filter Examples:
===========================
$.store.book //get all books for store
store.book //same as above.. just short way ($ is optional)
$.store.book[*].author //get all authors of all books
$.store.book[*] //get all books for store
$.store.book[2] //get 3rd book record
$.store.book[:2] //get first 2 books from the top
$.store.book[-2:] //get last 2 books
$.store.book[?(@@id=='bk101')] //Filter By Attribute: Get all books where id is bk102 (id is Attribute so use @@ rather than @)
$.store.book[?(@genre=='Computer')] //Filter By Node Value: Get all books where genre equals to 'Computer'
$..[?(@genre=='Computer')] //Filter By Node Value (search any level): Get all nodes where genre equals to 'Computer'
$.store.book[?(@.tag)] //filter all books with tag
$.store.books[?(@author=~ /Matthew|Kim/ )] //Using Regular Expression: get all books where author attribute contains 'Matthew' or 'Kim'
$.store.books[?(@author=~ /^Gambardella, Matthew|Ralls, Kim$/ )] //Using Regular Expression: get all books where author name is exactly 'Gambardella, Matthew' or 'Ralls, Kim'
$.store.books[?(@author=~ /^Gambardella/ )] //Using Regular Expression: get all books where author name starts with 'Gambardella'
$.store.books[?(@author=~ /Kim$/ )] //Using Regular Expression: get all books where author name ends with 'Kim'

-->
<store storeid="s1">
<book id="bk101">
<author>Gambardella, Matthew</author>
<title>XML Developer's Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2000-10-01</publish_date>
<description>An in-depth look at creating applications
with XML.</description>
</book>
<book id="bk102">
<author>Ralls, Kim</author>
<title>Midnight Rain</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2000-12-16</publish_date>
<description>A former architect battles corporate zombies,
an evil sorceress, and her own childhood to become queen
of the world.</description>
</book>
<book id="bk103">
<author>Corets, Eva</author>
<title>Maeve Ascendant</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2000-11-17</publish_date>
<description>After the collapse of a nanotechnology
society in England, the young survivors lay the
foundation for a new society.</description>
</book>
<book id="bk104">
<author>Corets, Eva</author>
<title>Oberon's Legacy</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2001-03-10</publish_date>
<description>In post-apocalypse England, the mysterious
agent known only as Oberon helps to create a new life
for the inhabitants of London. Sequel to Maeve
Ascendant.</description>
</book>
<book id="bk105">
<author>Corets, Eva</author>
<title>The Sundered Grail</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2001-09-10</publish_date>
<description>The two daughters of Maeve, half-sisters,
battle one another for control of England. Sequel to
Oberon's Legacy.</description>
</book>
<book id="bk106">
<author>Randall, Cynthia</author>
<title>Lover Birds</title>
<genre>Romance</genre>
<price>4.95</price>
<publish_date>2000-09-02</publish_date>
<description>When Carla meets Paul at an ornithology
conference, tempers fly as feathers get ruffled.</description>
</book>
<book id="bk107">
<author>Thurman, Paula</author>
<title>Splish Splash</title>
<genre>Romance</genre>
<price>4.95</price>
<publish_date>2000-11-02</publish_date>
<description>A deep sea diver finds true love twenty
thousand leagues beneath the sea.</description>
</book>
<book id="bk108">
<author>Knorr, Stefan</author>
<title>Creepy Crawlies</title>
<genre>Horror</genre>
<price>4.95</price>
<publish_date>2000-12-06</publish_date>
<description>An anthology of horror stories about roaches,
centipedes, scorpions and other insects.</description>
</book>
<book id="bk109">
<author>Kress, Peter</author>
<title>Paradox Lost</title>
<genre>Science Fiction</genre>
<price>6.95</price>
<publish_date>2000-11-02</publish_date>
<description>After an inadvertant trip through a Heisenberg
Uncertainty Device, James Salway discovers the problems
of being quantum.</description>
</book>
<book id="bk110">
<author>O'Brien, Tim</author>
<title>Microsoft .NET: The Programming Bible</title>
<genre>Computer</genre>
<price>36.95</price>
<publish_date>2000-12-09</publish_date>
<description>Microsoft's .NET initiative is explored in
detail in this deep programmer's reference.</description>
</book>
<book id="bk111">
<author>O'Brien, Tim</author>
<title>MSXML3: A Comprehensive Guide</title>
<genre>Computer</genre>
<price>36.95</price>
<publish_date>2000-12-01</publish_date>
<description>The Microsoft MSXML3 parser is covered in
detail, with attention to XML DOM interfaces, XSLT processing,
SAX and more.</description>
</book>
<book id="bk112">
<author>Galos, Mike</author>
<title>Visual Studio 7: A Comprehensive Guide</title>
<genre>Computer</genre>
<tag>tag1</tag>
<price>49.95</price>
<publish_date>2001-04-16</publish_date>
<description>Microsoft Visual Studio 7 is explored in depth,
looking at how Visual Basic, Visual C++, C#, and ASP+ are
integrated into a comprehensive development
environment.</description>
</book>
</store>

SELECT * FROM $
WITH (
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/somefile.xml'
--SRC='zappysys-public-bucket/some*.xml'
DATA='
<data parentCol1="p1" parentCol2="p2">
	<row><id>1</id><name>AAA</name></row>
	<row><id>2</id><name>BBB</name></row>
</data>'
,Filter='$.data.row[*]' -- for more information check this https://zappysys.com/blog/xmlpath-examples-expression-cheetsheet/
--,Filter='$.data.row[?(@name=='AAA')]' --//equal match example
--,Filter='$..[?(@name=='AAA')]' --//equal match example (scan all levels - parent or child)
--,Filter='$.data.row[?(@name=~/AAA|BBB/)]' --//regex match example

,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row)
,IncludeParentColumns='true'  --this will include any columns found above filtered node (e.g. rows).
,ParentColumnPrefix='p_'
)

Language Features Pivot Value (Columns to Rows)

This example shows how to pivot column to rows (e.g. Transform property name as row value). By default Tag name (i.e. Property) outputs as Column name but when your column name is Dynamic then you can use Pivot Feature to transform the data.

SELECT 
  Pivot_Name as ProjectName, 
  Pivot_Value_id as Id, 
  Pivot_Value_code as Code
FROM $
WITH (
Filter='$.data',
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/some*.xml'
--SRC='zappysys-public-bucket/somefile.xml'
DATA='
<data>
	<project1><id>1</id><code>AAA</code></project1>
	<project2><id>2</id><code>BBB</code></project2>
	<project3><id>3</id><code>CCC</code></project3>    
</data>',
EnablePivot='True'
)

Performance Handling Large Dataset / Avoid OutOfMemory Error (Using Disk for Query Engine)

Advanced Query can invokes ClientSide query Engine which means entire table is fetched in memory for processing causing OutOfMemory Exception (OOM). To avoid this error you can specify Disk based Temporary storage for query engine. Default is In memory storage.

SELECT * FROM $
WITH(

   Src='zappysys-public-bucket/cust-1.xml'
  ,TempStorageMode='Disk' --or 'Memory'
--OR
--,TempStorageMode=1 --//Memory=0, Disk=1
)

Language Features DateTime field Compare in WHERE / ORDER BY

This example shows how to use date time field in WHERE or / ORDER BY. Date Time field needs special handling. You have to use DATETIME function else it will do string compare rather true date time compare. Foe example WHERE OrderDate<=DATETIME('1997-12-31 00:00:00') -- OR WHERE OrderDate<=DATE('1997-12-31'). You must use DATETIME either left side or right side. Also must use 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd HH:mm:ss.fff' format (where fff is milliseconds)

SELECT * FROM "Root.value" 
WHERE OrderDate<=DATETIME('1997-12-31 00:00:00') -- OR use DATE('1997-12-31') DateTime column can be queried this way. You must wrap DATETIME function around it. Also must use 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss'  or 'yyyy-MM-dd HH:mm:ss.fff' format (where fff is milliseconds)
ORDER BY DATETIME(OrderDate)
WITH (SRC='zappysys-public-bucket/invoices.xml')

Language Features Group By / Limit / Order By

This example shows how to use GROUP BY / ORDER BY and LIMIT clauses.

SELECT 
  Country AS Invoice_Country
, SUM(UnitPrice * Quantity) AS Invoice_Total
FROM "Root.value" 
WHERE Discount > 0
--AND OrderDate<=DATETIME('1997-12-31 00:00:00') -- OR use DATE('1997-12-31') DateTime column can be queried this way. You must wrap DATETIME function around it. Also must use 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss'  or 'yyyy-MM-dd HH:mm:ss.fff' format (where fff is milliseconds)
GROUP BY Country 
HAVING SUM(UnitPrice * Quantity) > 1000
ORDER BY Invoice_Total DESC --,DATETIME(OrderDate)
LIMIT 3
WITH (SRC='zappysys-public-bucket/invoices.xml')

Language Features Case Statement

This example shows how to write CASE statement to select / output different value based on multiple conditions.

SELECT 
  name 
, CASE id 
 WHEN 1 THEN 1+1 
 WHEN 2 THEN 2+2 
 ELSE 0 
 END ThisIsCaseColumn 
FROM [data.row] 
WITH (
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/some*.xml'
--SRC='zappysys-public-bucket/somefile.xml'
DATA='
<data>
	<row><id>1</id><name>AAA</name></row>
	<row><id>2</id><name>BBB</name></row>
</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
)

Language Features SELECT INTO (Temporary Table) / Multi Statements

This example shows how to use SELECT INTO statement to save result into temporary table and use it later in the same batch. Temp table is indicated by # prefix (e.g. #tmp). This example also uses multiple statements in the same batch. If you have multiple results in the same batch (i.e. SELECT * FROM ) then only last result is returned and others results are discarded. Each statement must be separated by semi-colon ( ; )

SELECT * INTO #tmp FROM [data.row] 
WITH (
--enter path in SRC or use static value in DATA
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/some*.xml'
--SRC='zappysys-public-bucket/somefile.xml'
DATA='
<data>
	<row><id>1</id><name>AAA</name></row>
	<row><id>2</id><name>BBB</name></row>
    <row><id>3</id><name>CCC</name></row>
</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
);

--//second statement in the same batch 
select * from #tmp 
where id > 1
--AND SomeDateColumn=DATETIME('2012-12-31 00:00:00')  --DateTime column can be queried this way... Must use 'yyyy-MM-dd HH:mm:ss' format
;           

Language - JOIN Statement using SELECT INTO / Multi Statements

This example shows how to use SELECT INTO statement and use of multiple statements in the same batch. Output from only last SELECT is returned if you have multiple SELECT * FROM in the same batch. Each statement must be separated by semi-colon ( ; )

SELECT * INTO #cust FROM [data.row] 
WITH (
--enter path in SRC or use static value in DATA
--SRC='zappysys-public-bucket/api/customers.xml'
--SRC='zappysys-public-bucket/cust*.xml'
--SRC='zappysys-public-bucket/customers.xml'
DATA='
<data>
	<row><customerid>1</customerid><name>AAA</name></row>
	<row><customerid>2</customerid><name>BBB</name></row>
    <row><customerid>3</customerid><name>CCC</name></row>
</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
);

SELECT * INTO #ord FROM [data.row] 
WITH (
DATA='
<data>
	<row><orderid>1000</orderid><customerid>1</customerid><orderdate>2012-01-01T00:00:00</orderdate><total>2000.50</total></row>
	<row><orderid>1001</orderid><customerid>1</customerid><orderdate>2012-01-02T00:00:00</orderdate><total>1000.50</total></row>
    <row><orderid>1002</orderid><customerid>2</customerid><orderdate>2013-01-01T00:00:00</orderdate><total>5000.70</total></row>
    <row><orderid>1003</orderid><customerid>2</customerid><orderdate>2013-01-02T00:00:00</orderdate><total>6000.70</total></row>
</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
);

--//second statement in the same batch 
SELECT c.customerid,o.orderid,o.orderdate,o.total 
FROM #cust c
INNER JOIN #ord o ON c.customerid=o.customerid
--LEFT OUTER JOIN #ord o ON c.customerid=o.customerid
--WHERE o.orderdate='2012-01-01 00:00:00' --for datetime column format must be queried in 'yyyy-MM-dd HH:mm:ss' format only
--To Use greater than less than must need to use DATETIME function (e.g. WHERE o.OrderDate<=DATETIME('1997-12-31 23:12:59')
;           

Language Features UNION ALL / UNION Statement

This example shows how to write UNION or UNION ALL statement to combine results from multiple SQL queries. To remove duplicate rows from result use UNION rather than UNION ALL.

SELECT * into #tbl1 FROM [data.row] 
WITH (
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/some*.xml'
--SRC='zappysys-public-bucket/somefile.xml'
DATA='
<data>
	<row><id>1</id><name>AAA</name></row>
	<row><id>2</id><name>BBB</name></row>
</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
);

SELECT * into #tbl2 FROM [data.row] 
WITH (
--enter path in SRC or use static value in DATA
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/some*.xml'
--SRC='zappysys-public-bucket/somefile.xml'
DATA='
<data>
	<row><id>1</id><name>CCC</name></row>
	<row><id>2</id><name>DDD</name></row>
</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
);

select * from #tbl1
UNION ALL
select * from #tbl2;

Language Features Basic SQL queries

Basic query examples.

--Whenever Possible try to write Simple Query. When you use WHERE / GROUP BY / ORDER BY Expressions. Driver may invoke Client side processing if server side doesnt support that construct.
--For comment use line comment with two dash (e.g. --my comment) or use block comment for multi line comment (e.g. /*my comment*/ )
--Any query must contain at least two required parts
--data source (or table) names and a list of column names
--column names come first (preceded by SELECT keyword)
--table names follow (preceded by FROM keyword)

--Here is the query that returns all rows with all columns (* stands for all columns) from 'Accounts' table
SELECT * FROM Accounts

--Use quoted identifiers when you have space or other character in names (e.g. Column or Table name contains space)
SELECT ID, "Account Name" FROM "Closed Accounts"
SELECT ID, [Account Name] FROM [Closed Accounts]

--returns all rows from 'Accounts' table, each row will have Id and Amount columns
SELECT Id, Amount FROM Accounts

--returns only those rows of 'Accounts' table that meet next condition: Amount is greater or equal to 1000.0
SELECT Id, Amount FROM Accounts WHERE Amount >= 1000.0

--returns only those rows of 'Accounts' table that meet next condition: Amount is greater than 1000 or DepartamentID is equal to 123 
SELECT Id, Amount FROM Accounts WHERE (Amount > 1000.0 OR DepartamentID = 123)

--returns only those rows of 'Accounts' table that meet next condition: DepartamentID is equal to 1, 101 or 202 
SELECT Id, Amount FROM Accounts WHERE DepartamentID IN (1, 101, 202)

--returns only those rows of 'Accounts' table that meet next condition: DepartamentID is equal to 321 and Amount is less than 100 
SELECT Id, Amount FROM Accounts WHERE DepartamentID = 321 AND Amount < 100

--returns only those rows of 'Persons' table that meet next condition: Name column value starts with 'John'
SELECT * FROM Person WHERE Name LIKE 'John%'

--case statement
SELECT 
 FirstName,
 LastName,
 CASE Country 
  WHEN 'US' THEN 'NA' 
  WHEN 'Canada' THEN 'NA'
  WHEN 'India' THEN 'Asia'
  WHEN 'China' THEN 'Asia'
  ELSE 'Unknown' 
 END ColRegion 
 FROM Person 

--escaping string values (use of /n /r /t /v allowed)
SELECT * FROM MyTable WITH( Body=@'Line1/nLine2')

---

Copyrights reserved. ZappySys LLC.