(:Tally to DBF:)
(: ConnectionString to be specified in the RTSXQueryFreemium.ini file :)
(:
ConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\RTSXQueryFreemiumV1.0;Extended Properties="dBASE IV;User ID=Admin;Password=;"'
:)


(:XQuery output:)
(:COL1 - VCHID:)
(:COL2 - VCHTYPE:)
(:COL3 - VCHDATE:)
(:COL4 - LEDGERS:)
(:COL5 - LEDGERAMT:)
(:COL6 - DRCR:)

(:XQuery begins here:)
<output>
<createtable>CREATE TABLE RTable1.dbf (VCHID CHAR(15),[VCHTYPE] CHAR(50),[VCHDATE] char(50),[LEDGER] char(60),[LEDGERAMT] FLOAT,[DRCR] char(15))</createtable>
{
for
$row  in (ENVELOPE/BODY/IMPORTDATA/REQUESTDATA/TALLYMESSAGE/VOUCHER),
$ledger at $pos in distinct-values($row/descendant::LEDGERNAME)
let
$ledgeramt      := sum($row/descendant::LEDGERNAME[.=$ledger]/parent::node()/AMOUNT),
$fieldnames:= ("VCHID","VCHTYPE","VCHDATE","LEDGER","LEDGERAMT","DRCR"),
$fieldvalues:=($row/parent::node()/@MASTERID,$row/@VCHTYPE,$row/DATE,$ledger,if (fn:exists($ledgeramt)) then abs($ledgeramt) else (0),if ($ledgeramt<0) then "Dr" else "Cr")
return
	sslib:sql-insert('RTable1',$fieldnames,$fieldvalues)
}	
</output>