How to create templated XML document using Mysql as datasource

Describe the issue/error/question

Use Case To generate Sales Orders in XML file with a predefined xml template using mysql as data source
We are using a accounting software Tally which supports xml import of data using the templated xml file. Our requirement is to connect the sales crm (Vtiger self hosted open source) system to N8N (using mysql) to generate the XML file according to the xml template (im using set node to feed the template) which will give the sales order xml document for each order ID
Problem:
For single line items the workflow seems to work, but in case where more than one line items are involved (array of products) the workflow doesn’t work. i tried using item list node to combine all items as one. but it didn’t work.

What is the error message (if any)?

Please share the workflow

Share the output returned by the last node

Information on your n8n setup

  • **n8n version:**0.204.0
  • Database you’re using (default: SQLite):
  • Running n8n with the execution process [own(default), main]:
  • Running n8n via [Docker, npm, n8n.cloud, desktop app]: npm

Hi @Shoaib, welcome to the community :tada:

Perhaps you can share some example data you’d like to convert into an XML document? It seems the Set nodes from your workflow already use some static data, but I guess that’s not what’s coming from MySQL?

I’ve also provided an example workflow recently creating an XML feed and consisting of multiple items which you might find helpful when getting started:

Hello @MutedJam,

this is the template / static data using which i want to generate an xml document. The template in already in xml and the end result should consist of the dynamic value in the template, for getting the dynamic data im using mysql.

XML TEMPLATE

