如何将XML文件中的图书数据插入Oracle数据库的Books表？

首先假设你的Books表结构如下（若不存在需先创建）：

CREATE TABLE Books (
    category VARCHAR2(50),
    title VARCHAR2(100),
    author VARCHAR2(100),
    year NUMBER(4),
    price NUMBER(10,2)
);

方法一：直接使用SQL/XML的XMLTABLE函数插入 #

如果XML内容可直接嵌入SQL语句，用XMLTABLE解析XML并批量插入是最简便的方式：

INSERT INTO Books (category, title, author, year, price)
SELECT
    book.category,
    book.title,
    book.author,
    book.year,
    book.price
FROM
    XMLTABLE('/bookstore/book'
        PASSING XMLTYPE('
<bookstore> 
    <book category="cooking"><title lang="en">Everyday Italian</title> <author>Giada De Laurentiis</author> <year>2005</year><price>30.00</price></book>
    <book category="children"><title lang="en">Harry Potter</title><author>J K. Rowling</author><year>2005</year><price>29.99</price></book>
</bookstore>')
        COLUMNS
            category VARCHAR2(50) PATH '@category', -- 读取book元素的category属性
            title VARCHAR2(100) PATH 'title',       -- 读取title子元素的文本
            author VARCHAR2(100) PATH 'author',     -- 读取author子元素的文本
            year NUMBER(4) PATH 'year',             -- 读取year子元素的文本并转为数字
            price NUMBER(10,2) PATH 'price'         -- 读取price子元素的文本并转为小数
    ) book;
COMMIT;

关键说明 #

• XMLTYPE将字符串转换为Oracle可识别的XML类型
• XMLTABLE的/bookstore/book路径指定要遍历的XML节点
• PATH参数对应XML中数据的位置：@xxx表示元素的属性，直接写节点名表示该节点的文本内容

方法二：从本地XML文件读取并插入 #

如果XML数据存储在本地文件中，可通过PL/SQL结合UTL_FILE工具读取文件后解析插入：

步骤1：创建目录对象（需DBA权限） #

首先创建指向XML文件所在目录的数据库对象，并授予读取权限：

CREATE DIRECTORY XML_DIR AS '/your/local/xml/file/path';
GRANT READ ON DIRECTORY XML_DIR TO YOUR_DATABASE_USER;

步骤2：编写PL/SQL块读取并插入 #

DECLARE
    v_xml XMLTYPE;
    v_file UTL_FILE.FILE_TYPE;
    v_clob CLOB;
    v_buffer VARCHAR2(32767);
BEGIN
    -- 读取XML文件内容到CLOB
    v_file := UTL_FILE.FOPEN('XML_DIR', 'books.xml', 'R');
    LOOP
        BEGIN
            UTL_FILE.GET_LINE(v_file, v_buffer);
            v_clob := v_clob || v_buffer || CHR(10);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXIT; -- 文件读取完毕
        END;
    END LOOP;
    UTL_FILE.FCLOSE(v_file);
    
    -- 将CLOB转为XMLTYPE并执行插入
    v_xml := XMLTYPE(v_clob);
    
    INSERT INTO Books (category, title, author, year, price)
    SELECT
        book.category,
        book.title,
        book.author,
        book.year,
        book.price
    FROM
        XMLTABLE('/bookstore/book'
            PASSING v_xml
            COLUMNS
                category VARCHAR2(50) PATH '@category',
                title VARCHAR2(100) PATH 'title',
                author VARCHAR2(100) PATH 'author',
                year NUMBER(4) PATH 'year',
                price NUMBER(10,2) PATH 'price'
        ) book;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- 异常处理：确保文件关闭
        IF UTL_FILE.IS_OPEN(v_file) THEN
            UTL_FILE.FCLOSE(v_file);
        END IF;
        RAISE; -- 抛出原异常便于排查
END;
/

注意事项 #

• 确保Oracle服务器可访问你指定的目录路径（若为远程服务器，路径是服务器端的路径）
• 如果XML文件过大，可调整v_buffer的大小或使用分段读取的方式优化

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