Reading XML and other special formats in Spark | Data Engineering

Q: How do you read XML in Spark?

You use the sparkxml data source. The key option is rowTag, the repeating element Spark turns into one row. rootTag names the document wrapper. Nested elements map to struct columns and repeated elements to arrays. Read XML: each <transaction becomes one row df = spark.read.format("xml") \ .option("

Q: How do you read from a relational database via JDBC?

Use spark.read.format("jdbc") with options for URL, table or query, user, password, and driver. For large tables, parallelize with partitionColumn, lowerBound, upperBound, and numPartitions to avoid a singlepartition bottleneck. JDBC: read from Oracle with parallel partitions df = spark.read.format(

Q: How do you read Avro files?

spark.read.format("avro") reads Avro files. In streaming, Avro is often paired with a Schema Registry for schemaaware deserialization. Read Avro file df = spark.read.format("avro").load( "s3://banklake/raw/events/") df.show(3) Streaming with Schema Registry df_stream = spark.readStream.format("kafka

Q: What are common interview follow-up questions?

How do you handle XML attributes vs element text in sparkxml? How do you push down filters through the JDBC connector? What is the difference between dbtable and query options in JDBC? How do you read from HBase in Spark? How does Spark 4's builtin XML support differ from the sparkxml package?

Definition

Spark reads XML by naming the rowTag, and reads JDBC, Avro, Delta, Iceberg, and HBase through pluggable connectors, all via spark.read.format with format-specific options.

spark-xml – a data-source connector (built into Spark 4, available as com.databricks.spark.xml earlier) that reads XML by treating a specified repeating element as one row.
rowTag – the XML element that becomes one DataFrame row.
rootTag – the wrapper element around all rowTags.
DataSource API – Spark's pluggable interface where a format name plus options produces a DataFrame.
JDBC connector – reads relational databases with options for URL, table, user, password, and partitioned parallel reads.
Format-agnostic pipeline – once any source is loaded as a DataFrame, the rest of the pipeline is the same regardless of origin.

The National Bank receives data in many shapes: a messaging partner sends XML, the core banking system lives in an Oracle database, the lake stores Parquet and Delta, and a NoSQL store holds HBase rows. Spark is like a universal reader with interchangeable lenses: snap on the XML lens and tell it which tag is one record, snap on the JDBC lens and give it a connection string, snap on the Delta lens for the lake. The reading syntax is always the same shape, only the format and options change.

Understanding the Concepts

How do you read XML in Spark?

You use the spark-xml data source. The key option is rowTag, the repeating element Spark turns into one row. rootTag names the document wrapper. Nested elements map to struct columns and repeated elements to arrays.

PYTHON

# Read XML: each <transaction> becomes one row
df = spark.read.format("xml") \
    .option("rowTag", "transaction") \
    .option("rootTag", "transactions") \
    .load("s3://bank-lake/incoming/transfers.xml")

df.printSchema()
df.show(3)

TEXT

root
 |-- txn_id: string
 |-- account_id: string
 |-- amount: decimal(15,2)
 |-- date: string

+------+----------+--------+----------+
|txn_id|account_id|  amount|      date|
+------+----------+--------+----------+
|    T1|   SAV2001|15000.00|2024-10-29|
|    T2|   CUR3002|25000.00|2024-10-29|
+------+----------+--------+----------+

XML

<!-- Input XML file: transfers.xml --syn-kw">>
syn-kw"><transactionssyn-kw">>
  syn-kw"><transactionsyn-kw">>
    syn-kw"><txn_idsyn-kw">>T1syn-kw"></txn_idsyn-kw">>
    syn-kw"><account_idsyn-kw">>SAV2001syn-kw"></account_idsyn-kw">>
    syn-kw"><amountsyn-kw">>15000.00syn-kw"></amountsyn-kw">>
    syn-kw"><datesyn-kw">>2024-10-29syn-kw"></datesyn-kw">>
  syn-kw"></transactionsyn-kw">>
  syn-kw"><transactionsyn-kw">>
    syn-kw"><txn_idsyn-kw">>T2syn-kw"></txn_idsyn-kw">>
    syn-kw"><account_idsyn-kw">>CUR3002syn-kw"></account_idsyn-kw">>
    syn-kw"><amountsyn-kw">>25000.00syn-kw"></amountsyn-kw">>
    syn-kw"><datesyn-kw">>2024-10-29syn-kw"></datesyn-kw">>
  syn-kw"></transactionsyn-kw">>
syn-kw"></transactionssyn-kw">>

How do you read from a relational database via JDBC?

Use spark.read.format("jdbc") with options for URL, table or query, user, password, and driver. For large tables, parallelize with partitionColumn, lowerBound, upperBound, and numPartitions to avoid a single-partition bottleneck.

PYTHON

# JDBC: read from Oracle with parallel partitions
df = spark.read.format("jdbc") \
    .option("url", "jdbc:oracle:thin:@oracle-host:1521/bankdb") \
    .option("dbtable", "accounts") \
    .option("user", "etl_user") \
    .option("password", "secret") \
    .option("driver", "oracle.jdbc.OracleDriver") \
    .option("partitionColumn", "account_id") \
    .option("lowerBound", "1") \
    .option("upperBound", "1000000") \
    .option("numPartitions", "10") \
    .load()

JAVA

package com.bank.formats;

public class JdbcConnector {
    public static void main(String[] args) {
        System.out.println("JDBC connector options:");
        System.out.println("  url: connection string");
        System.out.println("  dbtable or query: source");
        System.out.println("  user, password: credentials");
        System.out.println("  driver: JDBC driver class");
        System.out.println();
        System.out.println("Parallel reads:");
        System.out.println("  partitionColumn + bounds + numPartitions");
        System.out.println("  Without: single-partition bottleneck!");
    }
}

TEXT

JDBC connector options:
  url: connection string
  dbtable or query: source
  user, password: credentials
  driver: JDBC driver class

Parallel reads:
  partitionColumn + bounds + numPartitions
  Without: single-partition bottleneck!

How do you read Avro files?

spark.read.format("avro") reads Avro files. In streaming, Avro is often paired with a Schema Registry for schema-aware deserialization.

PYTHON

# Read Avro file
df = spark.read.format("avro").load(
    "s3://bank-lake/raw/events/")
df.show(3)

# Streaming with Schema Registry
df_stream = spark.readStream.format("kafka") \
    .option("subscribe", "bank-events") \
    .load()
# Deserialize Avro payload with registry

How do you read Delta Lake and Iceberg tables?

Delta and Iceberg are read via their respective format names. Delta supports time travel options; Iceberg integrates through its catalog.

PYTHON

# Delta Lake: with time travel
df = spark.read.format("delta") \
    .option("versionAsOf", 5) \
    .load("s3://bank-lake/curated/txns_delta")

# Apache Iceberg: via catalog
df = spark.read.format("iceberg").load(
    "bank_catalog.curated.transactions")

How does the DataSource API unify all formats?

Spark's DataSource API means a format name plus options always produces a DataFrame. Once loaded, all data looks the same regardless of source, so the rest of your pipeline is format-agnostic. Writing mirrors reading with df.write.format(...).save(...).

JAVA

package com.bank.formats;

public class UnifiedAPI {
    public static void main(String[] args) {
        System.out.println("Reading pattern:");
        System.out.println("  spark.read.format(NAME)");
        System.out.println("    .option(KEY, VALUE)");
        System.out.println("    .load(PATH)");
        System.out.println();
        System.out.println("NAME can be:");
        System.out.println("  xml, jdbc, avro, parquet,"
            + " orc, delta, iceberg, csv, json");
        System.out.println();
        System.out.println("Once loaded: all are DataFrames.");
        System.out.println("Pipeline does not care about source format.");
    }
}

TEXT

Reading pattern:
  spark.read.format(NAME)
    .option(KEY, VALUE)
    .load(PATH)

NAME can be:
  xml, jdbc, avro, parquet, orc, delta, iceberg, csv, json

Once loaded: all are DataFrames.
Pipeline does not care about source format.

What are common pitfalls with special format connectors?

Connectors must match Spark and Scala versions. For JDBC, always parallelize large pulls. For XML, supply an explicit schema for irregular documents to avoid inference surprises.

JAVA

package com.bank.formats;

public class ConnectorPitfalls {
    public static void main(String[] args) {
        System.out.println("Common pitfalls:");
        System.out.println("  1. Version mismatch: connector vs Spark");
        System.out.println("     -> Check Scala version too (2.12/2.13)");
        System.out.println("  2. JDBC without partitioning:");
        System.out.println("     -> Single partition, one connection");
        System.out.println("     -> Bottleneck on large tables");
        System.out.println("  3. XML schema inference:");
        System.out.println("     -> Irregular docs surprise you");
        System.out.println("     -> Supply explicit StructType");
        System.out.println("  4. Missing --packages flag:");
        System.out.println("     -> ClassNotFoundException");
    }
}

TEXT

Common pitfalls:
  1. Version mismatch: connector vs Spark
     -> Check Scala version too (2.12/2.13)
  2. JDBC without partitioning:
     -> Single partition, one connection
     -> Bottleneck on large tables
  3. XML schema inference:
     -> Irregular docs surprise you
     -> Supply explicit StructType
  4. Missing --packages flag:
     -> ClassNotFoundException

How do you combine multiple sources in one pipeline?

A typical banking pipeline reads XML from a partner, accounts from Oracle via JDBC, and joins them into a Delta table.

PYTHON

# Combine XML transfers + JDBC accounts
xml_df = spark.read.format("xml") \
    .option("rowTag", "transfer") \
    .load("s3://bank-lake/incoming/transfers.xml")

acct_df = spark.read.format("jdbc") \
    .option("url", "jdbc:oracle:thin:@oracle:1521/bankdb") \
    .option("query",
        "SELECT account_id, holder_name FROM accounts") \
    .option("user", "etl_user") \
    .option("password", "secret") \
    .load()

# Join and write to Delta
enriched = xml_df.join(acct_df, "account_id")
enriched.write.format("delta").mode("append").save(
    "s3://bank-lake/curated/transfers_delta")

What are common interview follow-up questions?

How do you handle XML attributes vs element text in spark-xml?
How do you push down filters through the JDBC connector?
What is the difference between dbtable and query options in JDBC?
How do you read from HBase in Spark?
How does Spark 4's built-in XML support differ from the spark-xml package?

Visual Overview

DIAGRAM

  Spark Universal Reader Pattern
  ──────────────────────────────

  ┌──────────┐  spark.read.format("xml")      ┌────────────┐
  │ XML file │ ────────────────────────────── ▶│            │
  └──────────┘                                 │            │
  ┌──────────┐  spark.read.format("jdbc")     │            │
  │ Oracle DB│ ────────────────────────────── ▶│ DataFrame  │
  └──────────┘                                 │ (unified)  │
  ┌──────────┐  spark.read.format("avro")     │            │
  │ Avro file│ ────────────────────────────── ▶│            │
  └──────────┘                                 │            │
  ┌──────────┐  spark.read.format("delta")    │            │
  │Delta Lake│ ────────────────────────────── ▶│            │
  └──────────┘                                 └─────┬──────┘
                                                      │
                                                      ▼
                                              Pipeline is
                                              format-agnostic

Key Takeaway

Spark reads XML through a data-source connector, using rowTag to name the repeating element that becomes one row and rootTag for the wrapper element. The reading pattern generalizes across many formats through the DataSource API: a format name plus options always produces a DataFrame. JDBC reads relational databases with parallel partitioning. Avro reads Avro files, often with a Schema Registry. Delta reads Delta Lake tables with time travel. Iceberg reads through its catalog. Once loaded, every source looks identical, so the rest of the pipeline is format-agnostic.

The practical advice is to add connectors with the packages flag and match them to Spark and Scala versions, always parallelize large JDBC reads so you do not bottleneck on one partition, and supply an explicit schema for irregular XML so inference does not surprise you.

In a banking context at the National Bank of India, a partner sent a daily XML feed of interbank transfers, reference accounts lived in Oracle via JDBC with a pushdown query, the two were joined to enrich transfers with holder names, and the result was written to the Delta lake for downstream teams.

The power of the DataSource API is that the pipeline does not care whether data comes from XML, Oracle, or Avro: once loaded, it is all DataFrames.

Remember one sentence: Spark reads XML, JDBC, Avro, Delta, Iceberg, and more through pluggable connectors, and once loaded they are all DataFrames in a format-agnostic pipeline.

---