Flattening Nested EDI XML Namespaces with xmltodict Permalink to this section#

↑ Part of XML to JSON Conversion with xmltodict.

A supplier ASN that carries three XML namespaces — one for the envelope, one for the header, one for line items, each with its own prefix that changes between vendors and sometimes between deliveries from the same vendor — does not fail to parse. It parses fine and produces a dictionary keyed on ns0:LineItem, cbc:ID, {urn:vendor:v3}Quantity, and a scatter of @-prefixed attribute keys and #text leaves that no downstream join can use. The fix is not a smarter parser; it is a disciplined xmltodict.parse() configuration — a namespace collapse map plus explicit force_list rules — followed by a recursive flattener that turns the resulting nested OrderedDict into flat, dotted-path columns a pandas.DataFrame can merge on.

Operational Trigger Signals Permalink to this section#

Reach for a namespace-collapse-and-flatten step, rather than parsing raw and hoping the shape is stable, when these conditions show up in your supplier XML feeds:

1. Prefix churn across deliveries. The same logical element arrives as ns0:LineItem in Monday’s ASN and ns3:LineItem in Tuesday’s, because the trading partner’s translator regenerates prefixes per batch. Any downstream code keyed on the literal prefix string breaks silently the next time the prefix rotates.
2. Multiple namespaces per document. A single payload mixes an envelope namespace, a header namespace, and a line-item namespace — common when a vendor wraps a UBL or cXML fragment inside a proprietary envelope — so a flat process_namespaces=False parse produces three incompatible key vocabularies in one dict.
3. Attribute-borne join keys mixed with #text leaves. Quantities, unit-of-measure codes, and currency qualifiers live in attributes (<Quantity unitCode="EA">120</Quantity>), so the parsed node is {"@unitCode": "EA", "#text": "120"} instead of a scalar, and naive pandas.json_normalize() calls choke on the mixed shape.
4. Single-vs-list ambiguity on line items. A one-line-item order parses LineItem as a dict; a multi-line order parses the same tag as a list. Code that calls .get("Quantity") on the result raises AttributeError: 'list' object has no attribute 'get' on roughly a third of real-world ASNs, intermittently, depending on order size.
5. Downstream code expects dotted columns, not nested dicts. The matching engine and the Schema Validation Using Pydantic layer both want flat fields like lineitems.lineitem.0.item_id, not a three-level OrderedDict, because that is what maps cleanly onto DataFrame columns and Pydantic model fields.

If a feed has exactly one namespace, no attribute-borne data, and stable list arity, the whole-document recipe on the parent XML to JSON Conversion with xmltodict page is sufficient on its own — this collapse-and-flatten procedure earns its complexity only once namespaces and arity actually drift.

Step-by-Step Implementation Permalink to this section#

Treat the conversion as three independently testable stages: collapse namespaces to short, stable keys at parse time; force known-variable elements into lists so arity never flips; then recursively flatten the resulting dict into dotted-path scalars.

1. Build a namespace collapse map keyed by URI, not prefix. Prefixes rotate between deliveries; the namespace URI is the only stable identifier a vendor actually commits to. Map each URI to a short, fixed alias.
2. Parse with process_namespaces=True and the collapse map. xmltodict rewrites every {uri}tag pair to alias:tag using your map, so the output vocabulary is stable regardless of which prefix the source document used.
3. Enumerate every element that can repeat and pass it to force_list. Anything that is a collection in the schema — line items, pack references, charge lines — goes in the set even if a specific test document only has one, so single-item orders never collapse to a bare dict.
4. Recursively flatten the parsed dict into dotted-path keys. Walk every dict and list node, appending the key (or list index) to the running path, and stop at scalars, @attribute keys, and #text leaves.
5. Load the flattened records into a DataFrame. Each top-level record becomes one row; the dotted paths become column names, which is exactly the shape Converting Legacy EDI XML to Structured JSON hands to the coercion layer next.

import logging
from typing import Any, Dict, List, Optional, Union

import pandas as pd
import xmltodict

logger = logging.getLogger("supply_chain.ingest.xml_flatten")

# Keyed by namespace URI, not prefix -- prefixes rotate between deliveries,
# the URI is the only thing a vendor's translator actually keeps stable.
NAMESPACE_COLLAPSE_MAP: Dict[str, Optional[str]] = {
    "urn:vendor:envelope:v3": "env",
    "urn:vendor:header:v1": "hdr",
    "urn:vendor:lineitems:v2": "li",
    "http://www.w3.org/2001/XMLSchema-instance": None,  # drop xsi:* noise entirely
}

# Elements that repeat in some payloads and appear singly in others. Every
# name here is forced to a list so arity never flips between documents.
FORCE_LIST_ELEMENTS = ("li:LineItem", "li:PackReference", "hdr:ChargeLine")


def parse_supplier_xml(xml_bytes: bytes) -> Dict[str, Any]:
    """Parse namespaced supplier XML into a dict with collapsed, stable keys."""
    parsed = xmltodict.parse(
        xml_bytes,
        process_namespaces=True,
        namespaces=NAMESPACE_COLLAPSE_MAP,
        force_list=FORCE_LIST_ELEMENTS,
        attr_prefix="@",
        cdata_key="#text",
        strip_whitespace=True,
    )
    logger.info("parsed supplier xml: %d top-level keys", len(parsed))
    return parsed


