I'm working with PDM archive data and trying to understand whether there's a practical way to extract this information into SQL for analysis, reporting, or internal tools.

Every document folder in the SOLIDWORKS PDM archive has its own `index.xml`. It contains information about the different versions of that document, and sometimes a version points (`ref`) to an older version instead of having its own archived file.

The idea is to have a SQL table that can show, for every document version:

* Document ID

* Archive folder/path

* Actual archived filename (`00000001.pdf`, etc.)

* Version information

* UID

* `ref`, if applicable

* The actual physical archived file associated with that version

The tricky part is the `ref` relationship. If a version doesn't have its own archived file, I need to follow the `ref` back to the referenced version until I can determine which physical file belongs to that version.

At the moment, I'm using `xp_cmdshell` to find the `index.xml` files and then processing them individually in T-SQL with `OPENROWSET` and `sp_executesql`.

But there is something I'm not sure about.

Is this `ref` information stored somewhere in the PDM database as well?

I don't know whether PDM stores this information in one of the existing tables, whether it is derived or encoded in some other form, or whether `index.xml` is the actual place where this relationship is maintained.

One thing that made me even more curious is the Archive Consistency Check** tool in PDM. It can check the archive structure and file consistency across a vault, which made me wonder whether PDM is using some internal metadata or mechanism that I'm not seeing.

If the information is already available somewhere in the database, I'd like to understand where and in what form PDM maintains it instead of assuming that the XML files are the only source.

And if `index.xml` really is the source of truth, I'd like to understand the best way to process this archive information at scale and bring it into SQL.

I'm mainly looking for insight from PDM administrators who have worked with the archive structure or built internal reporting/analysis tools.

Does anyone know how PDM maintains or resolves this version → `ref` → physical file relationship behind the scenes?