Overview
An IRS 990 e-file document is a deeply nested XML tree. Before it can
become tidy relational tables, ef2
flattens it: every node becomes one row, so the whole
hierarchy is laid out as a long data frame we can filter, label, and
pivot. This article walks through flatten_xml() and
explains what each piece of the output means.
We use one real filing shipped with the package. It’s a modest ~100 KB return that still exercises the interesting cases — its Schedule I lists 83 grants and its Part VII lists 15 officers/directors, both one-to-many tables:
library(ef2)
library(dplyr)
#> Warning: package 'dplyr' was built under R version 4.5.2
#>
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#>
#> filter, lag
#> The following objects are masked from 'package:base':
#>
#> intersect, setdiff, setequal, union
# The filing's public URL is used to derive its OBJECTID; the XML itself is
# read from the copy shipped with the package.
url <- "https://gt990datalake-rawdata.s3.amazonaws.com/EfileData/XmlFiles/202341529349301414_public.xml"
xml_file <- system.file("extdata", "sample_990.xml", package = "ef2")
doc <- xml2::read_xml(xml_file)
xml2::xml_ns_strip(doc)
doc
#> {xml_document}
#> <Return schemaLocation="http://www.irs.gov/efile" returnVersion="2022v5.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
#> [1] <ReturnHeader binaryAttachmentCnt="0">\n <ReturnTs>2023-06-01T15:20:06-05:00</ReturnTs>\n < ...
#> [2] <ReturnData documentCnt="7">\n <IRS990 documentId="RetDoc1038000001" referenceDocumentId="Re ...xml2::xml_ns_strip() removes XML namespaces (the
irs: / efile: prefixes). The IRS schema
changes namespace declarations across years; stripping them lets us
write one set of XPaths that works for every schema version.
Here is one raw grant entry from Schedule I — a small nested subtree that will become one row of a relational table:
xml2::xml_find_first(doc, "//RecipientTable") |> as.character() |> cat()
#> <RecipientTable>
#> <RecipientBusinessName>
#> <BusinessNameLine1Txt>100WOMEN STRONG</BusinessNameLine1Txt>
#> </RecipientBusinessName>
#> <USAddress>
#> <AddressLine1Txt>714 EAST MARKET STREET</AddressLine1Txt>
#> <CityNm>LEESBURG</CityNm>
#> <StateAbbreviationCd>VA</StateAbbreviationCd>
#> <ZIPCd>20178</ZIPCd>
#> </USAddress>
#> <RecipientEIN>541950727</RecipientEIN>
#> <IRCSectionDesc>INTERFUND GRANT</IRCSectionDesc>
#> <CashGrantAmt>40250</CashGrantAmt>
#> <NonCashAssistanceAmt>0</NonCashAssistanceAmt>
#> <PurposeOfGrantTxt>HUMAN SERVICES</PurposeOfGrantTxt>
#> </RecipientTable>Step 1 — Flatten the document
flat <- flatten_xml(doc, url)
dim(flat)
#> [1] 1742 10
names(flat)
#> [1] "OBJECTID" "ORDER" "XPATH" "XPATH2" "TABLE_HEADER" "TABLE_ID"
#> [7] "TYPE" "RDB_TABLE" "VARIABLE_NAME" "VALUE"One filing expands to ~1,700 rows — one per XML node. The columns are:
| Column | Meaning |
|---|---|
OBJECTID |
Unique filing id (OID-…), derived from the URL |
ORDER |
Document order of the node (1, 2, 3, …) |
XPATH |
Full path to the node, with positional indices like
[3]
|
XPATH2 |
Normalized path — indices and namespaces stripped — used to join the concordance |
TABLE_HEADER |
The node’s grouping (“root”) context |
TABLE_ID |
Which instance of a repeating group this node belongs to |
TYPE |
"parent" (structure) or "terminal"
(data) |
RDB_TABLE |
Concordance table the node maps to (blank if unmapped) |
VARIABLE_NAME |
Standardized variable name (or the raw node name if unmapped) |
VALUE |
The node’s text content |
The flattened table is also shipped with the package
(sample_flat.rds) so the companion “extract tables”
articles can start straight from it.
Step 2 — Root nodes vs. leaf nodes
XML mixes two kinds of nodes: branch/root nodes that
only group other nodes, and leaf nodes that actually
hold a value. ef2 predicts which is which, because only
leaf nodes carry data worth extracting.
It does this purely from the set of XPaths, with two helpers:
-
find_terminal_nodes()— a path is a leaf (“terminal”) if no other path starts with it followed by/(i.e. it has no children). -
find_parent_nodes()— every ancestor prefix of some path is a parent.
get_type() combines them and labels every row:
table(flat$TYPE)
#>
#> parent terminal
#> 359 1383
# A parent node (groups children, holds no data) vs. its leaf children:
flat |>
filter(XPATH2 %in% c(
"/Return/ReturnData/IRS990ScheduleI/RecipientTable", # parent
"/Return/ReturnData/IRS990ScheduleI/RecipientTable/CashGrantAmt" # terminal
)) |>
select(XPATH2, TYPE, VALUE) |>
head(3)
#> XPATH2 TYPE
#> 1 /Return/ReturnData/IRS990ScheduleI/RecipientTable parent
#> 2 /Return/ReturnData/IRS990ScheduleI/RecipientTable/CashGrantAmt terminal
#> 3 /Return/ReturnData/IRS990ScheduleI/RecipientTable parent
#> VALUE
#> 1 100WOMEN STRONG714 EAST MARKET STREETLEESBURGVA20178541950727INTERFUND GRANT402500HUMAN SERVICES
#> 2 40250
#> 3 A FARM LESS ORDINARY17281 SIMMONS RDPURCELLVILLEVA20132811191778501(C)(3)377290GENERAL SUPPORTWhy it matters: extraction always begins with
filter(TYPE == "terminal"). The parent rows are dropped —
they exist only to describe structure.
Edge case: the prediction is structural, not
semantic. If the IRS schema ever emitted a node that held both
text and child elements (“mixed content”), the heuristic would treat it
as a parent and its text would be ignored. In practice the 990 schema
keeps data on leaf nodes, so this is safe — but it is the reason
ef2 derives type from the tree shape rather than trusting
any single node.
Step 3 — “Table headers” are root nodes
TABLE_HEADER is the grouping path a node lives under,
computed by get_header(). A table header is a root
node: it is a grouping label only — it contains other subnodes
and never holds its own data. It is what ties the repeating children of,
say, one grant together, and what maps a group of leaf nodes to a
logical table.
Step 4 — What the table numbers mean
TABLE_ID (e.g. TID-000-000-000,
TID-000-000-001) is the key that separates
one-to-one data from one-to-many data.
It is derived by get_table_id(), which reads the last
positional index [N] from the raw
XPATH:
-
TID-000-000-000→ one-to-one (1:1). The node is not inside a repeating group, so the filing has exactly one of it (organization name, total revenue, a Yes/No checkbox). Every 1:1 field across every schedule sharesTID-000-000-000. -
TID-000-000-001,TID-000-000-002, … → one-to-many (1:M). The node is the Nth instance of a repeating group. All the fields of one grant (its EIN, amount, purpose) share the sameTABLE_ID, which is exactly what lets us reassemble each grant as its own row later.
# 1:1 fields all sit in TID-000-000-000:
flat |>
filter(TABLE_ID == "TID-000-000-000", TYPE == "terminal",
RDB_TABLE == "F9-P01-T00-SUMMARY") |>
select(VARIABLE_NAME, TABLE_ID, VALUE) |>
head(4)
#> VARIABLE_NAME TABLE_ID
#> 1 F9_01_ACT_GVRN_ACT_MISSION TID-000-000-000
#> 2 F9_01_ACT_GVRN_NUM_VOTE_MEMB TID-000-000-000
#> 3 F9_01_ACT_GVRN_NUM_VOTE_MEMB_IND TID-000-000-000
#> 4 F9_01_ACT_GVRN_EMPL_TOT TID-000-000-000
#> VALUE
#> 1 THE CF SUPPORTS CHARITABLE, LITERARY AND EDUCATIONAL PROGRAMS IN THE NORTHERN REGION OF VA.
#> 2 14
#> 3 14
#> 4 5
# 1:M grant #1 vs grant #2 -> different TABLE_IDs:
flat |>
filter(grepl("IRS990ScheduleI/RecipientTable", XPATH2),
VARIABLE_NAME == "SI_02_GRANT_US_ORG_AMT_CASH") |>
select(TABLE_ID, VARIABLE_NAME, VALUE) |>
head(4)
#> TABLE_ID VARIABLE_NAME VALUE
#> 1 TID-000-000-001 SI_02_GRANT_US_ORG_AMT_CASH 40250
#> 2 TID-000-000-002 SI_02_GRANT_US_ORG_AMT_CASH 37729
#> 3 TID-000-000-003 SI_02_GRANT_US_ORG_AMT_CASH 77417
#> 4 TID-000-000-004 SI_02_GRANT_US_ORG_AMT_CASH 63008Step 5 — Labeling XPaths with concordance variable names
Flattening joins XPATH2 against the packaged concordance
to attach a standardized VARIABLE_NAME and its logical
RDB_TABLE. This is why XPATH2 must have the
[N] indices stripped —
RecipientTable[1]/CashGrantAmt and
RecipientTable[2]/CashGrantAmt are the same
variable, and both must match the single concordance entry
RecipientTable/CashGrantAmt.
flat |>
filter(grepl("IRS990ScheduleI/RecipientTable", XPATH2), TYPE == "terminal") |>
select(XPATH2, RDB_TABLE, VARIABLE_NAME, VALUE) |>
head(4)
#> XPATH2
#> 1 /Return/ReturnData/IRS990ScheduleI/RecipientTable/RecipientBusinessName/BusinessNameLine1Txt
#> 2 /Return/ReturnData/IRS990ScheduleI/RecipientTable/USAddress/AddressLine1Txt
#> 3 /Return/ReturnData/IRS990ScheduleI/RecipientTable/USAddress/CityNm
#> 4 /Return/ReturnData/IRS990ScheduleI/RecipientTable/USAddress/StateAbbreviationCd
#> RDB_TABLE VARIABLE_NAME VALUE
#> 1 SI-P02-T01-GRANTS-US-ORGS-GOVTS SI_02_GRANT_US_ORG_NAME_L1 100WOMEN STRONG
#> 2 SI-P02-T01-GRANTS-US-ORGS-GOVTS SI_02_GRANT_US_ORG_ADDR_L1 714 EAST MARKET STREET
#> 3 SI-P02-T01-GRANTS-US-ORGS-GOVTS SI_02_GRANT_US_ORG_ADDR_CITY LEESBURG
#> 4 SI-P02-T01-GRANTS-US-ORGS-GOVTS SI_02_GRANT_US_ORG_ADDR_STATE VATracking XPaths that aren’t in the concordance yet.
When a path has no concordance entry, flatten_xml() does
not drop it — it keeps the row, falls back to the raw node name (via
get_vnames()), and leaves RDB_TABLE blank.
This filing happens to be fully covered by the concordance:
c(labeled = sum(flat$TYPE == "terminal" & flat$RDB_TABLE != ""),
unlabeled = sum(flat$TYPE == "terminal" & flat$RDB_TABLE == ""))
#> labeled unlabeled
#> 1383 0To see the fallback in action, flatten a snippet containing a node the concordance has never seen:
mini <- xml2::read_xml(
"<Return><ReturnData><IRS990><BrandNewFutureField>42</BrandNewFutureField></IRS990></ReturnData></Return>")
xml2::xml_ns_strip(mini)
flatten_xml(mini, "example") |>
filter(TYPE == "terminal") |>
select(XPATH2, RDB_TABLE, VARIABLE_NAME, VALUE)
#> XPATH2 RDB_TABLE VARIABLE_NAME VALUE
#> 1 /Return/ReturnData/IRS990/BrandNewFutureField BrandNewFutureField 42BrandNewFutureField isn’t in the concordance, so
RDB_TABLE stays blank and VARIABLE_NAME
defaults to the node name. Across a whole corpus,
generate_xpath_report() and process_xpaths()
roll these fallbacks up into a report of every distinct
XPATH2, how often it occurs, and which schema versions it
appears in. Joining that report to the concordance
(all = TRUE) surfaces exactly which XPaths are still
unmapped — the worklist for extending the concordance
to cover new or rare fields.
Step 6 — Attributes are a separate table
Not all data lives in element text. Some is stored in XML
attributes — for example, a node may carry
referenceDocumentId pointing at a supporting statement.
Element text is what flatten_xml() captures; attributes are
pulled out separately by get_attr_df() into their own tidy
table (OBJECTID, node_name,
xpath, attr_name, attr_value),
which ef2 stores as the ATTRIBUTES table in
DuckDB alongside FLATXML:
attrs <- get_attr_df(doc, url)
dim(attrs)
#> [1] 32 5
count(attrs, attr_name, name = "n")
#> attr_name n
#> 1 binaryAttachmentCnt 1
#> 2 documentCnt 1
#> 3 documentId 7
#> 4 referenceDocumentId 20
#> 5 returnVersion 1
#> 6 schemaLocation 1
#> 7 xmlns:xsi 1Step 7 — Keys
Finally, get_keys() extracts the filing-level metadata
that keys every table — the EIN, organization name, tax period, return
type/flags, and schema version:
str(get_keys(doc, url))
#> List of 16
#> $ EIN2 : chr "EIN-54-1950727"
#> $ OBJECTID : chr "OID-202341529349301414"
#> $ ORG_EIN : chr "541950727"
#> $ ORG_NAME_L1 : chr "COMMUNITY FOUNDATION FOR LOUDOUN AND"
#> $ ORG_NAME_L2 : chr "NORTHERN FAUQUIER COUNTIES"
#> $ RETURN_AMENDED_X : logi TRUE
#> $ RETURN_GROUP_X : logi FALSE
#> $ RETURN_PARTIAL_X : logi FALSE
#> $ RETURN_TAXPER_DAYS : num 365
#> $ RETURN_TIME_STAMP : chr "2023-06-01T15:20:06-05:00"
#> $ RETURN_TYPE : chr "990"
#> $ TAX_PERIOD_BEGIN_DATE: chr "2022-01-01"
#> $ TAX_PERIOD_END_DATE : chr "2022-12-31"
#> $ TAX_YEAR : chr "2022"
#> $ URL : chr "https://gt990datalake-rawdata.s3.amazonaws.com/EfileData/XmlFiles/202341529349301414_public.xml"
#> $ VERSION : chr "2022v5.0"What you learned
-
flatten_xml()turns one nested filing into one row per node. -
TYPEdistinguishes structural parent/root nodes from data-bearing terminal/leaf nodes; only terminals are extracted. -
TABLE_HEADERis a root grouping node with no data of its own. -
TABLE_IDencodes repetition:TID-000-000-000= one-to-one,TID-000-000-00N= the Nth row of a one-to-many group. -
XPATH2(indices stripped) joins the concordance to attachVARIABLE_NAME/RDB_TABLE; unmapped paths keep their node name and are tracked for concordance updates. - Attributes and keys are extracted into their own tables.
Next: “Extracting One-to-One Tables” and “Extracting One-to-Many Tables” pivot this flattened output into analysis-ready tables.