def flatten_dict(
    node: Union[Dict[str, Any], List[Any], Any],
    parent_key: str = "",
    sep: str = ".",
) -> Dict[str, Any]:
    """Recursively flatten a nested xmltodict result into dotted-path scalars.

    Dict keys and list indices are joined with `sep`. Attribute keys (prefixed
    with '@') and text leaves ('#text') flatten in place -- an element that
    has both an attribute and a text body yields two sibling columns rather
    than a collision, e.g. `quantity.@unitcode` and `quantity.#text`.
    """
    flat: Dict[str, Any] = {}
    if isinstance(node, dict):
        for key, value in node.items():
            # Normalize collapsed namespace keys ("li:LineItem" -> "lineitem")
            # so column names stay readable and stable across vendors.
            clean_key = key.split(":")[-1].lower() if ":" in key else key
            new_key = f"{parent_key}{sep}{clean_key}" if parent_key else clean_key
            flat.update(flatten_dict(value, new_key, sep))
    elif isinstance(node, list):
        if not node:
            logger.warning("empty list at path '%s'; no rows emitted", parent_key)
        for index, item in enumerate(node):
            flat.update(flatten_dict(item, f"{parent_key}{sep}{index}", sep))
    else:
        # Scalar leaf: string, number, or None from an empty element.
        flat[parent_key] = node
    return flat


def supplier_xml_to_dataframe(xml_bytes: bytes, record_path: str) -> pd.DataFrame:
    """Parse, flatten, and load one supplier XML payload's line items into a DataFrame."""
    parsed = parse_supplier_xml(xml_bytes)
    try:
        records = flatten_dict(parsed)
    except RecursionError:
        # A malformed or adversarially deep payload; quarantine instead of crashing the batch.
        logger.error("recursion limit hit flattening payload at '%s'", record_path)
        return pd.DataFrame()
    df = pd.DataFrame([records])
    logger.info("flattened to %d columns for record_path '%s'", len(df.columns), record_path)
    return df

Configuration Reference Permalink to this section#

These xmltodict.parse() keyword arguments, plus the flattener’s own separator argument, are what make the pipeline deterministic across vendors and deliveries:

Debugging & Recovery Permalink to this section#

Namespace and arity bugs in this pipeline are almost always silent until a specific record shape triggers them, so build recovery in from the start:

• Namespace drift breaking the collapse map. A vendor migrates schema versions and starts emitting urn:vendor:lineitems:v3 instead of v2. Because the collapse map is keyed by exact URI, unmapped elements fall through with their raw {uri}tag form still in the key, and downstream columns silently rename themselves. Log every top-level key that still contains { after parsing and alert on it — that is the map falling behind the schema.
• Missing force_list entries. The most common production incident: a single-line order parses LineItem as a dict, so flatten_dict emits lineitems.lineitem.@itemid instead of lineitems.lineitem.0.@itemid, and a DataFrame concatenation across a batch produces two incompatible column sets. Catch this at ingestion by diffing the column set of each parsed record against a reference schema before it reaches the matching stage, not after.
• Attribute/child key collisions. If a raw element is named text or attr, or a child element shares a name with an attribute on its own parent, the flattener’s dotted path can theoretically collide. Guard against it by asserting len(flat) == len(set(flat))-equivalent uniqueness in a unit test against a canonical fixture per vendor, and fail loudly rather than let one column silently overwrite another.
• Deeply recursive or adversarial payloads. A malformed feed with runaway nesting can hit Python’s recursion limit inside flatten_dict. Catch RecursionError explicitly, as the reference implementation does, and route the offending record to a dead-letter queue rather than letting it kill the batch worker — the same DLQ pattern used across Converting Legacy EDI XML to Structured JSON.
• Empty lists producing zero rows. force_list on an element that turns out to be genuinely absent yields an empty list, and flatten_dict emits nothing for that path. If a downstream Pydantic model marks line_items as required, that record should fail validation loudly at the Schema Validation Using Pydantic layer rather than silently loading as a header-only row.

FAQ Permalink to this section#

Why key the namespace collapse map by URI instead of by prefix? Permalink to this section#

Because the prefix is an arbitrary local alias the sender’s XML serializer chooses per document, while the URI is the actual namespace identity declared in the schema and is expected to stay constant across a schema version. A collapse map keyed by prefix breaks the first time a vendor’s translator regenerates prefixes, which happens more often than most integrations expect; a map keyed by URI survives prefix churn entirely and only needs updating when the vendor genuinely changes schema versions.

Should I flatten before or after validating with Pydantic? Permalink to this section#

Flatten first, then validate the flattened row. A recursive flattener has no schema awareness — it cannot tell a required field from an optional one — so it should stay a pure structural transform. Validation, type coercion, and required-field enforcement belong to a typed contract downstream, as covered in Schema Validation Using Pydantic. Keeping the two concerns separate means a schema change only touches the validation layer, not the parsing code.

Does this same collapse-and-flatten approach apply to EDI X12 data converted through an XML wrapper? Permalink to this section#

Partially. If a trading partner wraps X12 segments in an XML envelope before transmission, the same process_namespaces and force_list discipline applies to the wrapper. But native X12, without an XML wrapper, has its own segment and element delimiters rather than tags and namespaces, so it needs the dedicated segment parser described in Parsing EDI X12 Envelopes into Line Items rather than xmltodict at all.

Related Permalink to this section#

• XML to JSON Conversion with xmltodict — the parent decode strategy this procedure specializes
• Converting Legacy EDI XML to Structured JSON — the full profile-parse-validate-stream-recover pipeline this stage plugs into
• Parsing EDI X12 Envelopes into Line Items — the segment-delimited sibling format for partners who skip XML entirely
• Schema Validation Using Pydantic — the typed contract that consumes these flattened, dotted-path rows
• ↑ Parent: XML to JSON Conversion with xmltodict