Parsing SOLIDWORKS PDM Archive index.xml into SQL — Version History Extraction Guide

A question posted on the 3DEXPERIENCE forum in August 2026 had zero replies after several days: how do you extract the version history stored in the PDM archive’s index.xml files into SQL for reporting or internal tooling? The poster wanted Document ID, archive folder path, actual archived filename (like 00000001), and the full version chain, including entries that use a ref pointer instead of storing their own copy.

No answer existed. No Dassault KB article covers it. No public blog post addresses it. This is that guide.

Two data sources, one version history

SOLIDWORKS PDM stores version history in two places simultaneously:

1. The SQL database — the Revisions table holds version metadata: who checked in, when, comment, file size, workflow state. It does not tell you the physical filename in the archive.

2. The archive server file system — each document folder holds the actual files (00000001, 00000002, …) and an index.xml that tracks which versions have their own file vs. which use a ref pointer to an older version.

For reporting on the SQL side alone you lose the physical file chain. For walking the archive XML alone you lose the check-in metadata. Joining the two gives you the complete picture: a row per version with committer, date, comment, physical archive path, and file size.

The SQL schema you need

These four tables cover the complete version history:

Documents — one row per file in the vault:

DocumentsInProjects — links files to vault folders:

Projects — vault folder tree:

Revisions — one row per checked-in version:

The base SQL query to get all version history for a vault:

SELECT
    d.DocumentID,
    d.Filename,
    p.Path                  AS VaultPath,
    r.RevNr                 AS Version,
    r.Date                  AS CheckInDate,
    u.Username              AS CheckedInBy,
    r.Comment,
    r.FileSize,
    r.FlushedDate
FROM Revisions r
JOIN Documents d         ON d.DocumentID  = r.DocumentID
JOIN DocumentsInProjects dip ON dip.DocumentID = d.DocumentID
                             AND dip.Deleted = 0
JOIN Projects p          ON p.ProjectID   = dip.ProjectID
LEFT JOIN Users u        ON u.UserID      = r.UserID
ORDER BY d.DocumentID, r.RevNr;

Connect to the vault’s SQL instance using Windows authentication (the vault database name matches your vault name). Run this in SSMS or from Python via pyodbc.

This gives you the full version history from SQL. It does not tell you the physical archive filename (00000001, etc.) or whether a given version’s file is missing from the archive (flushed/deduped).

The archive file system layout

The archive server stores files in a folder tree rooted at the archive path configured in the PDM Administration Tool. To find your archive root: in the Admin Tool, right-click the vault → Properties → Archive tab.

Each document gets its own subfolder named from its DocumentID converted to uppercase hexadecimal, zero-padded to 8 characters. The folder path is then split into two levels: the first 2 characters form the top-level directory, the remaining 6 characters form the subfolder:

<ArchiveRoot>\
  00\
    000001\     <- DocumentID 1  (0x00000001)
    000047\     <- DocumentID 71 (0x00000047)
  00\
    0004D2\     <- DocumentID 1234 (0x000004D2)
  ...

To derive the archive path from a DocumentID in Python:

def doc_id_to_archive_path(archive_root: str, document_id: int) -> str:
    hex_id = f"{document_id:08X}"          # e.g., 000004D2
    top    = hex_id[:2]                    # 00
    sub    = hex_id[2:]                    # 0004D2
    return os.path.join(archive_root, top, sub)

Inside each document folder:

000004D2\
  index.xml
  00000001          <- version 1 file (no extension)
  00000002          <- version 2 file (no extension)
  00000003.gz       <- version 3 file (gzip-compressed)

Versions are zero-padded to 8 digits. A .gz extension means the archive server compressed that version. Absence of a numbered file for a given version means that version uses a ref pointer (see below).

The index.xml schema

Every document folder has an index.xml. Its structure:

<?xml version="1.0" encoding="UTF-8"?>
<Index>
  <Entry version="1" flags="0" size="245760" compressedSize="245760"/>
  <Entry version="2" flags="0" size="245760" compressedSize="0" ref="1"/>
  <Entry version="3" flags="2" size="298496" compressedSize="142357"/>
  <Entry version="4" flags="0" size="298496" compressedSize="0" ref="3"/>
</Index>

Attributes:

The ref field is content-based deduplication. When a user checks in a file that hasn’t actually changed (check-in comment update only, or a downstream rebuild that left the file byte-for-byte identical), PDM detects the duplicate and writes a ref entry instead of storing a second copy. Version 2 in the example above has the same content as version 1 — no 00000002 file exists on disk. To get the actual file for version 2 you read 00000001.

