Loading XML Into Snowflake With Native Support and FLATTEN

Snowflake is one of the few warehouses that reads XML without an external parser. You load the file into a column of type VARIANT, and the engine keeps the document as a tree you can navigate in SQL. It is a good fit for a feed that arrives as XML and has to be queryable the same day, and a poor fit if the documents are huge. This post gives the sequence of statements, explains the shape the data takes once loaded and lists what to check before you rely on it.

The input

An orders file of the kind used throughout this cluster, with a root element and many repeating children:

<orders>
  <order id="1" status="paid">
    <customer>customer-42</customer>
    <placed>2026-03-14</placed>
    <lines>
      <line sku="SKU1187" qty="9"><price>124.37</price></line>
      <line sku="SKU0615" qty="2"><price>569.38</price></line>
    </lines>
  </order>
  <order id="2" status="open">...</order>
</orders>

The goal is two tables, or one wide view, in typed columns: orders with id, status, customer and date, and lines with sku, quantity and price.

Step 1: a file format and a landing table

A file format tells COPY how to read the files. For XML, the option that matters is STRIP_OUTER_ELEMENT:

CREATE FILE FORMAT xml_orders
  TYPE = XML
  STRIP_OUTER_ELEMENT = TRUE
  COMPRESSION = AUTO;

CREATE TABLE raw_orders (src VARIANT);

Without STRIP_OUTER_ELEMENT, the whole file is one document with the root orders and one VARIANT value that holds all the orders. With it set to TRUE, Snowflake drops the root and loads each child as its own row. That is almost always what you want for a feed, because rows keep each value small and let the engine parallelize the load. The COMPRESSION = AUTO setting detects gzip and similar from the file name and header, so a .xml.gz file loads without unpacking (compressing XML archives and reading them in place explains why you keep it compressed).

Step 2: copy the file in

Put the file on a stage and copy it:

COPY INTO raw_orders
  FROM @orders_stage/2026-10-27/
  FILE_FORMAT = (FORMAT_NAME = 'xml_orders')
  ON_ERROR = 'ABORT_STATEMENT';

The ON_ERROR setting decides what happens when a document is malformed. The default for COPY INTO a table skips the file; ABORT_STATEMENT fails the whole statement, which is what you want for a first run so errors are not silently ignored. Record the result: COPY returns a row per file with the number of rows loaded and any first error. Compare the count with the number of records in the source (parsing big XML files fast suggests a grep -c before loading).

What is in the VARIANT

Each row holds one order as a tree. Snowflake's representation of XML, as its documentation describes it, uses reserved keys:

• @ holds the element's name ("order").

• $ holds the element's content: a string for a text-only element, or an array of child nodes.

• A key beginning with @ and a name, such as @id, holds an attribute value.

So one order looks roughly like an object with @ set to order, @id set to 1, @status set to paid, and a $ array containing the customer, placed and lines child objects, each with their own @ and $. You can look at the real shape on your own data with SELECT src FROM raw_orders LIMIT 1, and it is worth doing before writing any extraction, because the exact nesting depends on whitespace handling and on the options you set.

Step 3: pull typed columns out

Scalar attributes are read with path notation, and child elements with XMLGET:

SELECT
  src:"@id"::NUMBER                          AS order_id,
  src:"@status"::STRING                      AS status,
  XMLGET(src, 'customer'):"$"::STRING        AS customer,
  XMLGET(src, 'placed'):"$"::DATE            AS placed
FROM raw_orders;

XMLGET(src, 'customer') returns the first child named customer; its "$" is the text. The casts at the end turn VARIANT strings into proper types, and they are where bad data fails, so decide how to handle values that do not parse (TRY_TO_DATE, TRY_CAST) before the first production load.

Step 4: unpack repeating elements with FLATTEN

Order lines repeat, so they need FLATTEN, which turns an array into rows:

SELECT
  o.src:"@id"::NUMBER                            AS order_id,
  l.value:"@sku"::STRING                         AS sku,
  l.value:"@qty"::NUMBER                         AS qty,
  XMLGET(l.value, 'price'):"$"::NUMBER(10,2)     AS price
FROM raw_orders o,
     LATERAL FLATTEN(INPUT => XMLGET(o.src, 'lines'):"$") l
WHERE l.value:"@" = 'line';

XMLGET(o.src, 'lines'):"$" is the array of children of lines, and FLATTEN gives one row per child. The WHERE clause keeps only elements named line, since the array could also contain text nodes or other elements. Declare price as NUMBER(10,2) so money stays exact; a FLOAT would reintroduce the rounding noise seen in other engines (flattening XML in Spark and Databricks shows what a double does to a sum).

Wrap both queries in views, or materialize them into typed tables with CREATE TABLE ... AS SELECT, so analysts do not carry the XML navigation in every query.

Limits to check

Value size. A VARIANT value has a maximum size, documented at 16 MB in the standard configuration at the time of writing. A single 200 MB XML document does not fit, which is why STRIP_OUTER_ELEMENT matters and why a file with one giant element must be split or parsed outside first.

Namespaces. Prefixed names are kept in the element name key. Query with the prefix as written or strip namespaces upstream.

Whitespace. By default, whitespace-only text between elements is dropped. PRESERVE_SPACE changes that, which you need only for mixed content.

Type inference. The loader keeps text as strings unless you cast, and automatic conversion options can change numbers such as 007. Check DISABLE_AUTO_CONVERT against your identifiers.

Errors. VALIDATION_MODE on COPY lets you dry-run a load and see the errors without writing rows.

Related reading

This post is part of XML in warehouses and lakes. The same data is read in other engines in parsing XML in BigQuery with a JavaScript UDF and flattening XML in Spark and Databricks, and the generic querying approach is in querying XML data in Snowflake. The authoritative references are Snowflake's pages on loading XML data and XMLGET. Inspect your sample first in the XML formatter.

When native XML loading in Snowflake is not the right path

The documents are very large. One multi-hundred-megabyte document will not fit a VARIANT. Split at record boundaries or parse outside the warehouse and load Parquet (choosing Parquet after you parse XML).

The XML is mixed content. Documents with inline markup are awkward to query as trees of $ arrays. Extract the text outside and load that.

You parse the same XML repeatedly. Navigating the VARIANT on every query costs compute each time. Materialize typed tables once.

The source has a hostile schema. Deeply optional, irregular feeds need a loader that applies rules and reports rejects, which a plain COPY does not.