(:COL1 - ID:)
(:COL2 - VOUCHERTYPE:)
(:COL3 - VCHDATE:)
(:COL4 - VCHNUMBER:)
(:COL5 - PARTYLEDGERNAME:)
(:COL6 - STOCKITEMNAME:)
(:COL7 - BILLEDQTY:)
(:COL8 - RATE:)
(:COL9 - AMOUNT:)


(:XQuery begins here:)
<output>
<createtable>CREATE TABLE [sheet1$] ([ID] CHAR(255),[VOUCHERTYPE] CHAR(255),[VCHDATE] char(255),[VCHNUMBER] char(255),[PARTYNAME] char(255),[STOCKITEM] char(255),[QTY] FLOAT,[RATE] char(255),[AMOUNT] FLOAT)</createtable>
{
for 
$row  in (ENVELOPE/BODY/IMPORTDATA/REQUESTDATA/TALLYMESSAGE/VOUCHER),
$stockitem at $pos in ($row/ALLINVENTORYENTRIES.LIST/STOCKITEMNAME)
let
$qty := ($row/ALLINVENTORYENTRIES.LIST/number(substring-before(normalize-space(data(ACTUALQTY)),' ')))[$pos],
$rate := ($row/ALLINVENTORYENTRIES.LIST/RATE)[$pos],
$amount := ($row/ALLINVENTORYENTRIES.LIST/AMOUNT)[$pos],
$fieldnames:= ("ID","VOUCHERTYPE","VCHDATE","VCHNUMBER","PARTYNAME","STOCKITEM","QTY","RATE","AMOUNT"),
$fieldvalues:=($row/parent::node()/@MASTERID,$row/@VCHTYPE,$row/DATE,$row/VOUCHERNUMBER,$row/PARTYLEDGERNAME,$stockitem,if (fn:exists($qty)) then data($qty) else "",if (fn:exists($rate)) then data($rate) else "",if (fn:exists($amount)) then data($amount) else 0)
return
    sslib:sql-insert('[sheet1$]',$fieldnames,$fieldvalues)
}	
</output>