ref chains can be longer than one hop but in practice are never circular. Resolving a chain:

def resolve_ref(entries: dict, version: int) -> int:
    """Walk ref chain to find the version with an actual file."""
    visited = set()
    while version in entries and "ref" in entries[version]:
        if version in visited:
            raise ValueError(f"Circular ref chain at version {version}")
        visited.add(version)
        version = entries[version]["ref"]
    return version

Complete Python extraction script

This script walks the archive, parses every index.xml, queries SQL for metadata, and writes a joined CSV. Run it from any machine with archive share access and SQL access:

import os
import csv
import xml.etree.ElementTree as ET
import pyodbc

ARCHIVE_ROOT   = r"\\pdm-server\SWPDMArchive"   # adjust to your path
SQL_SERVER     = "pdm-sql\\SOLIDWORKS"            # or "pdm-sql,1433"
VAULT_DB       = "MyVaultName"                    # vault database name
OUTPUT_CSV     = "pdm_version_history.csv"

# ── SQL connection ────────────────────────────────────────────────────────────
conn_str = (
    f"DRIVER={{SQL Server}};"
    f"SERVER={SQL_SERVER};"
    f"DATABASE={VAULT_DB};"
    "Trusted_Connection=yes;"
)
conn   = pyodbc.connect(conn_str)
cursor = conn.cursor()

# ── Load SQL version metadata ─────────────────────────────────────────────────
cursor.execute("""
    SELECT
        d.DocumentID,
        d.Filename,
        p.Path          AS VaultPath,
        r.RevNr         AS Version,
        r.Date          AS CheckInDate,
        u.Username      AS CheckedInBy,
        r.Comment,
        r.FileSize,
        CASE WHEN r.FlushedDate IS NULL THEN 0 ELSE 1 END AS IsFlushed
    FROM Revisions r
    JOIN Documents d         ON d.DocumentID  = r.DocumentID
    JOIN DocumentsInProjects dip ON dip.DocumentID = d.DocumentID
                                 AND dip.Deleted = 0
    JOIN Projects p          ON p.ProjectID   = dip.ProjectID
    LEFT JOIN Users u        ON u.UserID      = r.UserID
""")

sql_rows = {}   # (document_id, version) -> row dict
for row in cursor.fetchall():
    key = (row.DocumentID, row.Version)
    sql_rows[key] = {
        "document_id":  row.DocumentID,
        "filename":     row.Filename,
        "vault_path":   row.VaultPath,
        "version":      row.Version,
        "checkin_date": row.CheckInDate,
        "checked_in_by": row.CheckedInBy,
        "comment":      row.Comment,
        "file_size_sql": row.FileSize,
        "is_flushed":   row.IsFlushed,
    }

conn.close()

# ── Walk archive and parse index.xml files ───────────────────────────────────
def parse_index_xml(path: str) -> dict:
    """Returns {version_int: {flags, size, compressedSize, ref?}} dict."""
    try:
        tree = ET.parse(path)
    except ET.ParseError:
        return {}
    entries = {}
    for entry in tree.getroot().findall("Entry"):
        v = int(entry.get("version", 0))
        d = {
            "flags":          int(entry.get("flags",          0)),
            "size":           int(entry.get("size",           0)),
            "compressedSize": int(entry.get("compressedSize", 0)),
        }
        ref = entry.get("ref")
        if ref is not None:
            d["ref"] = int(ref)
        entries[v] = d
    return entries

def archive_path_for(document_id: int) -> str:
    hex_id = f"{document_id:08X}"
    return os.path.join(ARCHIVE_ROOT, hex_id[:2], hex_id[2:])

def resolve_ref(entries: dict, version: int) -> int:
    visited = set()
    while version in entries and "ref" in entries[version]:
        if version in visited:
            return version   # bail on cycle (should not happen)
        visited.add(version)
        version = entries[version]["ref"]
    return version

# ── Write output ─────────────────────────────────────────────────────────────
fieldnames = [
    "document_id", "filename", "vault_path", "version",
    "checkin_date", "checked_in_by", "comment",
    "archive_folder", "archive_file",
    "file_size_sql", "file_size_xml",
    "is_compressed", "has_ref", "resolved_version",
    "is_flushed", "xml_entry_missing",
]

