如何在H2数据库中导入XML文件？解决XML类型不支持、SQL命令失效等问题

我之前也碰到过类似的困扰，H2确实没有原生的XML数据类型支持，而且很多SQL Server里的XML操作语法在H2里也不生效，但咱们可以通过几个变通方案来完成XML到H2的导入，下面是我亲测有效的方法：

方法1：用H2的TEXT类型存储XML+XPath函数解析 #

这个方法不用额外工具，纯靠H2自身的功能就能搞定，步骤如下：

第一步：创建存储XML的临时表

先建一个临时表来存放整个XML内容，用TEXT类型替代不支持的XML类型：

CREATE TABLE XML_STORE (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    RAW_XML TEXT
);

第二步：导入XML文件到临时表

用H2的READ_TEXT函数读取本地XML文件内容并插入到临时表：

INSERT INTO XML_STORE (RAW_XML)
SELECT READ_TEXT('/绝对路径/your_customers.xml', 'UTF-8');

注意：如果是嵌入式H2，路径要确保数据库进程能访问到，也可以用相对路径（比如相对于H2的工作目录）

第三步：解析XML并插入到业务表

接下来用H2的XPath系列函数（XPATH_STRING、XPATH_NODESET、UNNEST）把XML数据拆分到对应的业务表中。

首先创建业务表：

-- 客户表
CREATE TABLE CUSTOMERS (
    CUSTOMER_ID VARCHAR(10) PRIMARY KEY,
    CUSTOMER_NAME VARCHAR(100) NOT NULL,
    ADDRESS VARCHAR(200)
);

-- 订单表
CREATE TABLE ORDERS (
    ORDER_ID VARCHAR(10) PRIMARY KEY,
    CUSTOMER_ID VARCHAR(10) REFERENCES CUSTOMERS(CUSTOMER_ID),
    ORDER_DATE TIMESTAMP
);

-- 订单明细表
CREATE TABLE ORDER_DETAILS (
    ORDER_ID VARCHAR(10) REFERENCES ORDERS(ORDER_ID),
    PRODUCT_ID INT,
    QUANTITY INT,
    PRIMARY KEY (ORDER_ID, PRODUCT_ID)
);

然后插入客户数据：

WITH CUSTOMER_NODES AS (
    SELECT XPATH_NODESET(RAW_XML, '/ROOT/Customers/Customer') AS C_NODE
    FROM XML_STORE
)
INSERT INTO CUSTOMERS (CUSTOMER_ID, CUSTOMER_NAME, ADDRESS)
SELECT
    XPATH_STRING(C_NODE, '@CustomerID'),
    XPATH_STRING(C_NODE, '@CustomerName'),
    TRIM(XPATH_STRING(C_NODE, 'Address'))
FROM CUSTOMER_NODES;

插入订单数据：

WITH CUSTOMER_NODES AS (
    SELECT XPATH_NODESET(RAW_XML, '/ROOT/Customers/Customer') AS C_NODE
    FROM XML_STORE
),
ORDER_NODES AS (
    SELECT
        XPATH_STRING(C_NODE, '@CustomerID') AS CUSTOMER_ID,
        UNNEST(XPATH_NODESET(C_NODE, 'Orders/Order')) AS O_NODE
    FROM CUSTOMER_NODES
)
INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_DATE)
SELECT
    XPATH_STRING(O_NODE, '@OrderID'),
    CUSTOMER_ID,
    PARSEDATETIME(XPATH_STRING(O_NODE, '@OrderDate'), 'yyyy-MM-dd''T''HH:mm:ss')
FROM ORDER_NODES;

插入订单明细数据：

WITH CUSTOMER_NODES AS (
    SELECT XPATH_NODESET(RAW_XML, '/ROOT/Customers/Customer') AS C_NODE
    FROM XML_STORE
),
ORDER_NODES AS (
    SELECT
        XPATH_STRING(C_NODE, '@CustomerID') AS CUSTOMER_ID,
        XPATH_STRING(O_NODE, '@OrderID') AS ORDER_ID,
        UNNEST(XPATH_NODESET(O_NODE, 'OrderDetail')) AS D_NODE
    FROM CUSTOMER_NODES,
         UNNEST(XPATH_NODESET(C_NODE, 'Orders/Order')) AS O_NODE
)
INSERT INTO ORDER_DETAILS (ORDER_ID, PRODUCT_ID, QUANTITY)
SELECT
    ORDER_ID,
    XPATH_STRING(D_NODE, '@ProductID')::INT,
    XPATH_STRING(D_NODE, '@Quantity')::INT
FROM ORDER_NODES;

方法2：先转CSV再导入H2 #

如果觉得XPath写起来麻烦，可以先把XML转换成CSV格式，再用H2的CSVREAD函数批量导入，这也是个很直观的方案：

比如用Python写个小脚本转XML到CSV（你也可以用其他工具比如XSLT）：

import xml.etree.ElementTree as ET
import csv

# 解析XML
tree = ET.parse('customers.xml')
root = tree.getroot()

