Need to create XML file from output of 2 MySql Queries

Questions
,
1

Good day

following from Split and create array - #9 by MutedJam

I have a workflow that queries a MySql database for invoice details.

One query gets the invoice header containing “customer details”, Invoice totals, etc.
The next query gets the invoice lines for the invoice… i can be multiple lines.

From the above results, I have to create an XML file like the example below:

1   <?xml version="1.0" encoding="utf-8"?>
2   <InvoicesDoc xmlns="http://www.aade.gr/myDATA/invoice/v1.0" xsi:schemaLocation="http://www.aade.gr/myDATA/invoice/v1.0 schema.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ic="https://www.aade.gr/myDATA/incomeClassificaton/v1.0">
3       <invoice>
4         <issuer>
5           <vatNumber>YOURVATNUMBERHERE</vatNumber>
6           <country>GR</country>
7           <branch>0</branch>
8         </issuer>
9         <counterpart>
10           <vatNumber>CLIENTVATNUMBERHERE</vatNumber>
11           <country>GR</country>
12           <branch>0</branch>
13           <address>
14             <postalCode>11133</postalCode>
15             <city>Αθήνα</city>
16           </address>
17         </counterpart>
18         <invoiceHeader>
19           <series>ΤΙΜΟΛΟΓΙΟ ΠΩΛΗΣΗΣ</series>
20           <aa>136</aa>
21           <issueDate>2022-07-21</issueDate>
22           <invoiceType>1.1</invoiceType>
23           <currency>EUR</currency>
24         </invoiceHeader>
25         <paymentMethods>
26           <paymentMethodDetails>
27             <type>5</type>
28             <amount>124.00</amount>
29           </paymentMethodDetails>
30         </paymentMethods>
31         <invoiceDetails>
32           <lineNumber>1</lineNumber>
33           <netValue>100.00</netValue>
34           <vatCategory>1</vatCategory>
35           <vatAmount>24.00</vatAmount>
36           <incomeClassification>
37             <icls:classificationType>E3_561_001</icls:classificationType>
38             <icls:classificationCategory>category1_1</icls:classificationCategory>
39             <icls:amount>100.00</icls:amount>
40           </incomeClassification>
41         </invoiceDetails>
42         <invoiceSummary>
43           <totalNetValue>100.00</totalNetValue>
44           <totalVatAmount>24.00</totalVatAmount>
45           <totalWithheldAmount>0</totalWithheldAmount>
46           <totalFeesAmount>0</totalFeesAmount>
47           <totalStampDutyAmount>0</totalStampDutyAmount>
48           <totalOtherTaxesAmount>0</totalOtherTaxesAmount>
49           <totalDeductionsAmount>0</totalDeductionsAmount>
50           <totalGrossValue>124.00</totalGrossValue>
51           <incomeClassification>
52             <icls:classificationType>E3_561_001</icls:classificationType>
53             <icls:classificationCategory>category1_1</icls:classificationCategory>
54             <icls:amount>100.00</icls:amount>
55           </incomeClassification>
56         </invoiceSummary>
57       </invoice>
58   </InvoicesDoc>

This is my workflow (not sure how to share it properly):

image
image978×327 18.7 KB

Here is my Invoice header data (from Function 1):

[{
    "line_items": [{
        "invoiceid": 109092,
        "mod_by_internal": "Denis XXXXXXX",
        "mod_date_internal": "2022-09-01T16:29:18.000Z",
        "modifiedby": 5,
        "createdtime": "2020-10-13T08:41:57.000Z",
        "modifiedtime": "2022-09-01T13:29:18.000Z",
        "invoicedate1": "2020-10-19T00:00:00.000Z",
        "duedate1": "2020-11-19T00:00:00.000Z",
        "invoice_no": "PRO-131",
        "accountname": "BarXXXXXXXXXX",
        "cf_1183": 0,
        "cf_1184": null,
        "inv_type": "1.3 Sales - Third Country",
        "cf_1111": "19",
        "cf_1107": 35,
        "cf_1105": "",
        "cf_1087": "",
        "cf_1085": "",
        "cf_859": "",
        "inv_language": "EN",
        "cf_703": "",
        "inv_actt_name_gr": "BarkXXXXXXXX",
        "inv_vat": "GB538XXXXXXXX",
        "inv_doi": "",
        "inv_acct_businesstype": "",
        "cf_752": 0,
        "cf_756": "barkXXX@XXXXX",
        "cf_758": "",
        "cf_1089": "TNT",
        "cf_1101": "786.40000000",
        "cf_1103": "375.00000000",
        "cf_1109": "1161.4000",
        "cf_1121": "",
        "cf_1188": "",
        "cf_1190": "",
        "cf_1213": "",
        "cf_1215": "0",
        "cf_1219": null,
        "cf_1221": "",
        "cf_1245": "GB - United Kingdom",
        "cf_1249": "375.0000",
        "cf_2026": "",
        "cf_2098": "",
        "cf_2506": "",
        "cf_2508": null,
        "cf_2510": "",
        "bill_city": "HXXXXXX",
        "bill_code": "CXXXXXXX",
        "bill_country": "Great Britain",
        "bill_state": "",
        "bill_street": "POXXXXXXXXXXXX",
        "bill_pobox": null
    }]
}]

