Flatten an XML in pyspark environment

Most of us are familiar with reading JSON files in a PySpark environment, followed by extraction of the required fields. All solutions mention use of explode functionality followed by extraction. Depending on choice, the explode can be selective (for specific nodes) or for all the nodes in the data set.

If the JSON is complex, the explode command creates a lot of records, which is a topic for another post.

Recently we faced a similar situation - we had to load data from an XML. We had to connect to a SOAP (yes it is still around) interface and fetch data. Our payload was XML and the response was XML (naturally, as it is SOAP). We wanted to extract data from the XML and store it in a (Databricks) table.

As we had prior experience with flattening for a JSON, we thought we could do something similar with the XML. But we could not find enough examples. Most examples were in Python (using pandas).

After much thought and trial, I hit upon a solution. I decided to look at the 'xmltodict' Python library. This library, when given an XML, converts it to a Python dictionary. As a Python dictionary and a JSON are interchangeable, we could, if conversion was successful, get the data converted from XML to JSON, which could then be flattened.

We loaded the XML and used 'xmltodict' parse it. It did the job and generated and equivalent JSON. After applying the step of removing namespace elements from the keys using replace function in Python, we had a standard structure JSON.

And we were in business.

#parsing #conversion #flattening #flatten #json #xml #pyspark #python #ingestion #data_load #databricks