Import Data Vouchers ABC Company ${street address} ${address line2} ${address line3} ${street address} ${street address} ${street address} -1 ${s.o timestamp} f5dd5c53-f8d1-4e96-b7d5-df0aa049c1c8-000234c8 Regular Regular ${state} Sales Order ${operator name} ${country} ${client gstin} ${state} ${Client Name} ${Client Name} ${client PO} ${Client Name} ${zipcode} ${client gstin} ${Client Name} ${zipcode} ${state} ${SO Number} ${Client Name} Invoice Voucher View ${Client Name} ${country} No No No No No No No No 20221203 No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No Yes No No No No No 292742 144584 192831146688816 $(item name desc}
   </BASICUSERDESCRIPTION.LIST>
   <STOCKITEMNAME>${item sku}</STOCKITEMNAME>
   <ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
   <ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE>
   <ISAUTONEGATE>No</ISAUTONEGATE>
   <ISCUSTOMSCLEARANCE>No</ISCUSTOMSCLEARANCE>
   <ISTRACKCOMPONENT>No</ISTRACKCOMPONENT>
   <ISTRACKPRODUCTION>No</ISTRACKPRODUCTION>
   <ISPRIMARYITEM>No</ISPRIMARYITEM>
   <ISSCRAP>No</ISSCRAP>
   <RATE>${unit price}</RATE>
   <AMOUNT>${current line item total amount}</AMOUNT>
   <ACTUALQTY> ${quantity}</ACTUALQTY>
   <BILLEDQTY> ${quantity}</BILLEDQTY>
   <BATCHALLOCATIONS.LIST>
    <BATCHNAME>Primary Batch</BATCHNAME>
    <INDENTNO/>
    <ORDERNO>2808</ORDERNO>
    <TRACKINGNUMBER/>
    <DYNAMICCSTISCLEARED>No</DYNAMICCSTISCLEARED>
    <AMOUNT>${current line item total amount}</AMOUNT>
    <ACTUALQTY>${quantity}</ACTUALQTY>
    <BILLEDQTY>${quantity}</BILLEDQTY>
    <ORDERDUEDATE JD="44897" P="3-Dec-22">3-Dec-22</ORDERDUEDATE>
    <ADDITIONALDETAILS.LIST>        </ADDITIONALDETAILS.LIST>
    <VOUCHERCOMPONENTLIST.LIST>        </VOUCHERCOMPONENTLIST.LIST>
   </BATCHALLOCATIONS.LIST>
   <ACCOUNTINGALLOCATIONS.LIST>
    <OLDAUDITENTRYIDS.LIST TYPE="Number">
     <OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS>
    </OLDAUDITENTRYIDS.LIST>
    <LEDGERNAME>SALES INTERSTATE GST ( 18% )</LEDGERNAME>
    <GSTCLASS/>
    <ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
    <LEDGERFROMITEM>No</LEDGERFROMITEM>
    <REMOVEZEROENTRIES>No</REMOVEZEROENTRIES>
    <ISPARTYLEDGER>No</ISPARTYLEDGER>
    <ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE>
    <ISCAPVATTAXALTERED>No</ISCAPVATTAXALTERED>
    <ISCAPVATNOTCLAIMED>No</ISCAPVATNOTCLAIMED>
    <AMOUNT>${current line item total amount}</AMOUNT>
    <SERVICETAXDETAILS.LIST>        </SERVICETAXDETAILS.LIST>
    <BANKALLOCATIONS.LIST>        </BANKALLOCATIONS.LIST>
    <BILLALLOCATIONS.LIST>        </BILLALLOCATIONS.LIST>
    <INTERESTCOLLECTION.LIST>        </INTERESTCOLLECTION.LIST>
    <OLDAUDITENTRIES.LIST>        </OLDAUDITENTRIES.LIST>
    <ACCOUNTAUDITENTRIES.LIST>        </ACCOUNTAUDITENTRIES.LIST>
    <AUDITENTRIES.LIST>        </AUDITENTRIES.LIST>
    <INPUTCRALLOCS.LIST>        </INPUTCRALLOCS.LIST>
    <DUTYHEADDETAILS.LIST>        </DUTYHEADDETAILS.LIST>
    <EXCISEDUTYHEADDETAILS.LIST>        </EXCISEDUTYHEADDETAILS.LIST>
    <RATEDETAILS.LIST>        </RATEDETAILS.LIST>
    <SUMMARYALLOCS.LIST>        </SUMMARYALLOCS.LIST>
    <STPYMTDETAILS.LIST>        </STPYMTDETAILS.LIST>
    <EXCISEPAYMENTALLOCATIONS.LIST>        </EXCISEPAYMENTALLOCATIONS.LIST>
    <TAXBILLALLOCATIONS.LIST>        </TAXBILLALLOCATIONS.LIST>
    <TAXOBJECTALLOCATIONS.LIST>        </TAXOBJECTALLOCATIONS.LIST>
    <TDSEXPENSEALLOCATIONS.LIST>        </TDSEXPENSEALLOCATIONS.LIST>
    <VATSTATUTORYDETAILS.LIST>        </VATSTATUTORYDETAILS.LIST>
    <COSTTRACKALLOCATIONS.LIST>        </COSTTRACKALLOCATIONS.LIST>
    <REFVOUCHERDETAILS.LIST>        </REFVOUCHERDETAILS.LIST>
    <INVOICEWISEDETAILS.LIST>        </INVOICEWISEDETAILS.LIST>
    <VATITCDETAILS.LIST>        </VATITCDETAILS.LIST>
    <ADVANCETAXDETAILS.LIST>        </ADVANCETAXDETAILS.LIST>
   </ACCOUNTINGALLOCATIONS.LIST>
   <DUTYHEADDETAILS.LIST>       </DUTYHEADDETAILS.LIST>
   <SUPPLEMENTARYDUTYHEADDETAILS.LIST>       </SUPPLEMENTARYDUTYHEADDETAILS.LIST>
   <TAXOBJECTALLOCATIONS.LIST>       </TAXOBJECTALLOCATIONS.LIST>
   <REFVOUCHERDETAILS.LIST>       </REFVOUCHERDETAILS.LIST>
   <EXCISEALLOCATIONS.LIST>       </EXCISEALLOCATIONS.LIST>
   <EXPENSEALLOCATIONS.LIST>       </EXPENSEALLOCATIONS.LIST>
  </ALLINVENTORYENTRIES.LIST>
  <!--Line item 1 end-->
  <!--Line item 2 begin-->
  <ALLINVENTORYENTRIES.LIST>
    <BASICUSERDESCRIPTION.LIST TYPE="String">
     <BASICUSERDESCRIPTION>$(item name desc}</BASICUSERDESCRIPTION>

    </BASICUSERDESCRIPTION.LIST>
    <STOCKITEMNAME>${item sku}</STOCKITEMNAME>
    <ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
    <ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE>
    <ISAUTONEGATE>No</ISAUTONEGATE>
    <ISCUSTOMSCLEARANCE>No</ISCUSTOMSCLEARANCE>
    <ISTRACKCOMPONENT>No</ISTRACKCOMPONENT>
    <ISTRACKPRODUCTION>No</ISTRACKPRODUCTION>
    <ISPRIMARYITEM>No</ISPRIMARYITEM>
    <ISSCRAP>No</ISSCRAP>
    <RATE>${unit price}</RATE>
    <AMOUNT>${current line item total amount}</AMOUNT>
    <ACTUALQTY> ${quantity}</ACTUALQTY>
    <BILLEDQTY> ${quantity}</BILLEDQTY>
    <BATCHALLOCATIONS.LIST>
     <BATCHNAME>Primary Batch</BATCHNAME>
     <INDENTNO/>
     <ORDERNO>2808</ORDERNO>
     <TRACKINGNUMBER/>
     <DYNAMICCSTISCLEARED>No</DYNAMICCSTISCLEARED>
     <AMOUNT>${current line item total amount}</AMOUNT>
     <ACTUALQTY>${quantity}</ACTUALQTY>
     <BILLEDQTY>${quantity}</BILLEDQTY>
     <ORDERDUEDATE JD="44897" P="3-Dec-22">3-Dec-22</ORDERDUEDATE>
     <ADDITIONALDETAILS.LIST>        </ADDITIONALDETAILS.LIST>
     <VOUCHERCOMPONENTLIST.LIST>        </VOUCHERCOMPONENTLIST.LIST>
    </BATCHALLOCATIONS.LIST>
    <ACCOUNTINGALLOCATIONS.LIST>
     <OLDAUDITENTRYIDS.LIST TYPE="Number">
      <OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS>
     </OLDAUDITENTRYIDS.LIST>
     <LEDGERNAME>SALES INTERSTATE GST ( 18% )</LEDGERNAME>
     <GSTCLASS/>
     <ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
     <LEDGERFROMITEM>No</LEDGERFROMITEM>
     <REMOVEZEROENTRIES>No</REMOVEZEROENTRIES>
     <ISPARTYLEDGER>No</ISPARTYLEDGER>
     <ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE>
     <ISCAPVATTAXALTERED>No</ISCAPVATTAXALTERED>
     <ISCAPVATNOTCLAIMED>No</ISCAPVATNOTCLAIMED>
     <AMOUNT>${current line item total amount}</AMOUNT>
     <SERVICETAXDETAILS.LIST>        </SERVICETAXDETAILS.LIST>
     <BANKALLOCATIONS.LIST>        </BANKALLOCATIONS.LIST>
     <BILLALLOCATIONS.LIST>        </BILLALLOCATIONS.LIST>
     <INTERESTCOLLECTION.LIST>        </INTERESTCOLLECTION.LIST>
     <OLDAUDITENTRIES.LIST>        </OLDAUDITENTRIES.LIST>
     <ACCOUNTAUDITENTRIES.LIST>        </ACCOUNTAUDITENTRIES.LIST>
     <AUDITENTRIES.LIST>        </AUDITENTRIES.LIST>
     <INPUTCRALLOCS.LIST>        </INPUTCRALLOCS.LIST>
     <DUTYHEADDETAILS.LIST>        </DUTYHEADDETAILS.LIST>
     <EXCISEDUTYHEADDETAILS.LIST>        </EXCISEDUTYHEADDETAILS.LIST>
     <RATEDETAILS.LIST>        </RATEDETAILS.LIST>
     <SUMMARYALLOCS.LIST>        </SUMMARYALLOCS.LIST>
     <STPYMTDETAILS.LIST>        </STPYMTDETAILS.LIST>
     <EXCISEPAYMENTALLOCATIONS.LIST>        </EXCISEPAYMENTALLOCATIONS.LIST>
     <TAXBILLALLOCATIONS.LIST>        </TAXBILLALLOCATIONS.LIST>
     <TAXOBJECTALLOCATIONS.LIST>        </TAXOBJECTALLOCATIONS.LIST>
     <TDSEXPENSEALLOCATIONS.LIST>        </TDSEXPENSEALLOCATIONS.LIST>
     <VATSTATUTORYDETAILS.LIST>        </VATSTATUTORYDETAILS.LIST>
     <COSTTRACKALLOCATIONS.LIST>        </COSTTRACKALLOCATIONS.LIST>
     <REFVOUCHERDETAILS.LIST>        </REFVOUCHERDETAILS.LIST>
     <INVOICEWISEDETAILS.LIST>        </INVOICEWISEDETAILS.LIST>
     <VATITCDETAILS.LIST>        </VATITCDETAILS.LIST>
     <ADVANCETAXDETAILS.LIST>        </ADVANCETAXDETAILS.LIST>
    </ACCOUNTINGALLOCATIONS.LIST>
    <DUTYHEADDETAILS.LIST>       </DUTYHEADDETAILS.LIST>
    <SUPPLEMENTARYDUTYHEADDETAILS.LIST>       </SUPPLEMENTARYDUTYHEADDETAILS.LIST>
    <TAXOBJECTALLOCATIONS.LIST>       </TAXOBJECTALLOCATIONS.LIST>
    <REFVOUCHERDETAILS.LIST>       </REFVOUCHERDETAILS.LIST>
    <EXCISEALLOCATIONS.LIST>       </EXCISEALLOCATIONS.LIST>
    <EXPENSEALLOCATIONS.LIST>       </EXPENSEALLOCATIONS.LIST>
   </ALLINVENTORYENTRIES.LIST>
  
  <!--Line item 2 end-->
  
  <SUPPLEMENTARYDUTYHEADDETAILS.LIST>      </SUPPLEMENTARYDUTYHEADDETAILS.LIST>
  <EWAYBILLERRORLIST.LIST>      </EWAYBILLERRORLIST.LIST>
  <IRNERRORLIST.LIST>      </IRNERRORLIST.LIST>
  <INVOICEDELNOTES.LIST>      </INVOICEDELNOTES.LIST>
  <INVOICEORDERLIST.LIST>      </INVOICEORDERLIST.LIST>
  <INVOICEINDENTLIST.LIST>      </INVOICEINDENTLIST.LIST>
  <ATTENDANCEENTRIES.LIST>      </ATTENDANCEENTRIES.LIST>
  <ORIGINVOICEDETAILS.LIST>      </ORIGINVOICEDETAILS.LIST>
  <INVOICEEXPORTLIST.LIST>      </INVOICEEXPORTLIST.LIST>
  <LEDGERENTRIES.LIST>
   <OLDAUDITENTRYIDS.LIST TYPE="Number">
    <OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS>
   </OLDAUDITENTRYIDS.LIST>
   <LEDGERNAME>${Client Name}</LEDGERNAME>
   <GSTCLASS/>
   <ISDEEMEDPOSITIVE>Yes</ISDEEMEDPOSITIVE>
   <LEDGERFROMITEM>No</LEDGERFROMITEM>
   <REMOVEZEROENTRIES>No</REMOVEZEROENTRIES>
   <ISPARTYLEDGER>Yes</ISPARTYLEDGER>
   <ISLASTDEEMEDPOSITIVE>Yes</ISLASTDEEMEDPOSITIVE>
   <ISCAPVATTAXALTERED>No</ISCAPVATTAXALTERED>
   <ISCAPVATNOTCLAIMED>No</ISCAPVATNOTCLAIMED>
   <AMOUNT>${Total Amount}</AMOUNT>
   <SERVICETAXDETAILS.LIST>       </SERVICETAXDETAILS.LIST>
   <BANKALLOCATIONS.LIST>       </BANKALLOCATIONS.LIST>
   <BILLALLOCATIONS.LIST>       </BILLALLOCATIONS.LIST>
   <INTERESTCOLLECTION.LIST>       </INTERESTCOLLECTION.LIST>
   <OLDAUDITENTRIES.LIST>       </OLDAUDITENTRIES.LIST>
   <ACCOUNTAUDITENTRIES.LIST>       </ACCOUNTAUDITENTRIES.LIST>
   <AUDITENTRIES.LIST>       </AUDITENTRIES.LIST>
   <INPUTCRALLOCS.LIST>       </INPUTCRALLOCS.LIST>
   <DUTYHEADDETAILS.LIST>       </DUTYHEADDETAILS.LIST>
   <EXCISEDUTYHEADDETAILS.LIST>       </EXCISEDUTYHEADDETAILS.LIST>
   <RATEDETAILS.LIST>       </RATEDETAILS.LIST>
   <SUMMARYALLOCS.LIST>       </SUMMARYALLOCS.LIST>
   <STPYMTDETAILS.LIST>       </STPYMTDETAILS.LIST>
   <EXCISEPAYMENTALLOCATIONS.LIST>       </EXCISEPAYMENTALLOCATIONS.LIST>
   <TAXBILLALLOCATIONS.LIST>       </TAXBILLALLOCATIONS.LIST>
   <TAXOBJECTALLOCATIONS.LIST>       </TAXOBJECTALLOCATIONS.LIST>
   <TDSEXPENSEALLOCATIONS.LIST>       </TDSEXPENSEALLOCATIONS.LIST>
   <VATSTATUTORYDETAILS.LIST>       </VATSTATUTORYDETAILS.LIST>
   <COSTTRACKALLOCATIONS.LIST>       </COSTTRACKALLOCATIONS.LIST>
   <REFVOUCHERDETAILS.LIST>       </REFVOUCHERDETAILS.LIST>
   <INVOICEWISEDETAILS.LIST>       </INVOICEWISEDETAILS.LIST>
   <VATITCDETAILS.LIST>       </VATITCDETAILS.LIST>
   <ADVANCETAXDETAILS.LIST>       </ADVANCETAXDETAILS.LIST>
  </LEDGERENTRIES.LIST>
  <LEDGERENTRIES.LIST>
   <OLDAUDITENTRYIDS.LIST TYPE="Number">
    <OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS>
   </OLDAUDITENTRYIDS.LIST>
   <ROUNDTYPE/>
   <LEDGERNAME>OUTPUT IGST ( 18% )</LEDGERNAME>
   <GSTCLASS/>
   <ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
   <LEDGERFROMITEM>No</LEDGERFROMITEM>
   <REMOVEZEROENTRIES>No</REMOVEZEROENTRIES>
   <ISPARTYLEDGER>No</ISPARTYLEDGER>
   <ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE>
   <ISCAPVATTAXALTERED>No</ISCAPVATTAXALTERED>
   <ISCAPVATNOTCLAIMED>No</ISCAPVATNOTCLAIMED>
   <AMOUNT>${Tax Amount}</AMOUNT>
   <VATEXPAMOUNT>${Tax Amount}</VATEXPAMOUNT>
   <SERVICETAXDETAILS.LIST>       </SERVICETAXDETAILS.LIST>
   <BANKALLOCATIONS.LIST>       </BANKALLOCATIONS.LIST>
   <BILLALLOCATIONS.LIST>       </BILLALLOCATIONS.LIST>
   <INTERESTCOLLECTION.LIST>       </INTERESTCOLLECTION.LIST>
   <OLDAUDITENTRIES.LIST>       </OLDAUDITENTRIES.LIST>
   <ACCOUNTAUDITENTRIES.LIST>       </ACCOUNTAUDITENTRIES.LIST>
   <AUDITENTRIES.LIST>       </AUDITENTRIES.LIST>
   <INPUTCRALLOCS.LIST>       </INPUTCRALLOCS.LIST>
   <DUTYHEADDETAILS.LIST>       </DUTYHEADDETAILS.LIST>
   <EXCISEDUTYHEADDETAILS.LIST>       </EXCISEDUTYHEADDETAILS.LIST>
   <RATEDETAILS.LIST>       </RATEDETAILS.LIST>
   <SUMMARYALLOCS.LIST>       </SUMMARYALLOCS.LIST>
   <STPYMTDETAILS.LIST>       </STPYMTDETAILS.LIST>
   <EXCISEPAYMENTALLOCATIONS.LIST>       </EXCISEPAYMENTALLOCATIONS.LIST>
   <TAXBILLALLOCATIONS.LIST>       </TAXBILLALLOCATIONS.LIST>
   <TAXOBJECTALLOCATIONS.LIST>       </TAXOBJECTALLOCATIONS.LIST>
   <TDSEXPENSEALLOCATIONS.LIST>       </TDSEXPENSEALLOCATIONS.LIST>
   <VATSTATUTORYDETAILS.LIST>       </VATSTATUTORYDETAILS.LIST>
   <COSTTRACKALLOCATIONS.LIST>       </COSTTRACKALLOCATIONS.LIST>
   <REFVOUCHERDETAILS.LIST>       </REFVOUCHERDETAILS.LIST>
   <INVOICEWISEDETAILS.LIST>       </INVOICEWISEDETAILS.LIST>
   <VATITCDETAILS.LIST>       </VATITCDETAILS.LIST>
   <ADVANCETAXDETAILS.LIST>       </ADVANCETAXDETAILS.LIST>
  </LEDGERENTRIES.LIST>
  <PAYROLLMODEOFPAYMENT.LIST>      </PAYROLLMODEOFPAYMENT.LIST>
  <ATTDRECORDS.LIST>      </ATTDRECORDS.LIST>
  <GSTEWAYCONSIGNORADDRESS.LIST>      </GSTEWAYCONSIGNORADDRESS.LIST>
  <GSTEWAYCONSIGNEEADDRESS.LIST>      </GSTEWAYCONSIGNEEADDRESS.LIST>
  <TEMPGSTRATEDETAILS.LIST>      </TEMPGSTRATEDETAILS.LIST>
 </VOUCHER>