with open(OUTPUT_CSV, "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writeheader()

    for (doc_id, version), meta in sql_rows.items():
        archive_folder = archive_path_for(doc_id)
        xml_path       = os.path.join(archive_folder, "index.xml")

        xml_entries    = parse_index_xml(xml_path) if os.path.exists(xml_path) else {}
        entry          = xml_entries.get(version)
        xml_missing    = entry is None

        if entry:
            has_ref          = "ref" in entry
            resolved_ver     = resolve_ref(xml_entries, version)
            resolved_file    = f"{resolved_ver:08d}"
            archive_file     = resolved_file
            file_size_xml    = entry["size"]
            is_compressed    = entry["flags"] == 2
        else:
            has_ref = resolved_ver = archive_file = is_compressed = None
            file_size_xml = None

        writer.writerow({
            **meta,
            "archive_folder":   archive_folder,
            "archive_file":     archive_file,
            "file_size_xml":    file_size_xml,
            "is_compressed":    is_compressed,
            "has_ref":          has_ref,
            "resolved_version": resolved_ver,
            "xml_entry_missing": xml_missing,
        })

print(f"Written {len(sql_rows)} rows to {OUTPUT_CSV}")

What the output tells you

Each row in the CSV corresponds to one checked-in version of one file. Key columns:

archive_file — the zero-padded filename on disk (e.g., 00000001). This is the resolved physical file after following any ref chain. To open or copy the actual stored content, read <archive_folder>\<archive_file> (or <archive_file>.gz if is_compressed is true).

has_ref — true when this version’s content is shared with an older version. For storage accounting, count only rows where has_ref is false (or null where resolved_version == version).

xml_entry_missing — true when SQL has a Revisions row for this version but the archive index.xml has no corresponding entry. This is an indicator of archive corruption or a partially-flushed vault. It is different from is_flushed: flushed versions are deliberately removed and have a FlushedDate in SQL; XML-missing versions are unexpected gaps.

file_size_sql vs file_size_xml — the SQL Revisions.FileSize and the index.xml size attribute should match. When they differ it typically means the index.xml was rebuilt by a PDM admin tool after a corruption event and the sizes were not correctly restored — a known failure mode with the PDM archive rebuild utility.

Querying the resulting data

With the CSV loaded into a SQL table (or using pandas in Python), common queries:

Total vault storage by version (excluding refs):

SELECT SUM(file_size_xml) AS total_bytes
FROM pdm_version_history
WHERE has_ref = 0 AND xml_entry_missing = 0 AND is_flushed = 0;

Files with archive/SQL size mismatch:

SELECT document_id, filename, version, file_size_sql, file_size_xml
FROM pdm_version_history
WHERE file_size_sql <> file_size_xml
  AND xml_entry_missing = 0
  AND is_flushed = 0;

Version count by user (who checks in the most):

SELECT checked_in_by, COUNT(*) AS check_ins
FROM pdm_version_history
GROUP BY checked_in_by
ORDER BY check_ins DESC;

Orphaned archive folders (document folder exists on disk but no SQL row): the script above only writes SQL-known documents. Invert the walk — find all archive subfolders, derive the DocumentID, check if SQL knows about it — to find any archive folders with no SQL record. These can accumulate after vault migrations or forced document deletions that bypassed PDM.

Known edge cases

Versions above the SQL maximum. If someone restored the archive from backup without restoring the SQL database to the same point, the archive may have higher version numbers than SQL has records for. The script handles this: SQL drives the iteration, so extra archive files are not written to the output.

Multi-value ref chains. Internally documented as always being length-1 but the resolve_ref function handles arbitrary depth. Do not assume any external tool resolves the chain correctly.

Compressed vs. uncompressed. The compressedSize attribute in the XML is the compressed (stored) size. The size attribute is always the uncompressed (original) size. The PDM archive server compresses files above a threshold configured per-vault. For storage calculations use size (uncompressed) for logical sizing and compressedSize (when non-zero) for physical disk usage.

PDM Standard vs. Professional. PDM Standard stores the archive on the same machine as the SQL database. The archive path is usually C:\SolidWorks PDM\<VaultName>. PDM Professional separates archive servers; the archive path is a UNC path. The script works for both — only the ARCHIVE_ROOT value changes.

Connecting to the broader automation picture

Once you have the full version history in SQL you can build:

• Audit reports for ISO/IATF compliance (every check-in, who, when, comment)
• Storage analysis to identify files worth flushing old versions from (the PDM flush-versions task needs a candidate list)
• Broken-ref detection for archive health monitoring
• Pre-migration validation when moving vaults between servers

The SolidWorks PDM batch plot automation guide and PDM-aware VBA macro approach cover the output side — generating files from the vault. This guide covers the audit side — understanding what versions of what files are actually stored. For the broader automation picture, the SolidWorks workflow automation guide outlines where vault-side scripting fits relative to macros and add-ins.