5.2 Relational Databases

Creating Database Scripts

Once you have completed your schema masking and the schema now matches your reporting requirements, you can generate a set of database scripts that can be used to create a set of related tables which match the loaded and masked schema. This can be done by selecting the 'Generate Scripts" button in the Relational Database ribbon:

Database name. The script will create a new database using the name provided in the "Database name" input box.  It is not however necessary to create a separate database for this purpose, and the scripts can be edited to instead create the tables inside an existing database.

Reporting subject. The reporting subject will be used as the database schema for all tables related to the current document.  This allows a single database to contain data for different reporting subjects without them interfering with each other.  A database schema is a prefix before a table, such as dbo.TableName.

Once the scripts have been generated, you can open them in SQL Server Management Studio, edit them (if necessary) and run them.  

Table dbo.XA_Datasets

The database scripts will create a table dbo.XA_Datasets.  This table is created in the default "dbo" database schema even if a different Reporting Subject was provided in the form above.  This is because this table would be shared by different reporting subjects using their own database schema.  For example, if you create a dataset record for a particular reporting entity for a particular period - you would refer to the same DataSetID across all reporting subjects.

When you attempt to import data, XML Authority will present the records of this table so you can specify which data needs to be imported.

Importing Data

To import data from Microsoft SQL, click the Import button:

You will be prompted to input the details of the relational database in order to import the data

Once connected you will be presented with the list f Datasets found in the table dbo.XA_Datasets:

XML Authority will only import data that reference the selected Dataset ID.