Python: create csv from xml with different nested elements

Question

This is my xml file:

    <?xml version="1.0" encoding="UTF-8"?>
    <Invoice xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ccts="urn:oasis:names:specification:ubl:schema:xsd:CoreComponentParameters-2" xmlns:sdt="urn:oasis:names:specification:ubl:schema:xsd:SpecializedDatatypes-2" xmlns:udt="urn:un:unece:uncefact:data:specification:UnqualifiedDataTypesSchemaModule:2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2 UBL-Invoice-2.0.xsd">
       <cbc:ID>102165444</cbc:ID>
       <cac:InvoiceLine>
          <cbc:ID>1.0000</cbc:ID>
          <cbc:Note />
          <cbc:InvoicedQuantity unitCode="CT">1.0000</cbc:InvoicedQuantity>
          <cbc:LineExtensionAmount currencyID="DKK">142.3900</cbc:LineExtensionAmount>
          <cac:TaxTotal>
             <cbc:TaxAmount currencyID="DKK">138.24</cbc:TaxAmount>
             <cac:TaxSubtotal>
                <cbc:TaxableAmount currencyID="DKK">142.39</cbc:TaxableAmount>
                <cbc:TaxAmount currencyID="DKK">7.20</cbc:TaxAmount>
                <cac:TaxCategory>
                   <cbc:ID schemeAgencyID="320" schemeID="urn:oioubl:id:taxcategoryid-1.1">3645</cbc:ID>
                   <cac:TaxScheme>
                      <cbc:ID schemeAgencyID="320" schemeID="urn:oioubl:id:taxschemeid-1.1">140</cbc:ID>
                      <cbc:Name>Afgift</cbc:Name>
                      <cbc:TaxTypeCode listAgencyID="320" listID="urn:oioubl:codelist:taxtypecode-1.1">StandardRated</cbc:TaxTypeCode>
                   </cac:TaxScheme>
                </cac:TaxCategory>
             </cac:TaxSubtotal>
          </cac:TaxTotal>
       </cac:InvoiceLine>
          <cbc:ID>2.0000</cbc:ID>
          <cbc:Note />
          <cbc:InvoicedQuantity unitCode="CT">1.0000</cbc:InvoicedQuantity>
          <cbc:LineExtensionAmount currencyID="DKK">142.3900</cbc:LineExtensionAmount>
          <cac:TaxTotal>
             <cbc:TaxAmount currencyID="DKK">138.24</cbc:TaxAmount>
             <cac:TaxSubtotal>
                <cbc:TaxableAmount currencyID="DKK">142.39</cbc:TaxableAmount>
                <cbc:TaxAmount currencyID="DKK">7.20</cbc:TaxAmount>
                <cac:TaxCategory>
                   <cbc:ID schemeAgencyID="320" schemeID="urn:oioubl:id:taxcategoryid-1.1">3645</cbc:ID>
                   <cac:TaxScheme>
                      <cbc:ID schemeAgencyID="320" schemeID="urn:oioubl:id:taxschemeid-1.1">140</cbc:ID>
                      <cbc:Name>Afgift</cbc:Name>
                      <cbc:TaxTypeCode listAgencyID="320" listID="urn:oioubl:codelist:taxtypecode-1.1">StandardRated</cbc:TaxTypeCode>
                   </cac:TaxScheme>
                </cac:TaxCategory>
             </cac:TaxSubtotal>
          </cac:TaxTotal>
          <cac:TaxTotal>
             <cbc:TaxAmount currencyID="DKK">35.60</cbc:TaxAmount>
             <cac:TaxSubtotal>
                <cbc:TaxableAmount currencyID="DKK">142.39</cbc:TaxableAmount>
                <cbc:TaxAmount currencyID="DKK">35.60</cbc:TaxAmount>
                <cac:TaxCategory>
                   <cbc:ID schemeAgencyID="320" schemeID="urn:oioubl:id:taxcategoryid-1.1">StandardRated</cbc:ID>
                   <cbc:Percent>25</cbc:Percent>
                   <cac:TaxScheme>
                      <cbc:ID schemeAgencyID="320" schemeID="urn:oioubl:id:taxschemeid-1.1">63</cbc:ID>
                      <cbc:Name>Moms</cbc:Name>
                   </cac:TaxScheme>
                </cac:TaxCategory>
             </cac:TaxSubtotal>
          </cac:TaxTotal>
       </cac:InvoiceLine>
    </Invoice>

As you can see, the file has an id, several 'Invoice lines', that each have their own id along with other child elements.

What I want to do is create a csv file with a row for each invoice line with information from specific nested elements. The challenge is that for each line there can be several 'TaxTotal' child-elements. In that case I'd want another line with that information like this:

