How to Generate SQL for an Archive

The CopyStorm Archive subsystem stores record data in database XML column types and many database professionals may not be familiar with how to write SQL queries to read archive data. Though we do not expect many people to write their own SQL queries against  CopyStorm archives, we have include a query generator to help a programmer to get started.

The basic procedure is:

  • Open the Archive Explore for the archive you want to query.
  • Select a table and the columns you want in the query.
  • Go to the Preview stage and click on the “Generate Query” button. The system will open a dialog which shows the SQL that matches your query. Copy and Paste the generated SQL and you will have a good starting point for  your own query.

The generated SQL queries provided by CopyStorm should help you avoid a deep dive into a study of how to build an XML column query for your specific database type.

To practice this procedure see:

Launch the Archive Explorer from the Archive Tab

Choose An Archive

Select a Table and Columns for the SQL

Open the Preview and Set a Filter

View the Generated SQL