xml spy
Previous  Top  Next
Import and Export

Before you implement your import and export tasks with the XMLSpy API, it is a good practice to test the connections, parameters, SLQ queries and so on in XML Spy. This way, you are able to verify the results and to make quick adjustments to all import or export parameters.

Most of the methods for importing and exporting data are placed in the "Application" object, the remaining functions are accessible via the "Document" interface.

There is some preparatory work necessary, before the actual import or export can be started. Every import/export job consists of two parts. You need to define a connection to your data and the specific behaviour for the import/export process.

In case of an import, the connection is either a database, a text-file or a Word document. The behaviour is basically which data (columns) should be imported in XMLSpy.

In case of an export, the connection is either a database or a text file. Specify which data (elements of the XML file) and additional parameters (e.g. automatic key generation or number of sub-levels) to use from the XML-structure for the behaviour.

The properties in the DatabaseConnection, TextImportExportSettings and ExportSettings interfaces have default values. See the corresponding descriptions in the "Interfaces" chapter for further information.

Import from database
These are the steps to establish a connection to an existing database for import:

1.Use a "DatabaseConnection" object and set the properties:  
The method "Application.GetDatabaseSettings" returns a new object for a database connection:  

   Dim objImpSettings As DatabaseConnection
   Set objImpSettings = objSpy.GetDatabaseSettings

You have to set either a ADO connection string,

   objImpSettings.ADOConnection = strADOConnection

or the path to an existing database file:

      objImpSettings.File = "C:\myDatabase.mdb"

To complete the settings you create a SQL select statement to define the data to be queried:

      objImpSettings.SQLSelect = "SELECT * FROM myTable"

2.Call "Application.GetDatabaseImportElementList" to get a collection of the resulting columns of the SQL query:  
 
   Dim objElementList As ElementList  
   Set objElementList = objSpy.GetDatabaseImportElementList(objImpSettings)  
 
This collection gives you the opportunity to control which columns should be imported and what type the new elements will become. Each item of the collection represents one column to import. If you remove an item, the corresponding column will not be imported. You can additionally modify the "ElementListItem.ElementKind" property, to set the type of the created XML elements for each column.  
 
Please consider that GetDatabaseImportElementList() executes the SQL query and could initiate a time consuming call. To avoid this, it is possible to pass a null-pointer (Nothing in VisualBasic) as the second parameter to ImportFromDatabase() to import all columns as plain XML elements.  

3.Start the import with "Application.ImportFromDatabase":  

   Dim objImpDoc As Document  
   Set objImpDoc = objSpy.ImportFromDatabase(objImpSettings,objElementList)  

Import from Text
Importing data from a text file is similar to the import from a database. You must use other interfaces (described in steps 1-3 below) with different methods and properties:

1.Use a "TextImportExportSettings" object and set the properties:  
The method "Application.GetTextImportExportSettings" returns a new object to specify a text file for import.  

   Dim objImpSettings As TextImportExportSettings  
   Set objImpSettings = objSpy.GetTextImportExportSettings  
 
You have to set at least the ImportFile property to the path of the file for the import. Another important property is HeaderRow. Set it to False, if the text file does not contain a leading line as a header row.  
 
   objImpSettings.ImportFile = "C:\myFile.txt"  
   objImpSettings.HeaderRow = False  
 
     
2.   Call "Application.GetTextImportElementList" to get a collection of all columns inside the text file:  
 
   Dim objElementList As ElementList  
   Set objElementList = objSpy.GetTextImportElementList(objImpSettings)  

3.Start the import with "Application.ImportFromText":  

   Dim objImpDoc As Document  
   Set objImpDoc = objSpy.ImportFromText(objImpSettings,objElementList)  

Export to database

1.Use a "DatabaseConnection" object and set the necessary properties.  
All properties except "SQLSelect" are important for the export. "ADOConnection" or "File" defines the target for the output. You need to set only one of them.  

2.Fill an "ExportSettings" object with the required values.  
These properties are the same options as those available in the export dialog of XMLSpy. Select the menu option Convert | Export to Text files/Database to see the options and try a combination of export settings. After that it is easy to transfer these settings to the properties of the interface.  
 
Call "Application.GetExportSettings" to get a ExportSettings object:  
 
   Dim objExpSettings As ExportSettings  
   Set objExpSettings = objSpy.GetExportSettings  
 
   objExpSettings.CreateKeys = False  
   objExpSettings.ExportAllElements = False  
   objExpSettings.SubLevelLimit = 2  

3.Build an element list with "Document.GetExportElementList".  
The element list enables you to eliminate XML elements from the export process. It also gives you information about the record and field count in the "RecordCount" and "FieldCount" properties. Set the "ExportSettings.ElementList" property to this collection. It is possible to set the element list to null/Nothing (default) to export all elements.  
 
4.Call "Document.ExportToDatabase" to execute the export.  
The description of the ExportToDatabase method contains also a code example for a database export.  
 
Export to text

1.Use a "TextImportExportSettings" object and set the necessary properties.  
 
2.Fill an "ExportSettings" object with the required values.  
See item number 2 from "Export to database" earlier on this page.  
 
3.   Build an element list with "Document.GetExportElementList".  
See item number 3 from "Export to database" earlier on this page.  
 
4.   Call "Document.ExportToText" to execute the export.  
The description of the ExportToText method contains also a code example for a database export.  
 

Previous  Top  Next

© 2002 Altova