KI_SQL_XML
| Argument | Enumeration | Purpose |
|---|---|---|
| handle | Handle | |
| sql$ | SQL statement | |
| xsl$ | Include xsl URL | |
| SYM(buf$) | Buffer to hold result | |
| status | KDB_ERROR_ENUM | Return status |
| len | Length of result buffer |
KI_SQL_XML
This command gives access to the functionality of the sql utility. Given an SQL statement it will perform the equivalent of a KI_PREPARE, KI_EXECUTE and enough KI_FETCHs to return all the data. It is intended to be used to convert table data to XML describing the data. If the symbol of the return buffer is negative then, if necessary, the buffer will be re-dimensioned large enough to accommodate the requested information. This allows the use of XML as a transport for arbitrary database information.
Like KI_PREPARE if a negative handle number is passed then functionallity is restricted to SELECT.
Note: A KI_SQL_XML call must be followed by a KI_CLOSE before the handle can be re-used to parse another SQL statement or open a table.
To output a few rows from a table as XML:
: DIM sql$1024, xsl$1024, buffer$(1)1
: DIM ki_status AS KDB_ERROR_ENUM, ki_connect, ki_handle, len
: CALL KI_ALLOC_CONNECT 0 TO ki_connect, ki_status
: IF (ki_status <> 0) THEN 'error("ALLOC_CONNECT")
: CALL KI_CONNECT "KDB", ki_connect, "WEB", " ", " " TO ki_status
: IF (ki_status <> 0) THEN 'error("CONNECT")
: CALL KI_ALLOC_HANDLE 0, ki_connect TO ki_handle, ki_status
: IF (ki_status <> 0) THEN 'error("ALLOC_HANDLE")
: sql$ = "SELECT TOP 3 MAGIC,COMMAGIC,CREATED FROM userlist WHERE MAGIC > 4100 FOR XML AUTO,XMLData"
: xsl$ = "/testdir/test.xsl"
: CALL KI_SQL_XML ki_handle, sql$, xsl$, -SYM(buffer$()) TO ki_status, len
: IF (ki_status <> 0) THEN 'error("SQL")
: PRINT STR(buffer$(),, len)
END
Could produce the following output. Note that an alternative could be to use the XML_TEXT command.
<?xml version="1.0" ?> <?xml-stylesheet type="text/xsl" href="/testdir/test.xsl" ?> <root> <Schema xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes"> <ElementType name="userlist" content="textOnly" model="closed"> <AttributeType name="MAGIC" dt:type="i4" /> <AttributeType name="COMMAGIC" dt:type="i3" /> <AttributeType name="CREATED" dt:type="date" /> <attribute type="MAGIC" required="no" /> <attribute type="COMMAGIC" required="no" /> <attribute type="CREATED" required="no" /> </ElementType> </Schema> <userlist MAGIC="4101" COMMAGIC="0" CREATED="2000-07-23" /> <userlist MAGIC="4102" COMMAGIC="67.76" CREATED="2000-07-23" /> <userlist MAGIC="4103" COMMAGIC="44" CREATED="2000-07-24" /> </root>
Compatibility
Prior to KCML 6.40 this was named KI_SQL.