kbmMW Features #4 – SQL, XML, JSON, VALUES() hand in hand

This blog post will be about the TkbmMWMemSQL component, which is a descendant of TkbmMemSQL.

I will in this post mostly take advantage of “learning by example”. So study the given examples to see a subset of all the nice capabilities available in kbmMW.

Users of kbmMemTable may recognize the component TkbmMemSQL. It is a component that presents SQL capabilities for kbmMemTable datasets.

TkbmMemSQL supports quite a big subset of the ANSI SQL syntax, including SELECT, INSERT, DELETE, UPDATE along with full DDL support like CREATE TABLE, ALTER TABLE, CREATE INDEX and so forth. The following are all valid SQL statements that can be used in both TkbmMemSQL and TkbmMWMemSQL

So it is already quite capable.

What TkbmMWMemSQL brings to the table is additional advanced features like being able to query XML, CSV and JSON documents from file and inline as part of the SQL statement itself, in addition to the VALUES(..) feature where a number of comma and semicolon separated values can be delivered directly to the SELECT statement as field and row values. VALUES(..) will be available in the upcoming release of kbmMW, while the remaining features already are available in current version.

The following are examples of advanced SQL features, showing how to work with XML and JSON data and files, both as input and output.

Some of the examples includes a -> syntax as part of the naming of a result field. This is a way to force the resulting field to be of a particular type, rather than deducing the field type based on the results of the query statement. The datatype identifiers includes LARGEINT, BIGINT, INT64, INT, VARCHAR2, VARCHAR, CHAR, BOOL, BOOLEAN, AUTOINC, FLOAT, DOUBLE, NUMERIC, REAL, DATETIME, TIMESTAMP, DATE, TIME, BLOB, GRAPHIC, CLOB, TEXT, CURRENCY, WORD, WIDEMEMO, MEMO and GUID, and can take an optional size (n). For example VARCHAR2(100).

The SUBSET keyword can be used to select a subset of the data structure for inclusion in the query. It is in its purpose similar to XPath probably recognized by those who have worked extensively with XML document parsing. The syntax is a regular expression matching a path to interesting nodes of a XML or JSON document to be included as the query’s source. Hence /data/.* means that all nodes under the data node which is found in top of the document. Only the fantasy sets the limit for how to filter the documents to get the relevant data.

 

 

Author: kimbomadsen

4 thoughts on “kbmMW Features #4 – SQL, XML, JSON, VALUES() hand in hand

  1. Looks great!!, Is this also also Capable to Count() XML nodes that has certain attribute value? I was thinking to have something like XQuery/XPath does. ¿Also SUM, Max, MIN?

    1. Yes. Use the subset to select nodes to look for and the whereclause to match attribute values via the xmlattr helper function.

    1. It is already in the latest kbmMW version, except for the VALUES(..) syntax which comes with the next release.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.