mysql存储过程解析xml

<employees>
    <employee>
        <id>1</id>
        <name>John Doe</name>
        <age>30</age>
    </employee>
    <employee>
        <id>2</id>
        <name>Jane Smith</name>
        <age>25</age>
    </عامل>
</employees>

DELIMITER //

CREATE PROCEDURE ParseXMLAndInsert()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(255);
    DECLARE emp_age INT;
    DECLARE cur CURSOR FOR SELECT id, name, age FROM OPENXML(@doc, '/employees/employee');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET @doc = '<employees>...</employees>'; -- 这里应该是实际的 XML 数据

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO emp_id, emp_name, emp_age;
        IF done THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO employees (id, name, age) VALUES (emp_id, emp_name, emp_age);
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE ParseXMLAndInsertUsingXMLExtract()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(255);
    DECLARE emp_age INT;
    DECLARE cur CURSOR FOR SELECT XML_EXTRACT(value, '/employee/id') AS id,
                                          XML_EXTRACT(value, '/employee/name') AS name,
                                          XML_EXTRACT(value, '/employee/age') AS age
                           FROM (SELECT CAST('<employees>...</employees>' AS CHAR(65535)) AS xml_data) AS t,
                                XMLTABLE('/employees/employee' PASSING xml_data RETURNING CONTENT);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO emp_id, emp_name, emp_age;
        IF done THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO employees (id, name, age) VALUES (emp_id, emp_name, emp_age);
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;