Parsing XML in BigQuery With a JavaScript UDF or Upstream

BigQuery is a warehouse that does not read XML at all. There is no file format for it in load jobs, and the SQL function library has no XML parsing. If your source is XML, you have two ways in: parse it inside the query with a JavaScript user-defined function, or parse it before it reaches BigQuery. The first is quick to try and has traps. The second takes more setup and is the better path for volume.

What BigQuery does and does not do

A load job reads files from Cloud Storage or local upload. The supported source formats are CSV, JSON (newline-delimited), Avro, Parquet and ORC, plus Firestore and Datastore exports. XML is not on the list. The SQL standard library has functions for JSON, strings, regular expressions and arrays, and nothing for XML. So an XML document can get into BigQuery in only two ways: as a string column, loaded through one of the supported formats, or as parsed data in one of them.

That gives two designs.

Load XML as text and parse in SQL. Land each document, or each record, as a STRING column in a table, typically through newline-delimited JSON with one field holding the XML text, or CSV. Parse it with a JavaScript UDF at query time or when building a typed table.

Parse before loading. A script, a Dataflow job or a function converts XML to Parquet or JSON lines, and BigQuery loads those like any other file. This is the standard answer for volume.

The UDF route

BigQuery supports JavaScript user-defined functions. Inside the function you can use plain JavaScript and, with an option, load external libraries from Cloud Storage. There is no DOM in this environment, so DOMParser is unavailable, and you need a pure-JavaScript XML parser such as fast-xml-parser, bundled to a single file and uploaded to a bucket.

The parsing function itself is ordinary JavaScript, so you can develop and test it in Node before putting it in a UDF. This one turns an order document into a structured value:

const { XMLParser } = require("fast-xml-parser");

function parseOrder(xml) {
  const parser = new XMLParser({
    ignoreAttributes: false,
    attributeNamePrefix: "",
    isArray: (name) => name === "line",
    parseTagValue: false,
  });
  const o = parser.parse(xml).order;
  return {
    id: Number(o.id),
    status: o.status,
    customer: o.customer,
    lines: (o.lines && o.lines.line ? o.lines.line : []).map((l) => ({
      sku: l.sku, qty: Number(l.qty), price: String(l.price),
    })),
  };
}

I ran it on three documents: an order with one line, an order with none and one with two:

{"id":7,"status":"paid","customer":"Ana","lines":[{"sku":"A","qty":2,"price":"3.50"}]}
{"id":8,"status":"open","customer":"Bo","lines":[]}
{"id":9,"status":"paid","customer":"Cy","lines":[{"sku":"A","qty":1,"price":"1.00"},{"sku":"B","qty":3,"price":"2.25"}]}

Two options in the parser configuration matter. isArray marks line as always an array, so the order with a single line returns an array of one, not an object, which is the same single-item trap described in modeling lists and optional data in XML. And parseTagValue: false keeps element text as text. My first run did not set it, and the output for prices was "3.5" and "1": the parser had converted 3.50 and 1.00 into numbers, and converting them back to a string dropped the trailing zeros. For money, that is a silent change of the data. Keep text as text and cast at the end, in SQL, to NUMERIC.

Wrapping it as a UDF

In BigQuery the function is declared with a return type and the library is referenced by its bucket path. In outline:

CREATE TEMP FUNCTION parse_order(xml STRING)
RETURNS STRUCT<id INT64, status STRING, customer STRING,
               lines ARRAY<STRUCT<sku STRING, qty INT64, price STRING>>>
LANGUAGE js
OPTIONS (library = ['gs://my-bucket/fast-xml-parser.min.js'])
AS r"""
  // body of parseOrder, using the library's global
""";

SELECT o.id, o.status, l.sku, l.qty, CAST(l.price AS NUMERIC) AS price
FROM raw_xml,
     UNNEST([parse_order(xml)]) AS o,
     UNNEST(o.lines) AS l;

The details to check in the documentation: how the bundled library exposes its global (a UMD or IIFE build is easiest), the supported JavaScript version, and the types the function may return. A struct with an array of structs works, and the declared return type must match what the function returns. Invalid XML makes the function throw, and in BigQuery that fails the query; wrap the parse in try/catch and return null, then count the nulls, so one bad document does not abort a scan of millions.

The limits that decide it

Resource limits. UDFs run under memory and time limits per row and per query, and a large XML string stresses both. A document of a few kilobytes parses quickly. Megabyte-size documents are a poor fit.

Cost and speed. JavaScript UDFs are slower than native SQL functions, and parsing happens every time you query the column. Materialize the output into a typed table once with CREATE TABLE ... AS SELECT and query that.

Maintenance. A library in a bucket is a dependency you version, and the bundled file must be reviewed like any code that runs on your data.

Security. A JavaScript parser has none of the entity-expansion risks of a DTD-processing parser, because fast-xml-parser does not resolve external entities, and the usual caution still applies to limits on input size. Parsing untrusted XML in any environment deserves a check (XML external entity attacks).

The better route for volume

For tables of any size, parse upstream. The streaming loop from this cluster turns a 127 MB file of 400,000 orders into 1.2 million flat rows in about eight seconds (choosing Parquet after you parse XML), and BigQuery loads Parquet directly with correct types, including NUMERIC from a decimal column. The XML never enters the warehouse, the types are fixed at parse time, and the load is a native, cheap operation. Use the UDF for the cases it suits: a small reference table, an investigation of what a feed contains, or a prototype to show stakeholders.

Related reading

This post is part of XML in warehouses and lakes. The sibling for another engine is loading XML into Snowflake with native support. The basics of parsing in JavaScript are in parsing XML in Node with fast-xml-parser, and the same data in a typed form in flattening XML in Spark and Databricks. Google documents JavaScript UDFs and the supported load formats, and the parser is at fast-xml-parser on GitHub. Check a sample document in the XML formatter.

When a JavaScript UDF is the wrong place to parse XML

The documents are large. Per-row memory limits make big strings fragile. Parse outside.

The table is large or queried often. Re-parsing on every query wastes slots. Materialize once, or parse before loading.

You need validation. A UDF is not a place to run an XSD. Validate upstream, where the tools are mature.

Your team has a pipeline already. If an ingestion service exists, adding the XML step there keeps the warehouse free of format-specific code.