Exploring RTS-XQuery Freemium

•    RTS-XQuery Extensions
•    Tally / XML to database

RTS-XQuery Extensions

RTS-XQuery supports XQuery updates to relational database tables from within an XQuery by providing sslib:sql-insert().

Function : sslib:sql-insert()

Syntax :-

declare updating function sslib:sql-insert(
        table as xs:string,
        columns as xs:string*,
        values as items()*
        ) external;

where:

- table is the database table in which the record is to be inserted.
- columns is a sequence of columns of the database table in values are to be inserted.
- values is a sequence of the values that are to be inserted into the specified columns.

Example 1:-

sslib:sql-insert('Rtable1','NAME','ABC Company Ltd')

Remarks:-
The above inserts only one value into the NAME field of table 'Rtable1'

Example 2:-

sslib:sql-insert('Rtable2,$field-names,$field-values)

Remarks:-
The above inserts a sequence of values as specified by variable $field-values into the columns $field-names of table 'RTable2'

Tally / XML to database

By default, RTS-XQuery Freemium transfers data to MS-Excel. To redirect output to other databases, follow the underneath steps:-

Step-by-step Instructions
1) Start Tally Software and open any company which contains some vouchers.
2) Minimize Tally software.
3) Start RTS-XQuery Freemium Software.
4) Next, specify the Period for which you want to retrieve the data from Tally.
5) Next, select the sample XQuery-file  "xquery-daybook-vouchers-financial-details-tally-to-dbf.xq"
6) Next, click on the option "Database". (The default connection string for DBASE will appear automatically)
7) Click on the START button to start processing.

If everything goes fine, then records will be imported into "RTable1.dbf" in  "C:\RTSXQuery-Freemium" folder. You can open this file in Foxpro / DBase or you can even open it with MS-Excel.

1) Follow the steps 1 to 4 as given above (Sample 1).
2) Next, select the sample XQuery-file "tally-to-relational-database.xq"
3) Next, follow remaining steps 6 to 7 as given above (Sample 1).

If everything goes fine, then records will be imported into the following tables :-
a) mvoucher.dbf  - main voucher table
b) ledvouch,dbf   - child voucher table which contains ledger details
c) invvouch.dbf    - child voucher table which contains Inventory details
 

Tally to other Databases (Guidelines)

Point 1
Specify the "Connection String" to connect to your database in the RTSXQueryFreemium.ini file. You can use any text editor to open/modify the RTSXQueryFreemium.ini file.

Remarks:-
a) By default, the Connection String in the RTSXQueryFreemium.ini file contains value to connect to DBASE.
b) For help on Connection Strings, refer http://www.connectionstrings.com. 

Point 2
Manually create the necessary tables in your database to which the XQuery result is to be sent.

Remarks:-
a) Alternatively, use the <createtable> tag in the Query-file to create tables at run-time. Refer sample query-file provided with the software viz: xquery-daybook-vouchers-financial-details-tally-to-dbf.xq
b) The XQuery result is sent to your database tables using sslib:sql-insert()

Point 3
While using RTS-XQuery Freemium, click the option  "Database" (By default, the option "Excel" is selected).