In the previous lesson, you learned how to insert data into a database using text files. In DatabaseSpy, you can also use XML files to export data from, or import data into, a database. The tutorial folder of your DatabaseSpy installation includes also example XML files for this purpose.
In this step, you will import data from dbo.tblFeedSuppliers.xml into the tblFeedSupplier database table. This assumes you have not already populated this table by other means (for example, by importing data from tblFeedSuppliers.txt file into it, see Importing Data from TXT Files).
To import data from an XML file into a database table:
|1.||Click the Import button in the Tools toolbar, or select the menu option Tools | Import data to the database..., or press Ctrl+I. The Import data to the database dialog box opens.|
|2.||Click the XML button on the left side of the dialog box.|
|3.||In the Selection tab, click next to the Path field. Select dbo.tblFeedSuppliers.xml from the \Altova\DatabaseSpy2019\DatabaseSpyExamples\Tutorial sub-folder of the Documents folder.|
|4.||Under Database Generation Options, select Update existing tables.|
|5.||Under Import Mode, select Generate SQL in SQL Editor.|
|7.||Under Start point of import, in the "Starting from" list, select "Row". (This specifies the name of the XML element from which data will be imported).|
|8.||Under Automatic fields, deactivate the Create primary/foreign keys check box.|
|9.||Under Other options, select Exclude namespace name.|
|10.||Under Preview, click the Apply Options button. This displays table data in the Preview window. Note that the Import element now is not an available choice anymore. To view the table definition, choose the Show Definition radio button.|
|11.||Click inside the "Target Table Name" column and choose dbo.tblFeedSuppliers.xml from the drop-down list.|
|12.||Click the TreatmentID table icon until it changes to the symbol for a column not to be imported . The column header allows you to define whether it should be included during the import process. We will not import the TreatmentID column since this is an identity column and the primary key will be generated automatically during import.|
At this stage, if you followed all the steps above, the import options must be as shown below (the two images below correspond to the Selection tab, and Options tab, respectively).
|13.||Click the Generate SQL button. This creates an SQL script which can be executed to complete the import process. Click OK in the message box to view the script in the SQL Editor.|
|14.||Click the Execute button or press F5.|
|15.||In the Online Browser, right-click the ZooDB database and select Refresh from the context menu.|
|16.||Right-click the tblMedicalTreatments table and select Retrieve data | All rows from the context menu or press Ctrl+Alt+R.|
© 2019 Altova GmbH