1

I want to import XML data which contains ampersands into MySQL.

The import fails to run after a row has been encountered with a raw ampersand (&). Admittedly this is not correct XML but that is what I am working with.

I have tried replacing the raw ampersands with & - this appears in the database as the raw text (not the equivalent ASCII ampersand).

I have tried replacing the raw ampersands with \& - this stops the import routine from running further.

Can you suggest how I can get the raw ampersand into the database using LOAD XML LOCAL INFILE?

Sample raw XML follows:

<?xml version="1.0" ?>
<REPORT>
    <CLA>
        <PLOT>R&S</PLOT>
        <VAL>100.10</VAL>
    </CLA>
    <CLA>
        <PLOT>G&N</PLOT>
        <VAL>200.20</VAL>
    </CLA>
</REPORT>
kjhughes
  • 106,133
  • 27
  • 181
  • 240
sidewaysglance
  • 177
  • 3
  • 11

1 Answers1

1

Admittedly this is not correct xml but that is what I am working with.

No, it's not that it's incorrect XML. It is not XML at all because it is not well-formed.

You have two ways forward:

  1. Fix the data by treating it as text to turn it into XML. (Replace the & with &amp;.)
  2. Load the data into the database using a non-XML data type.
Community
  • 1
  • 1
kjhughes
  • 106,133
  • 27
  • 181
  • 240