Working with XML Data: Import, Explore, and Convert

XML remains a common export format across enterprise systems, SOAP APIs, RSS feeds, and government open data portals. If you have ever tried to open an XML file in a spreadsheet, you know the result is usually a mess of nested tags rather than usable rows and columns.

ExploreMyData can import XML files directly and flatten them into a table you can filter, transform, and export. The entire process runs in your browser. Nothing is uploaded to a server.

How the import works

When you load an XML file, the parser walks the document tree and converts it to a JSON intermediate representation. That JSON is then loaded into DuckDB as a table.

Picking the rows. The parser looks at the direct children of the document root, groups them by tag name, and picks the tag that occurs most often. Those elements become your rows. Everything inside each one becomes columns.

Attributes become columns under their own bare name. An element like <item id="42"> produces a column called id, not @id. There is no sigil, which is convenient right up until an attribute and a child element share a name, at which case one overwrites the other. Nested structures are flattened using dot notation: author.name, author.email. Nesting deeper than three levels is collapsed to text, and you get a warning saying how many places that happened.

Namespace prefixes are stripped. A <dc:creator> element lands in a column called creator, and the same applies to namespaced attributes. This is what you want nine times out of ten. The tenth time, two namespaces share a local name, they collapse onto the same column, and the last one written wins. If a namespaced feed gives you fewer columns than you expected, that's why.

Values that look like numbers or booleans become numbers or booleans. XML has no types, so everything would otherwise arrive as text and nothing would be chartable or sortable as a quantity. The parser converts a leaf value only when it round-trips exactly: 42 becomes a number, true and false become booleans. Anything where converting would lose information stays text: a leading-zero code like 007, a trailing-zero decimal like 1.50, and very long digit strings such as account numbers. Product codes and zip codes survive intact, which is the whole point.

Example: importing an RSS feed

Consider a standard RSS feed exported from a blog or news site. The XML contains a <channel> element with multiple <item> children:

<rss version="2.0">
  <channel>
    <title>Engineering Blog</title>
    <item>
      <title>Deploying to Production</title>
      <link>https://example.com/deploy</link>
      <pubDate>Mon, 01 Apr 2026 08:00:00 GMT</pubDate>
    </item>
    <item>
      <title>Database Migration Patterns</title>
      <link>https://example.com/migrations</link>
      <pubDate>Fri, 28 Mar 2026 10:30:00 GMT</pubDate>
    </item>
  </channel>
</rss>

This is the case worth walking through carefully, because the obvious expectation is wrong. The document root here is <rss>, and it has exactly one child: <channel>. So the most frequent child tag is channel, with a count of one, and you get a single row plus a warning reading "Only 1 row detected. The XML structure may not be tabular." The two items end up flattened into item.title, item.link, and item.pubDate, and because both write to the same keys, only the second one survives. If you see one row and a warning, this is what happened.

The fix is to make the repeating element a direct child of the root: delete the <rss> wrapper so that <channel> is the root.

<channel>
  <title>Engineering Blog</title>
  <item>
    <title>Deploying to Production</title>
    <link>https://example.com/deploy</link>
    <pubDate>Mon, 01 Apr 2026 08:00:00 GMT</pubDate>
  </item>
  <item>
    <title>Database Migration Patterns</title>
    <link>https://example.com/migrations</link>
    <pubDate>Fri, 28 Mar 2026 10:30:00 GMT</pubDate>
  </item>
</channel>

Now the root's children are one <title> and two <item> elements. item wins on count, so each item becomes a row and the channel-level title drops out:

Each <item> becomes a row and its child elements become columns. From here you can click a header to sort, filter by title keyword, or convert the pubDate string to a proper date type for time-based analysis.

The general rule: if an import gives you one wide row instead of many narrow ones, your repeating element is buried under a wrapper. Lift it to the root, or point the XML to CSV tool at the record path you actually want.

Common XML sources

The same import process works for several common XML formats:

• RSS and Atom feeds: blog posts, podcast episodes, news articles. Each entry becomes a row with title, link, date, and description columns.
• SOAP API responses: many legacy enterprise APIs return XML. Save the response body as a file, then import it to inspect the payload as a table.
• CRM and ERP exports: Salesforce, SAP, and similar systems often provide XML export options. These tend to be deeply nested, but the flattening logic handles multiple levels.
• Government open data: regulatory filings, public records, and statistical datasets are frequently published as XML. The U.S. SEC EDGAR system and EU open data portals are typical examples.

After import: transform and export

Once your XML data is loaded as a table, you have access to the full pipeline. Common next steps:

1. Rename columns: XML element names are often verbose. Rename publicationDate to date for clarity.
2. Convert types: dates arrive as strings. Cast them to a date type so you can sort and filter by date range.
3. Filter rows: remove entries that don't match your criteria. Keep only items from the last 30 days, or only records with a specific status.
4. Aggregate: count entries by category, sum values by group, or compute averages. The grouping and aggregation guide covers this in detail.

When you are done, export the result in the format your downstream system expects:

• XML to CSV for spreadsheet or database import. From CSV you can chain to Excel if a colleague needs an XLSX, or Parquet for analytics handoff.

Import your XML file →