XML Data Importer to MySQL Database - n8n Workflow

Data Engineers needing to ingest legacy XML feeds.
Developers looking for robust n8n templates for data transformation.
Users seeking to implement file processing logic within an n8n workflow.
Anyone who needs to utilize the MySQL n8n node for bulk insertion operations.

This powerful n8n workflow solves the common challenge of integrating structured data stored in the XML format with relational databases like MySQL. Instead of writing complex parsing scripts, this n8n template provides a streamlined process. It uses specialized n8n node configurations to read the binary file, perform necessary parsing (XML to JSON), and then automatically split the data structure for efficient batch processing and insertion into a MySQL database. This is a crucial data pipeline automation demonstrating the flexibility of the n8n automation platform, ensuring data integrity and rapid deployment using minimal custom code within a dedicated n8n node environment.

1. The process starts with the manual n8n trigger, "When clicking 'Execute Workflow'", allowing for on-demand execution of the n8n workflow.


2. The "Read Binary Files" n8n node fetches the specified XML file (/home/node/.n8n/intermediate.xml) from the local file system, returning it as a binary buffer.


3. A "Code" n8n node extracts the binary buffer and converts the raw XML content into a standard UTF-8 string. This is a critical custom code step necessary to prepare the data for the next structural conversion n8n node.


4. The dedicated "XML to JSON" n8n node takes the string input and performs the necessary conversion, transforming the hierarchical XML data structure into a fully processable JSON object.


5. The "Item Lists" n8n node targets the product list within the JSON payload (Products.Product) and splits the array, ensuring each individual product record becomes a separate item in the n8n workflow. This prepares the data for efficient row-by-row database insertion.


6. Finally, the "Add new records" MySQL n8n node connects to the database, maps the processed JSON fields (such as Code, Name, Price) to the respective SQL table columns, and performs the batch insert operation, successfully completing the full data transformation n8n workflow.

1. Import the n8n workflow: Copy the provided n8n workflow JSON and import it directly into your n8n instance using the 'New' -> 'Import from JSON' function.


2. MySQL Credentials: The MySQL n8n node ("Add new records") requires database connection details. Set up a MySQL credential entry (matching the ID/name used, 'db4free MySQL' in this n8n template) with valid access information (host, port, user, and password).


3. File Configuration: Update the file path in the "Read Binary Files" n8n node to accurately point to the location of your input XML file on the host machine or in the n8n container file system.


4. Table Setup (Optional): If the target table (new_table) does not exist, temporarily enable and execute the disabled "Create new table" n8n node to prepare the database schema. Ensure this is run before the main n8n workflow execution.


5. Execution: Once credentials and file paths are set, execute the manual n8n trigger to start the data ingestion process.

When clicking "Execute Workflow" (n8n trigger):
Function: Initiates the entire data pipeline n8n workflow manually.
Key Configuration: Standard manual execution trigger.
Read Binary Files (n8n node):
Function: Reads the XML file containing the structured product data from the local file system.
Key Configuration: fileSelector: /home/node/.n8n/intermediate.xml.
Extract binary data (Code n8n node):
Function: Converts the binary file content into a usable UTF-8 string, which is necessary for the subsequent parsing n8n node.
Key Configuration: Uses custom JavaScript to read the binary buffer and convert it using toString('utf8').
XML to JSON (n8n node):
Function: Transforms the raw XML string input into a standard JSON object, making the data accessible for field extraction.
Key Configuration: Standard XML parsing parameters are utilized.
Item Lists (n8n node):
Function: Splits the array of products found under the path Products.Product into individual data items, preparing them for batch insertion.
Key Configuration: fieldToSplitOut: Products.Product.
Add new records (MySQL n8n node):
Function: Inserts the parsed product details from each item into the target MySQL newtable.
Key Configuration: table: newtable; Defines column mappings for fields like productCode (mapped from ={{ $json.Code }}) and MSRP (mapped from ={{ $json.Price }}).