Python - XML - LXML- How to use same XML tree structure from a sample XML file to create another XML file

Question

I have a XML file with some Tree structure, elements, attributes, text. I need to use the this XML as template (tree structure and tags) and create another XML, which might not have same number of elements (i.e. In below template there are two 'column' element, but the one I want to create has three elements 'column').

Below is the XML I want to use as template

<custom-data>
    <schema>SCHEMA</schema>
    <columns>
      <column>
        <name>ORGANIZATION</name>
        <datatype>NUMBER</datatype>
        <length/>
        <precision>18</precision>
        <not-null>Yes</not-null>
      </column>
      <column>
        <name>LOCATION</name>
        <datatype>NUMBER</datatype>
        <length/>
        <precision>18</precision>
        <not-null>Yes</not-null>
      </column>
    </columns>
</custom-data>

Instead of defining a similar tree using lxml by defining Each element one by one as below. For example if 'df' is my pandas dataframe with data. which has columns as (Target Column, Data Type , Length, Scale, Nullable._

Target Column   Data Type   Length  Scale   Nullable
COLUMN1          NUMBER       38    0   N
COLUMN2          NUMBER       38    0   N
COLUMN3          NUMBER       38    0   N

Below is the sample python code I am using

from lxml import etree as et
root = et.Element('custom-data')
schema= et.SubElement(root, 'schema')
schema.text='SCHEMA'
columns= et.SubElement(root, 'columns')

for row in df.iterrows():
    column = et.SubElement(columns, 'columns')
    name = et.SubElement(column , 'name')
    datatype = et.SubElement(column , 'datatype')
    length = et.SubElement(column , 'length')
    precision = et.SubElement(column , 'precision')
    notnull = et.SubElement(column , 'not-null')

    name.text = str(row[1]['Target Column'])
    datatype.text = str(row[1]['Data Type'])
    length.text = str(row[1]['Length'])
    precision.text = str(row[1]['Scale'])
    notnull.text = str(row[1]['Nullable'])

xml_test=et.tostring(root, pretty_print=True).decode('utf-8')
f=open("xml_test.xml", "w")
f.write(xml_test)

Expected output is

<custom-data>
    <schema>SCHEMA</schema>
    <columns>
      <column>
        <name>COLUMN1</name>
        <datatype>NUMBER</datatype>
        <length>38</length>
        <precision>0</precision>
        <not-null>N</not-null>
      </column>
      <column>
        <name>COLUMN2</name>
        <datatype>NUMBER</datatype>
        <length>38</length>
        <precision>0</precision>
        <not-null>N</not-null>
      </column>
      <column>
        <name>COLUMN3</name>
        <datatype>NUMBER</datatype>
        <length>38</length>
        <precision>0</precision>
        <not-null>N</not-null>
      </column>
    </columns>
</custom-data>

How can I leverage the structure given in the Sample XML, so that I dont need to define it again in my code. Any simpler method?

Daniel Haley · Accepted Answer · 2019-04-05 18:33:16Z

You should be able to parse the XML template and use a copy of the "column" element to make new copies populated with the data from your DataFrame.

The template should be cleaned and simplified to only contain static values and a single "column" element to use as a template.

Here's an example...

XML Template (template.xml)

<custom-data>
    <schema>SCHEMA</schema>
    <columns>
        <column>
            <name/>
            <datatype/>
            <length/>
            <precision/>
            <not-null/>
        </column>
    </columns>
</custom-data>

Python

import pandas as pd
from copy import deepcopy
from lxml import etree as et

# Mock up DataFrame for testing.
data = {"Target Column": ["COLUMN1", "COLUMN2", "COLUMN3"],
        "Data Type": ["NUMBER", "NUMBER", "NUMBER"],
        "Length": [38, 38, 38],
        "Scale": [0, 0, 0],
        "Nullable": ["N", "N", "N"]}
df = pd.DataFrame(data=data)

# Create ElementTree from template XML.
tree = et.parse("template.xml")

# Map column names to element names.
name_map = {"Target Column": "name",
            "Data Type": "datatype",
            "Length": "length",
            "Scale": "precision",
            "Nullable": "not-null"}

# Select "columns" element so we can append/remove children.
columns_elem = tree.xpath("/custom-data/columns")[0]
# Select "column" element to use as a template for new column elements.
column_template = columns_elem.xpath("column")[0]

for row in df.iterrows():
    # Create a new copy of the column template.
    new_column = deepcopy(column_template)

    # Populate elements in column template based on name map.
    for col_name, elem_name in name_map.items():
        new_column.xpath(elem_name)[0].text = str(row[1][col_name])

    # Append the new column element to "columns" element.
    columns_elem.append(new_column)

# Remove original empty column template.
columns_elem.remove(column_template)

# Write tree to file.
# Note: I could've just done tree.write("output.xml"), but I used
#       XMLParser(), tostring(), and fromstring() to get the indents
#       all the same.
parser = et.XMLParser(remove_blank_text=True)
et.ElementTree(et.fromstring(et.tostring(tree), 
                             parser=parser)).write("output.xml", 
                                                   pretty_print=True)

XML Output ("output.xml")

<custom-data>
  <schema>SCHEMA</schema>
  <columns>
    <column>
      <name>COLUMN1</name>
      <datatype>NUMBER</datatype>
      <length>38</length>
      <precision>0</precision>
      <not-null>N</not-null>
    </column>
    <column>
      <name>COLUMN2</name>
      <datatype>NUMBER</datatype>
      <length>38</length>
      <precision>0</precision>
      <not-null>N</not-null>
    </column>
    <column>
      <name>COLUMN3</name>
      <datatype>NUMBER</datatype>
      <length>38</length>
      <precision>0</precision>
      <not-null>N</not-null>
    </column>
  </columns>
</custom-data>

Thanks, I still dont have privilege to upvote your asnwer.I guess thats the best we can do. But here I need to make sure to have a cleaned up template file. So everytime, template file is edited, i need to update my copy of template file, I guess that is something i need to automate too, but something, I think, cant be avoided.
You’re welcome. Instead of upvoting, you can accept the answer by clicking on the check mark (✅) next to it. Thanks!

Collectives™ on Stack Overflow

Python - XML - LXML- How to use same XML tree structure from a sample XML file to create another XML file

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related