Help Import, export and backup
Importing XML data
Import XML Data Using LOAD LOCAL loads rows from an XML file into an existing table. bonSQL builds a
LOAD XML LOCAL INFILE statement from your choices and runs it on the current connection, so the file is
read from your computer, not from the server.
The table must exist already. To make one, see Creating a table.
Opening it
Select the table, or its database, in the Object Browser, then:
- Table > Import > Import XML Data Using LOAD LOCAL…
Tables and Columns
Tables lists the tables of the database, with the one selected in the Object Browser chosen. Columns lists its columns. Select the columns to fill (a click selects or deselects one column); only those are named in the statement. Select All and Deselect All help with long lists.
Charset
The character set the file is written in. When you choose the file, bonSQL looks at its start: it picks
ucs2 for a UTF-16 file and utf8 for UTF-8 text it recognises, and otherwise leaves the list on
[default], which adds no CHARACTER SET to the statement.
Rows Identified by
Tick Rows Identified by and enter the name of the element that holds one row, without the angle
brackets; bonSQL adds ROWS IDENTIFIED BY '<name>' to the statement. The box starts as row. Open the
file in a text editor first to see which element holds a row.
Other Options
Each option adds a word to the statement. What it does is up to the server; in short, Replace and Ignore decide what happens to a row whose key is already in the table.
| Option | Adds |
|---|---|
| Low priority | LOW_PRIORITY |
| Concurrent | CONCURRENT. Cannot be combined with Low priority. |
| Replace | REPLACE |
| Ignore | IGNORE. Cannot be combined with Replace. |
| Ignore n lines | IGNORE n LINES, to skip the start of the file. |
Running it
Enter the file in Import from file, or click … to choose it, then click Import…. When the server has finished, a window titled XML Import Result shows its summary of the load. If there were warnings, click Show Warnings… to read them.
bonSQL remembers the file name and the options for next time.
Tips
- Try a small file first and read Show Warnings… before loading a large one.
When it does not work
- The server refuses the statement: the server must allow
LOAD XML LOCAL. If it does not, it refuses the statement and bonSQL shows the server’s message; ask the server’s administrator. - No rows are loaded: check that Rows Identified by names the element that holds a row.
- Please specify a filename or Select at least one field: Import from file is empty, or no column is selected.