# 生成客户CSV
with open('customers.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(['CUSTOMER_ID', 'CUSTOMER_NAME', 'ADDRESS'])
    for customer in root.findall('./Customers/Customer'):
        writer.writerow([
            customer.get('CustomerID'),
            customer.get('CustomerName'),
            customer.find('Address').text.strip()
        ])

# 生成订单CSV
with open('orders.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(['ORDER_ID', 'CUSTOMER_ID', 'ORDER_DATE'])
    for customer in root.findall('./Customers/Customer'):
        c_id = customer.get('CustomerID')
        for order in customer.findall('./Orders/Order'):
            writer.writerow([
                order.get('OrderID'),
                c_id,
                order.get('OrderDate')
            ])

# 生成订单明细CSV
with open('order_details.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(['ORDER_ID', 'PRODUCT_ID', 'QUANTITY'])
    for customer in root.findall('./Customers/Customer'):
        for order in customer.findall('./Orders/Order'):
            o_id = order.get('OrderID')
            for detail in order.findall('./OrderDetail'):
                writer.writerow([
                    o_id,
                    detail.get('ProductID'),
                    detail.get('Quantity')
                ])

然后在H2里导入CSV：

-- 导入客户表
INSERT INTO CUSTOMERS
SELECT * FROM CSVREAD('customers.csv');

-- 导入订单表
INSERT INTO ORDERS
SELECT * FROM CSVREAD('orders.csv');

-- 导入订单明细表
INSERT INTO ORDER_DETAILS
SELECT * FROM CSVREAD('order_details.csv');

方法3：用Java程序解析XML并批量插入 #

如果你的项目是Java技术栈，用代码解析XML后通过JDBC批量插入会更灵活，尤其是复杂XML结构：

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;

public class XmlToH2Importer {
    private static final String H2_URL = "jdbc:h2:~/your_database_name";
    private static final String H2_USER = "sa";
    private static final String H2_PWD = "";
    private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(H2_URL, H2_USER, H2_PWD)) {
            // 解析XML文件
            DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = factory.newDocumentBuilder();
            Document doc = builder.parse("customers.xml");
            doc.getDocumentElement().normalize();

            // 批量插入客户
            insertCustomers(conn, doc);
            // 批量插入订单和明细
            insertOrdersAndDetails(conn, doc);

            System.out.println("导入完成！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void insertCustomers(Connection conn, Document doc) throws Exception {
        String sql = "INSERT INTO CUSTOMERS (CUSTOMER_ID, CUSTOMER_NAME, ADDRESS) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            NodeList customerNodes = doc.getElementsByTagName("Customer");
            for (int i = 0; i < customerNodes.getLength(); i++) {
                Element customer = (Element) customerNodes.item(i);
                pstmt.setString(1, customer.getAttribute("CustomerID"));
                pstmt.setString(2, customer.getAttribute("CustomerName"));
                pstmt.setString(3, customer.getElementsByTagName("Address").item(0).getTextContent().trim());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        }
    }

    private static void insertOrdersAndDetails(Connection conn, Document doc) throws Exception {
        String orderSql = "INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_DATE) VALUES (?, ?, ?)";
        String detailSql = "INSERT INTO ORDER_DETAILS (ORDER_ID, PRODUCT_ID, QUANTITY) VALUES (?, ?, ?)";

        try (PreparedStatement orderStmt = conn.prepareStatement(orderSql);
             PreparedStatement detailStmt = conn.prepareStatement(detailSql)) {

            NodeList customerNodes = doc.getElementsByTagName("Customer");
            for (int i = 0; i < customerNodes.getLength(); i++) {
                Element customer = (Element) customerNodes.item(i);
                String customerId = customer.getAttribute("CustomerID");

                NodeList orderNodes = customer.getElementsByTagName("Order");
                for (int j = 0; j < orderNodes.getLength(); j++) {
                    Element order = (Element) orderNodes.item(j);
                    String orderId = order.getAttribute("OrderID");
                    Timestamp orderDate = new Timestamp(DATE_FORMAT.parse(order.getAttribute("OrderDate")).getTime());

                    // 插入订单
                    orderStmt.setString(1, orderId);
                    orderStmt.setString(2, customerId);
                    orderStmt.setTimestamp(3, orderDate);
                    orderStmt.addBatch();

                    // 插入订单明细
                    NodeList detailNodes = order.getElementsByTagName("OrderDetail");
                    for (int k = 0; k < detailNodes.getLength(); k++) {
                        Element detail = (Element) detailNodes.item(k);
                        detailStmt.setString(1, orderId);
                        detailStmt.setInt(2, Integer.parseInt(detail.getAttribute("ProductID")));
                        detailStmt.setInt(3, Integer.parseInt(detail.getAttribute("Quantity")));
                        detailStmt.addBatch();
                    }
                }
            }
            orderStmt.executeBatch();
            detailStmt.executeBatch();
        }
    }
}

注意事项 #

• 确保H2版本支持所用的XPath函数（H2 1.4.200及以上版本都支持这些函数）
• 如果是内存模式的H2，导入后记得执行BACKUP TO命令持久化数据，否则重启后会丢失
• 文件路径问题：如果H2是服务器模式运行，要确保XML/CSV文件在服务器可访问的路径下，或者用网络路径

内容的提问来源于stack exchange，提问作者cloud.f