WL#1397: convert XML -> SQL

Affects: Server-5.5 — Status: Complete — Priority: Low

SUMMARY
-------

As a complement to --xml mode of mysqldump (and mysql) we can provide
an XSL that converts our XML dump to valid SQL dump. As far as I
understand it should be rather easy.


DECISIONS REGARDING THIS WL
---------------------------
- Jeffrey has decided that this is not for 5.1.
  Lars thinks this should be planned for 5.2.
  -- Lars, 2007-02-21

Please find the original information from the contributor here:



http://lists.mysql.com/internals/34169

http://lists.mysql.com/internals/34170





The final syntax is a little bit different:



LOAD XML [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'

    [REPLACE | IGNORE]

    INTO TABLE tbl_name

    [CHARACTER SET charset_name]

    [ROWS IDENTIFIED BY 'row_tag']

    [IGNORE number [LINES|ROWS]]

    [(col_name_or_user_var,...)]

    [SET col_name = expr,...)]



Difference comparing to the original Erik's patch:



1. "IGNORE 10 ROWS" is also understood as a synonym to "IGNORE 10 LINES",

because "lines" is something not well applicable to XML.

Note, as a side effect "IGNORE 10 ROWS" also works in "LOAD DATA" syntax.



2. "ROWS IDENTIFIER BY" is optional. The default tag is '<row>'.



3. "CHARACTER SET charset_name" is also possible, like in "LOAD DATA".
LLD is found in the contributed patches for this feature.



There's a contributed patch from Erik Wetterberg

implementing "LOAD XML" syntax, hardcoded

to understand only the format of "mysqldump --xml" output,

but doing it very efficiently:



http://lists.mysql.com/commits/20059





There's also not-so-efficient XPath-based solution,

but it is not restricted to only "mysqldump --xml" format,

and can load data from any XML files, which can

be useful to import data from third party applications:



http://lists.mysql.com/commits/20060

You must be logged in to tag this worklog

On Jul 6, 2007 @ 10:46, Erik Wetterberg said:

Seems like your software strips not only HTML but also xml, so my examples were removed.

Erik

On Jul 6, 2007 @ 10:27, Erik Wetterberg said:

My contribution is not limited to just the (strange) mysqldump --xml format. Rather it is written to support 1) fields as tags: value1 2) fields as attributes:

3) mysqldump -xml format: value1

It also supports hierarchical xml structures like this:

This could be loaded into two tables, invoice and item, and set the invoice id in both to keep the connection from the xml.

There are things that are not supported, like CDATA and BLOBs. There is also a minor bug with the SKIP ROWS option. I would be glad to fix this, but since it is not included in a release available to me, I can't.

I also don't really see the point in working on contributions that are never released.

Erik

Votes

Not yet rated.
You must be logged in to vote.

Watches

0 members are watching this worklog
You must be logged in to track this worklog.

Provide Feedback

Please note:
HTML will be purified, but we allow for a number of HTML tags so that you have the flexibility to decorate your comment text to some extent. The comments allow the following HTML tags:

strong, b, em, blockquote, a, code, pre

To put code into your comment, simply encapsulate your code with
[code language="XXX"][/code], where XXX is any common language, for instance "PHP", "SQL", "C", etc.

You must be logged in to comment