如何在PL/SQL数据库中保存XML文件

Question

我需要将已经存储在FTP位置的XML文件的文本存储在数据库表的字段中，我正在使用UTL_FILE包读取文件，但我对该包的操作不是很清楚, 我刚刚开始接触 PL/SQL 的存储过程。我的现有方法是：

1. 使用UTL_FILE.FOPEN打开文件。
2. 在存储过程中创建一个INSERT语句以将其存储在数据库字段中。

为此，我正在编写一个PL / SQL存储过程，但我不知道它是否适合我所需的内容。

    DECLARE

    file BFILE;
    clob CLOB;
    FILE UTL_FILE.file_type;
    TEXT VARCHAR2(32767);
    L_CONN UTL_TCP.connection;
    CODE SPRCMNT.MNTCODE%TYPE;
    TEXT      MNT.MNT_TEXT%TYPE;
    TEXT_NAR  MNT.MNT_TEXT_NAR%TYPE;

BEGIN

    L_CONN := FTP.LOGIN('000.00.000.00', '00', '*********', '*********');
    FTP.ASCII(P_CONN => L_CONN);
    FTP.GET(P_CONN => L_CONN, p_from_file => '/xml_file/file.xml', p_to_dir => 'UPLOAD_DIR',  p_to_file => 'file.xml');
    ftp.logout(l_conn);

    VFILE := UTL_FILE.fopen('UPLOAD_DIR', 'file.xml', 'R', 4000);

    LOOP
        BEGIN
            UTL_FILE.GET_LINE(VFILE, TEXT, 32767);
            DBMS_OUTPUT.PUT_LINE(TEXT);
            INSERT INTO SPRETA (
                     ID,
                     CODE,
                     TEXT,
                     TEXT_NAR,
                     DATE)
                            VALUES('15218',
                                   '15',
                                   TEXT,
                                   TEXT_NAR,
                                   SYSDATE)
                                   RETURN TEXT_NAR INTO l_clob;

         l_bfile := BFILENAME ('UPLOAD', 'file.xml');
         dbms_lob.fileopen( l_bfile, dbms_lob.FILE_READONLY );
         dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength(l_bfile) );
         dbms_lob.fileclose( l_bfile );
         COMMIT;

         EXCEPTION 
            WHEN OTHERS THEN EXIT;
                   dbms_output.put_line('Error al cargar el archivo');  

        END;
    END LOOP;

    UTL_FILE.FCLOSE(FILE);

END;

默认情况下，CMTT_CODE 将保存值 15，TEXT 保存XML文件的名称，TEXT_NAR 保存所有内容，即表格 MNT 中的所有XML文本。

我仍然无法将所有XML文本插入到TEXT_NAR表字段中。

我试图遵循简单易懂的理念，但由于我是新手，因此真的需要一些关于最佳实践的反馈意见。

主要错误在于它没有将XML名称和XML文件包含的内容保存在其相应的字段中。

- John Doe

---

PL/SQL 运行在数据库服务器的上下文中，因此它只能访问服务器具有访问权限的文件。 - Richard

这是一个会发生一次还是多次的过程？ 您能够访问数据库服务器并将文件放在那里吗？ 为什么/为什么不？ - Richard

你的客户端操作系统是什么？你安装了bash/cygwin吗？ - TenG

你已经大幅度修改了问题 - 这对回答原始版本的人来说似乎不太公平，最好为FTP提出一个新问题。 无论如何..在您发布的代码中，您从未为 p_text 或 p_text_nar 分配值 - 您希望它们包含什么？ 另外，为什么要循环并获取 XML 中的行并将其单独存储 - 难道您不希望将整个 XML 文档存储为单个值吗？ - Alex Poole

是的，我希望将代码存储在单行中。 - John Doe

Answer 1

尽管将XML数据文件发送到数据库服务器并使用UTL_FILE进行加载和处理是最清洁的方法，但通过使用正确的工具，您可以在一定程度上弥合客户端/服务器之间的差距。下面概述的方法依赖于客户端脚本来构建一个SQL*Plus脚本，该脚本在匿名块中初始化一个CLOB变量，并在SQL中使用该变量。以下示例是在bash中进行的 - 如果您的客户端安装有bash或Cygwin（Windows），或者您的客户端是Linux/MacOS，则可能会对您有所帮助。如果没有，也许有人可以编写DOS BAT/Powershell等效脚本。其思想是构造一个脚本，将XML数据呈现为匿名PLSQL块中的变量，然后传递到存储过程中。以下是bash脚本the_script.sh的内容：

XML_FILE=${1}   # File containing XML to load

SQL_SCRIPT=the_script.sql   # script we will construct to do the work

# Start constructing teh PLSQL blcok

cat <<EOF > ${SQL_SCRIPT}

declare
   vx_xml XMLTYPE;
   vc_xml CLOB ;
begin
   vc_xml := '';   
EOF

cat ${XML_FILE} | fold -80 -s | sed "s/^/      vc_xml := vc_xml || '/" | sed "s/$/';/" >> ${SQL_SCRIPT}


cat <<EOF >> ${SQL_SCRIPT}

   vx_xml := XMLTYPE(vc_xml);
   call_the_stored_proc(vx_xml); -- A strored procedure to process the XML data as required
end;
/

EOF

cat ${SQL_SCRIPT}

cat <<EOF > sqlplus -S /nolog

conn un/pw@db

@${SQL_SCRIPT}

quit

EOF

请按以下方式执行：

bash the_script.sh the_source_data.xml

- TenG

---

我尝试使用UTL_FILE包来实现，但仍无法将数据插入表中。 - John Doe

问题现在与原来完全不同，因此我上面的答案不再适用。 - TenG

Answer 2

由于您处于 Windows 和 Oracle 环境中，这里提供了一个 PowerShell 和 SQLcl 解决方案。

假设有一个名为 some.xml 的文件。

<?xml version="1.0"?>
<root>
<listitem someattribute='thingOne'>content One</listitem>
<listitem someattribute='thingTwo'>content Two</listitem>
<listitem someattribute='thingThree'>content Three</listitem>
</root>

一个表格 MYTABLE 如下：

Name           Null?    Type               
-------------- -------- ------------------ 
COLUMNONE               VARCHAR2(25)         
COLUMNTWO               VARCHAR2(25)       

我将使用一个PowerShell脚本(xml2csv.ps1)，如下所示：

#read from file
[xml]$inputFile = Get-Content -Path some.xml

#Walk through the records
$inputFile.root.ChildNodes | 
    # re-map to column names using ScriptBlock(s)
    Select-Object @{Name=’columnOne’; Expression={$_.someattribute}},@{Name=’columnTwo’; Expression={$_.'#text'}} |
        # write out CSV file
        Export-Csv -Path some.csv -NoTypeInformation -Encoding:UTF8

将此作为powershell.exe xml2csv.ps1运行。

使用SQLcl（从https://www.oracle.com/tools/downloads/sqlcl-downloads.html下载或包含在SQL Developer中）加载数据库，并运行LOAD命令（https://www.thatjeffsmith.com/archive/2019/09/sqlcl-and-the-load-csv-command/）。

启动会话sql.exe myuser/mypassword@MYDATABASE并执行：

LOAD MYTABLE some.csv

- Richard