2.3 Import XML

Import an XML file into a Swift Document as follows:

SwiftCmd.exe importXML Data.xdb C:\Path\To\XML\file.xml

Import multiple XML files as follows:

SwiftCmd.exe importXML Data.xdb file1.xml file2.xml ..

To specify a wildcard to select multiple XML files, use the following command line.:

SwiftCmd.exe importXML Data.xdb *.xml

Import XML from a URL be specifying the URL as follows:

SwiftCmd.exe importXML Data.xdb http://www.example.com/path/to/file.xml

If the website requires a user name and password, please specify the URL in the following format. Note that password should be specified in clear text form.

http://username:password@www.example.com/path/to/file.xml

You could also fetch files using FTP by specifying the URL as follows:

ftp://username:password@ftp.example.com/path/to/file.xml

Importing XML URLs from an input file

When you need to import files from many different locations or URLs, it is easier to store the locations in a text file rather than list them on the command line. In this case, the text file should contain one URL or path per line. For local paths, file specification using wildcards are supported.

SwiftCmd.exe -i input.txt importXML Data.xdb

The file ‘input.txt’ contains the following:

http://weather.gov/data/current_obs/KNMM.xml
http://weather.gov/data/current_obs/KPIB.xml
http://weather.gov/data/current_obs/KPQL.xml
http://weather.gov/data/current_obs/KTUP.xml
http://weather.gov/data/current_obs/KHKS.xml
http://weather.gov/data/current_obs/KOLV.xml
http://weather.gov/data/current_obs/KPGL.xml
http://weather.gov/data/current_obs/K3DU.xml

Repeated XML Elements (no normalization)

When repeated XML elements are encountered within a parent element, the data is inserted into the same table as determined by the parent element. A numbered prefix is added to the column name to distinguish the instances. For instance, consider the following XML snippet:

<address>
 <line>123 Main Street</line>
 <line>Suite 1</line>
 <city>Anytown</city>
 <state>Any State</state>
 <zip>12345</zip>
</address>

This snippet creates a table called ‘address’ with the following structure:

Table ‘address’
---------------------------------------------------------
line1           | line2   | city    | state     | zip   |
---------------------------------------------------------
123 Main Street | Suite 1 | Anytown | Any State | 12345 |
---------------------------------------------------------

Normalizing repeated XML elements

However, in some cases, there may be too many repeated elements to be inserted into the same table in this manner. Or the data might better fit into a separate table. In these cases, you can normalize the data so that repeated elements are inserted into a child table which is then linked to the parent table. From the command line, you can use the -N option to request this type of normalization.

For example, consider the following XML snippet:

<credits>
  <director>Martin Scorsese</director>
  <actor>Paul Newman</actor>
  <actor>Tom Cruise</actor>
  <actor>Mary Elizabeth Mastrantonio</actor>
  <actor>Helen Shaver</actor>
  <actor>John Turturro</actor>
  <actor>Bill Cobbs</actor>
  <actor>Robert Agins</actor>
  <actor>Forest Whitaker</actor>
</credits>

With the -N option, two tables would be created as shown which are linked using primary/foreign key relationships:

Table ‘credits’ is the parent table.

Table ‘credits’
---------------------------
PKEY | director
---------------------------
1    | Martin Scorsese	
---------------------------

Table ‘actor’ is the child table and linked to its parent ‘credits’ with the FKEY field. As you can see, these 8 rows in ‘actor’ are linked to the same row in the parent table ‘actor’.

Table ‘actor’
-----------------------------------------------
PKEY | FKEY | actor
-----------------------------------------------
1    | 1    | Paul Newman
2    | 1    | Tom Cruise
3    | 1    | Mary Elizabeth Mastrantonio
4    | 1    | Helen Shaver
5    | 1    | John Turturro
6    | 1    | Bill Cobbs
7    | 1    | Robert Agins
8    | 1    | Forest Whitaker
-----------------------------------------------