LOAD XML

Syntax:
LOAD XML
[LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE [db_name.]tbl_name
[CHARACTER SET charset_name]
[ROWS IDENTIFIED BY '<tagname>']
[IGNORE number {LINES | ROWS}]
[(field_name_or_user_var
[, field_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT},
[, col_name={expr | DEFAULT}] ...]

The LOAD XML statement reads data from an XML file into a table. The
file_name must be given as a literal string. The tagname in the
optional ROWS IDENTIFIED BY clause must also be given as a literal
string, and must be surrounded by angle brackets (< and >).

LOAD XML acts as the complement of running the mysql client in XML
output mode (that is, starting the client with the --xml option). To
write data from a table to an XML file, you can invoke the mysql client
with the --xml and -e options from the system shell, as shown here:

shell> mysql --xml -e 'SELECT * FROM mydb.mytable' > file.xml

To read the file back into a table, use LOAD XML. By default, the <row>
element is considered to be the equivalent of a database table row;
this can be changed using the ROWS IDENTIFIED BY clause.

This statement supports three different XML formats:

o Column names as attributes and column values as attribute values:

<row column1="value1" column2="value2" .../>

o Column names as tags and column values as the content of these tags:

<row>
<column1>value1</column1>
<column2>value2</column2>
</row>

o Column names are the name attributes of <field> tags, and values are
the contents of these tags:

<row>
<field name='column1'>value1</field>
<field name='column2'>value2</field>
</row>

This is the format used by other MySQL tools, such as mysqldump.

All three formats can be used in the same XML file; the import routine
automatically detects the format for each row and interprets it
correctly. Tags are matched based on the tag or attribute name and the
column name.

Prior to MySQL 8.0.21, LOAD XML did not support CDATA sections in the
source XML. (Bug #30753708, Bug #98199)

The following clauses work essentially the same way for LOAD XML as
they do for LOAD DATA:

o LOW_PRIORITY or CONCURRENT

o LOCAL

o REPLACE or IGNORE

o CHARACTER SET

o SET

See [HELP LOAD DATA], for more information about these clauses.

(field_name_or_user_var, ...) is a list of one or more comma-separated
XML fields or user variables. The name of a user variable used for this
purpose must match the name of a field from the XML file, prefixed with
@. You can use field names to select only desired fields. User
variables can be employed to store the corresponding field values for
subsequent re-use.

The IGNORE number LINES or IGNORE number ROWS clause causes the first
number rows in the XML file to be skipped. It is analogous to the LOAD
DATA statement's IGNORE ... LINES clause.

URL: https://dev.mysql.com/doc/refman/8.0/en/load-xml.html