MySQL解析XML遇阻求助：AIX环境无xmlstarlet且XML不被完全支持

Got it, let's work through this problem since you're stuck getting XML data into MySQL on AIX 7.1—no xmlstarlet available, and MySQL's native XML support isn't cutting it for your four different XML file types. Here are practical, environment-friendly alternatives to try:

1. Use Python/Perl to Parse XML and Generate Importable Formats #

Python (or Perl) is a solid choice here because it’s relatively easy to install on AIX (via the IBM AIX Toolbox) and has robust XML parsing libraries. You can write a simple script to extract data from your XML files and output either a CSV/TSV file (for LOAD DATA INFILE) or direct INSERT statements for MySQL.

For example, here’s a Python snippet tailored to the MovementReport structure you shared:

import xml.etree.ElementTree as ET
import csv

# Parse the XML file
tree = ET.parse("movement_report.xml")
root = tree.getroot()

# Set up output CSV
with open("movement_data.csv", "w", newline="") as csv_out:
    # Define columns based on your target MySQL table
    fieldnames = ["division", "confirmation"]
    writer = csv.DictWriter(csv_out, fieldnames=fieldnames)
    
    writer.writeheader()
    # Extract data from the Sender node
    sender_node = root.find(".//Sender")
    if sender_node is not None:
        division = sender_node.find("Division").text if sender_node.find("Division") else ""
        confirmation = sender_node.find("Confirmation").text if sender_node.find("Confirmation") else ""
        writer.writerow({"division": division, "confirmation": confirmation})

You can adapt this script for your other three XML types by adjusting the XPath queries and output fields. Once you have the CSV, import it into MySQL with:

LOAD DATA INFILE '/path/to/movement_data.csv'
INTO TABLE your_target_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

2. Leverage MySQL's XML Functions with a Temporary Table #

If you don’t want to use external scripts, you can load the raw XML into a temporary MySQL table and use built-in functions like ExtractValue to pull out the data you need. This works even if MySQL’s full XML support doesn’t cover your exact structure.

Example workflow:

-- Create a temporary table to store raw XML content
CREATE TEMPORARY TABLE temp_xml_store (xml_content TEXT);

-- Load the XML file into the temp table (ensure MySQL has read access to the file on AIX)
LOAD DATA INFILE '/path/to/your/xml/file.xml' INTO TABLE temp_xml_store;

-- Extract and insert data into your target table
INSERT INTO your_target_table (division, confirmation)
SELECT
    ExtractValue(xml_content, '//Sender/Division/text()') AS division,
    ExtractValue(xml_content, '//Sender/Confirmation/text()') AS confirmation
FROM temp_xml_store;

For more complex XML with repeating nodes, you can combine this with recursive CTEs (if your MySQL version supports them) to iterate through multiple entries.

3. Shell Scripting with awk/sed (For Simple, Predictable XML) #

If your XML files have a very consistent, flat structure (no deep nesting), you can use awk or sed to scrape the required fields and generate a CSV. This is a quick-and-dirty option for simpler cases.

Here’s an awk script example for your sample XML:

BEGIN {
    FS = "<|>"  # Split on XML tags
    print "division,confirmation"  # CSV header
}
/Division/ { if ($3 != "") division = $3 }
/Confirmation/ { if ($3 != "") { confirmation = $3; print division "," confirmation } }

Run it like this:

awk -f parse_xml.awk movement_report.xml > output.csv

Note: This method breaks easily if your XML has variable nesting or unexpected whitespace, so it’s only ideal for your simplest XML types.

内容的提问来源于stack exchange，提问作者emare