LOAD XML File into a table – MySQL

This article shows how to import XML data into a MySQL table. Once the data is imported into the table, you’ll see the data in rows and columns.

Dataset: Copy the following data and save it into your local file system.

<CustInfo>
<Customer><field name=”CustomerID”>1</field><field name=”CustomerName”>Dalillah</field>
<field name=”PhoneNumber”>00971305374799</field></Customer>
<Customer><field name=”CustomerID”>2</field><field name=”CustomerName”>Melanie</field>
<field name=”PhoneNumber”>00971305374776</field></Customer>
</CustInfo>

Now go to your MySQL instance and create a table to accommodate these values.

CREATE TABLE CustTest(
CustomerID INT,
CustomerName VARCHAR(100),
PhoneNumber VARCHAR(100)
);

Now load the XML data into the table using the following statement.

LOAD XML LOCAL INFILE
‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CustInfo.xml’
INTO TABLE CustTest
ROWS IDENTIFIED BY ‘<Customer>’;

Now, retrieve the data from the table.

SELECT * FROM CustTest;

If you are facing any of the following error messages –

• ERROR 1238 (HY000): Variable ‘secure_file_priv’ is a read only variable
• 3948 – Loading local data is disabled; this must be enabled on both the client and server sides
• File ‘data.txt’ not found (Errcode: 2)

then refer to the earlier post that lets you know how to fix it.