ID;/InvoiceLine/ID;InvoiceLine/InvoicedQuantity;/InvoiceLine/LineExtensionAmount;/InvoiceLine/TaxTotal/TaxAmount;/InvoiceLine/TaxTotal/TaxSubtotal/TaxableAmount    /InvoiceLine/TaxTotal/TaxSubtotal/TaxAmount ;/InvoiceLine/TaxTotal/TaxSubtotal/TaxCategory/ID;/InvoiceLine/TaxTotal/TaxSubtotal/TaxCategory/Percent;/InvoiceLine/TaxTotal/TaxSubtotal/TaxCategory/TaxScheme/ID;/InvoiceLine/TaxTotal/TaxSubtotal/TaxCategory/TaxScheme/Name
102165444;1;1;142,39;138,24;142,39;7,20;3645,00;;140;Afgift
102165444;2;1;142,39;138,24;142,39;7,20;3646,00;;140;Afgift
102165444;2;1;142,39;35,60;142,39;35,60;StandardRated;25,00;63;Moms

How do I accomplish this?

Is there always going to be at least one TaxTotal element? — Daniel Haley
– Daniel Haley, Commented Jun 26, 2018 at 15:34
Yes there is. And I haven't seen more than two yet, but I can't rule it out. — chrlo
– chrlo, Commented Jun 27, 2018 at 7:23

Daniel Haley · Accepted Answer · 2018-06-29 21:42:42Z

Since there's always going to be at least one TaxTotal element, I would create a new csv row for each one and go back up the tree for the preceding values.

Here's an example using lxml. I added a function to make it easier to handle empty values, but any additional formatting of values I'll leave up to you.

Python 3.6

from lxml import etree
import csv


def get_value(target_tree, xpath, namespaces):
    try:
        return target_tree.xpath(xpath, namespaces=namespaces)[0].text
    except IndexError:
        return ""


tree = etree.parse("input.xml")

ns = {"cac": "urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2",
      "cbc": "urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2",
      "i2": "urn:oasis:names:specification:ubl:schema:xsd:Invoice-2"}

with open("output.csv", "w") as csvfile:
    csvwriter = csv.writer(csvfile, delimiter=";", lineterminator="\n", quoting=csv.QUOTE_MINIMAL)
    # Header
    csvwriter.writerow(["ID", "/InvoiceLine/ID", "/InvoiceLine/InvoicedQuantity", "/InvoiceLine/LineExtensionAmount",
                        "/InvoiceLine/TaxTotal/TaxAmount", "/InvoiceLine/TaxTotal/TaxSubtotal/TaxableAmount",
                        "/InvoiceLine/TaxTotal/TaxSubtotal/TaxAmount",
                        "/InvoiceLine/TaxTotal/TaxSubtotal/TaxCategory/ID",
                        "/InvoiceLine/TaxTotal/TaxSubtotal/TaxCategory/Percent",
                        "/InvoiceLine/TaxTotal/TaxSubtotal/TaxCategory/TaxScheme/ID",
                        "/InvoiceLine/TaxTotal/TaxSubtotal/TaxCategory/TaxScheme/Name"])
    for tax_total in tree.xpath("//cac:TaxTotal", namespaces=ns):
        csvwriter.writerow([get_value(tax_total, "/i2:Invoice/cbc:ID", ns),
                            get_value(tax_total, "../cbc:ID", ns),
                            get_value(tax_total, "../cbc:InvoicedQuantity", ns),
                            get_value(tax_total, "../cbc:LineExtensionAmount", ns),
                            get_value(tax_total, "cbc:TaxAmount", ns),
                            get_value(tax_total, "cac:TaxSubtotal/cbc:TaxableAmount", ns),
                            get_value(tax_total, "cac:TaxSubtotal/cbc:TaxAmount", ns),
                            get_value(tax_total, "cac:TaxSubtotal/cac:TaxCategory/cbc:ID", ns),
                            get_value(tax_total, "cac:TaxSubtotal/cac:TaxCategory/cbc:Percent", ns),
                            get_value(tax_total, "cac:TaxSubtotal/cac:TaxCategory/cac:TaxScheme/cbc:ID", ns),
                            get_value(tax_total, "cac:TaxSubtotal/cac:TaxCategory/cac:TaxScheme/cbc:Name", ns)])

Output (output.csv)

ID;/InvoiceLine/ID;/InvoiceLine/InvoicedQuantity;/InvoiceLine/LineExtensionAmount;/InvoiceLine/TaxTotal/TaxAmount;/InvoiceLine/TaxTotal/TaxSubtotal/TaxableAmount;/InvoiceLine/TaxTotal/TaxSubtotal/TaxAmount;/InvoiceLine/TaxTotal/TaxSubtotal/TaxCategory/ID;/InvoiceLine/TaxTotal/TaxSubtotal/TaxCategory/Percent;/InvoiceLine/TaxTotal/TaxSubtotal/TaxCategory/TaxScheme/ID;/InvoiceLine/TaxTotal/TaxSubtotal/TaxCategory/TaxScheme/Name
102165444;1.0000;1.0000;142.3900;138.24;142.39;7.20;3645;;140;Afgift
102165444;2.0000;1.0000;142.3900;138.24;142.39;7.20;3645;;140;Afgift
102165444;2.0000;1.0000;142.3900;35.60;142.39;35.60;StandardRated;25;63;Moms

Collectives™ on Stack Overflow

Python: create csv from xml with different nested elements

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related