Parsing an XML Element to Dataframe in scala

user_11618455 0
Viewed 1037

I have an xml Response for a SOAP Request in Scala with Spark which i want to convert into a Dataframe so i can append it to a hive table.

I have tried databricks.spark.xml but it can only load xml files directly. I am unable to find a way to load an xml variable ( Elem)

Input:

    <XML>
    <hol_cal date="2019-01-01" Desc="New Year's Day"/>
    <hol_cal date="2019-04-19" Desc="Good Friday"/> 
    <hol_cal date="2019-04-22" Desc="Easter Monday"/>
    ...
    ...
    ...
    </XML>

Output: Data Frame:

|Date |Desc | |2019-01-01|New Year's Day| |2019-04-19|Good Friday | ....

element dataframe scala xml
1 Answers

I would use the following method:

  • Read the file into an RDD (where each element now consists of one row in the XML file)
val rawXML = sc.textFile(inputFileLocation)
  • Create a case class schema like the following:
case class DateSchema(date: String, desc: String)
  • Transform each row into an element of the DateSchema case class. You will probably want to filter out the rows that do not contain "date" and "Desc" strings in them first.
val parsedXML = rawXML.filter(row => row.contains("date") && row.contains("Desc")).map(row => {
   val splitRow = row.split("\"")
   DateSchema(splitRow(1), splitRow(3))
})
  • Convert this RDD into a dataframe using .toDF
val dateDF = parsedXML.toDF

dateDF.show

+----------+--------------+
|      date|          desc|
+----------+--------------+
|2019-01-01|New Year's Day|
|2019-04-19|   Good Friday|
|2019-04-22| Easter Monday|
+----------+--------------+
user_114989640
Related