Learn how to import XML data into an ACCESS Database (Admin only)
Learn how to import XML data into an SQL Server (Admin only)
For step-by-step instructions on Importing XML data into an ACCESS database, click here.
Please note that there are similar capabilities in MS Excel if you would prefer to use Excel rather than Access.
Included below are files that will help you import the XML data you get out of FAS Outcomes into an SQL server.
Importing XML data into SQL DB.pdf - This is a step-by-step document outlining how you can use the files below to import CAFAS XML data into a SQL Server database. The process is straightforward, but assumes knowledge of Microsoft SQL Server. Note that this document makes reference to files in a ".zip packet". The files from the ".zip packet" are the same as the files below.
CAFAS XSD Format.xml - This is an XSD file that describes the structure of the XML in the CAFAS export file. It can be used in conjunction with the process described in the document above, or can be used by any import process that can take advantage of XSD, such as using SSIS.
CAFAS XML to SQL.txt - This is a VB script file to actually load the data into SQL Server. It requires the installation of SQLXML 4.0 (described in the .pdf document above). After a few simple edits to point it to your own database, it can be run from the command line or just by double-clicking in Windows Explorer (all details for updating the script are found in the above .pdf document). Note that you will need to change the extension from .txt to .vbs for this file to work.
CAFAS Test Query.txt - These are some common queries (also documented in the .pdf document) that can be used as starting points for queries on the tables generated by this process and as tests that the data was imported successfully.