And here my invoice lines(from “Function”):

[{
    "line_items": [{
            "id": 109092,
            "productid": 109086,
            "sequence_no": 1,
            "quantity": "2.000",
            "listprice": "35.00000000",
            "discount_percent": null,
            "discount_amount": null,
            "comment": "GPS upgrade for Wave-4G series. (connectors + GPS antenna)",
            "description": null,
            "incrementondel": 0,
            "lineitem_id": 4532,
            "tax1": "0.000",
            "tax2": null,
            "tax3": null,
            "tax4": null,
            "image": null,
            "purchase_cost": "0.00000000",
            "margin": "0.00000000"
        },
        {
            "id": 109092,
            "productid": 6833,
            "sequence_no": 2,
            "quantity": "1.000",
            "listprice": "12.00000000",
            "discount_percent": null,
            "discount_amount": null,
            "comment": "12V-24V Car plug Power Adapter MW292",
            "description": null,
            "incrementondel": 0,
            "lineitem_id": 4533,
            "tax1": "0.000",
            "tax2": null,
            "tax3": null,
            "tax4": null,
            "image": null,
            "purchase_cost": "0.00000000",
            "margin": "0.00000000"
        },
        {
            "id": 109092,
            "productid": 109091,
            "sequence_no": 3,
            "quantity": "1.000",
            "listprice": "210.00000000",
            "discount_percent": null,
            "discount_amount": null,
            "comment": "Wave-4G GPS 1xSIM 1xLAN 1xWAN GPS - WiFi Router",
            "description": null,
            "incrementondel": 0,
            "lineitem_id": 4534,
            "tax1": "0.000",
            "tax2": null,
            "tax3": null,
            "tax4": null,
            "image": null,
            "purchase_cost": "0.00000000",
            "margin": "0.00000000"
        },
        {
            "id": 109092,
            "productid": 6391,
            "sequence_no": 4,
            "quantity": "1.000",
            "listprice": "35.00000000",
            "discount_percent": null,
            "discount_amount": null,
            "comment": "GPS tracker TR102",
            "description": null,
            "incrementondel": 0,
            "lineitem_id": 4535,
            "tax1": "0.000",
            "tax2": null,
            "tax3": null,
            "tax4": null,
            "image": null,
            "purchase_cost": "0.00000000",
            "margin": "0.00000000"
        }
    ]
}]

The DB search is based on “Views” so i can modify the field names if needed…

But how can i get all that info into that XML structure?

Many thanks in advance.

DD

2

Hey @xewonder, I am not sure I fully understand your requirements here. Your XML example includes fields which don’t seem to be present in the JSON data you have shared, for example counterpart, invoiceHeader or paymentMethods. Where do these details come from?

3

hello @MutedJam

That’s part of the problem.

I need to construct the xml as per specifications (from the government).

So for example i need to manually add “ <\counterpart>” or “ <\address>” and then add the relevant fields like “ <\vatNumber>” (in my case inv_vat from my invoice header>

As i said, I am using “views” for my query so i can rename “inv_vat” to “vatNumber”.

All the data I need is in my queries…

Another problem are the “invoice lines” where there may be many…

Thanks a lot for looking at this btw!

DD

4

not sure if I am going down the wrong path here…

image
image1623×382 29.1 KB

in this test, i am creating different “sets” with the data required for each header (like counterpart) and then use the functions

return [{
json: {
address: items.map(e => e.json)
}
}];

return [{
json: {
counterpart: items.map(e => e.json)
}
}];

and a merge to nest them…

result is:

image
image625×525 8.9 KB

is this the right approach?

Thank you

5

Hi @xewonder, I was thinking about using the Merge node as well (and the Function node would be my choice if calculations become necessary, such as calculating an invoice total). I didn’t get to build a full blown example flow yet though. But on first look this seems promising to me.

6

thank you so much…

The function is used to create a “label” like counterpart… with all the items below it.

I persist with this… looks like a big job but well worth it!

dd

1 Like