</TALLYMESSAGE>
<TALLYMESSAGE xmlns:UDF="TallyUDF">
 <COMPANY>
  <REMOTECMPINFO.LIST MERGE="Yes">
   <NAME>f5dd5c53-f8d1-4e96-b7d5-df0aa049c1c8</NAME>
   <REMOTECMPNAME>ABC company</REMOTECMPNAME>
   <REMOTECMPSTATE>${state company}</REMOTECMPSTATE>
  </REMOTECMPINFO.LIST>
 </COMPANY>
</TALLYMESSAGE>
<TALLYMESSAGE xmlns:UDF="TallyUDF">
 <COMPANY>
  <REMOTECMPINFO.LIST MERGE="Yes">
   <NAME>f5dd5c53-f8d1-4e96-b7d5-df0aa049c1c8</NAME>
   <REMOTECMPNAME>ABC Company</REMOTECMPNAME>
   <REMOTECMPSTATE>${State Company}</REMOTECMPSTATE>
  </REMOTECMPINFO.LIST>
 </COMPANY>
</TALLYMESSAGE>

${} is used to display data which is need to be inserted in the template (for references)

following is the output data I’m getting from the mysql node.
In this ive used multiple left joins from various tables (customers, products, orders, Organization) to get the data

the xml template here is showing some weird result on pasting it here.
Here is the pastebin link Download XML template for your reference.

hello @jan can you help me out on this?

If your XML file needs to follow a specific structure which you can’t get to work with the XML node itself you can still build the structure manually through the Code node. Check out Create an RSS feed based on a website's content | n8n workflow template for an example (this older example still uses Function/Function Item nodes, but the basic idea would work using the Code node as well).