How to convert XML to SQL: The Clever Way [Updated 2023]

To convert XML to SQL some wizardry is needed. Unlike CSV files and database tables, XML files aren’t naturally organized into rows and columns: XML is hierarchical. In this way XML is similar to JSON.

In order to convert XML to SQL then, SQLizer must work out how to flatten XML data into a tabular form.

Here’s how to convert XML to SQL with SQLizer:

Step 1: Drag and Drop your XML file

Step 2: Select your options

Step 3: Hit convert

You’re nearly done - your SQL file will be ready in a few seconds. It’s really that easy.

Here’s a video showing the exact steps to convert XML to SQL in 30 seconds.

SQLizer makes converting XML files to SQL easy peasy but if you want to know how to do it yourself and build your own script, there are a few things to consider.

How to convert XML to SQL yourself

Because of the hierarchical nature of XML, any XML data must first be “flattened”. SQLizer achieves this by converting each XML element containing data into a row; and attributes into columns.

If you need to convert XML to SQL your initial XML file will look something like this:

?xml version="1.0" encoding="utf-8"? Products Product name="widgets" id="1234" Order id="1" customer_id="9" order_date="01/01/2014"/ Order id="2" customer_id="3" order_date="01/02/2014"/ Order id="3" customer_id="6" order_date="01/03/2014"/ Order id="4" customer_id="12" order_date="01/04/2014"/ /Product /Products

However, this needs flattening before you can convert it into SQL. SQLizer flattens the above XML data into the following named columns:

• Products
• Products_Product
• Products_Product_name
• Products_Product_id
• Products_Product_Order
• Products_Product_Order_customer_id
• Products_Product_Order_order_date
• Products_Product_Order_id

Now we can convert into SQL. Here’s the resulting SQL script after SQLizer works its magic:

CREATE TABLE test2 ( Products NUMERIC(32, 16), Products_Product NUMERIC(32, 16), Products_Product_name VARCHAR(7) CHARACTER SET utf8, Products_Product_id NUMERIC(32, 16), Products_Product_Order NUMERIC(32, 16), Products_Product_Order_customer_id NUMERIC(32, 16), Products_Product_Order_order_date DATETIME, Products_Product_Order_id NUMERIC(32, 16) ); INSERT INTO test2 VALUES (NULL,NULL,'widgets',1234,NULL,9,'2014-01-01 00:00:00',1); INSERT INTO test2 VALUES (NULL,NULL,'widgets',1234,NULL,3,'2014-01-02 00:00:00',2); INSERT INTO test2 VALUES (NULL,NULL,'widgets',1234,NULL,6,'2014-01-03 00:00:00',3); INSERT INTO test2 VALUES (NULL,NULL,'widgets',1234,NULL,12,'2014-01-04 00:00:00',4);

As you can see, the flattened XML has been successfully converted into SQL INSERT statements, ready for importing to your SQL database.

One other thing to note: XML is case sensitive, so any script or SQL statement you write must reflect the original XML data. If it doesn’t, you’ll end up with errors.

Automate your conversions

While it’s nice to know the ins and outs of how to convert XML to SQL, it’s a lot nicer to know how it works and have it done for you by a reliable tool.

🖤 SQLizer team.

About: SQLizer offers free conversion for datasets with less than 5,000 rows, designed for personal use. If you have a CSV file with more than 5,000 rows that needs conversion, you can use the code CONVERTME10 to get a 10% discount on your Pro Monthly plan for the initial 12 months or enjoy a one-time 10% reduction on your Pro Annual plan.