I have an xml data coming with namespaces as shown below.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns1:report ns1:version="1.2" ns1:type="TRANSACTIONAL" xmlns:ns1="http://www.report/1.2">
<ns1:entities>
<ns1:entity ns1:type="PHYSICIANS">
<ns1:entity ns1:instance="207" ns1:type="PHYSICIAN" ns1:id="P1">
<ns1:attribute ns1:name="ID">207</ns1:attribute>
<ns1:attribute ns1:name="NAME">Dr. George</ns1:attribute>
<ns1:attribute ns1:name="ADDRESS_LINE_1">903 Lake Otis pkway </ns1:attribute>
<ns1:attribute ns1:name="ADDRESS_LINE_2" />
<ns1:attribute ns1:name="COUNTRY" ns1:tc="1" ns1:code="OLI_NATION_USA">United States of America</ns1:attribute>
<ns1:attribute ns1:name="STATE" ns1:tc="2" ns1:code="OLI_USA_AK">Alaska</ns1:attribute>
<ns1:attribute ns1:name="CITY">Anchorage</ns1:attribute>
</ns1:entity>
<ns1:entity ns1:instance="208" ns1:type="PHYSICIAN" ns1:id="P2">
<ns1:attribute ns1:name="ID">208</ns1:attribute>
<ns1:attribute ns1:name="NAME">Dr. James Hanover</ns1:attribute>
<ns1:attribute ns1:name="ADDRESS_LINE_1">220 Plano pkway </ns1:attribute>
<ns1:attribute ns1:name="ADDRESS_LINE_2" />
<ns1:attribute ns1:name="COUNTRY" ns1:tc="1" ns1:code="OLI_NATION_USA">United States of America</ns1:attribute>
<ns1:attribute ns1:name="STATE" ns1:tc="2" ns1:code="OLI_USA_AK">Texas</ns1:attribute>
<ns1:attribute ns1:name="CITY">Dallas</ns1:attribute>
</ns1:entity>
</ns1:entity>
</ns1:entities>
</ns1:report>I want this to be parsed and flattened to get an output as below to load to a table.
|ID |NAME |ADDRESS_LINE_1 |ADDRESS_LINE_2 |COUNTRY |STATE |CITY |
|---- |------------------ |--------------------|-----------------|----------------------------|------ |----------|
|207 |Dr. George |903 Lake Otis pkway |null |United States of America |Alaska |Anchorage |
|208 |Dr. James Hanover |220 Plano pkway |null |United States of America |Texas |Dallas |I have tried using the spark-xml but unable to bring it to a table like structure.
This is what I have tried so far.
from pyspark.sql.functions import explode
df = (spark.read
.format("com.databricks.spark.xml")
.option("rootTag", "ns1:entities")
.option("rowTag", "ns1:entity")
.load("/data/test/abc.xml"))
df_expl = df.select(
'*',
explode("`ns1:entity`").alias('N')
)
df_expl2 = df_expl.select(explode("N.ns1:attribute").alias("M"))I am not able to map the names to corresponding values as it is coming as different attributes with this.
Can anyone help me with this issue ?