如何将XML写入文件(PL/SQL)? [英] How to write XML to file (PL/SQL)?

问题描述

尽管有各种尝试，但我终生无法找到将其写入文件的方法.我会提前道歉，我对XML和SQL过程非常陌生.我的代码当前可以正确输出XML，但是我很难找到如何将其写入文件并使其成为过程的方法.

I cannot for the life of me find out how to write this to a file, despite various attempts. I will apologize in advance, I am very new to XML and SQL Procedures. My code is currently outputting XML correctly, I am just having trouble finding out how to write it to a file and make it a procedure.

这里是我目前拥有的非常精简的版本(关于XML和选择):

Here is a very-cut-down version (regarding the XML and select) of what I currently have:

    PROCEDURE markviewimport_interface 
    (
    p_invoice_id number(10), 
    p_filename nvarchar(30)
    )IS

    v_xmloutput CLOB;
    --v_FILENAME VARCHAR2(100) := concat(r_markviewimport.invoice_id,'-',r_markviewimport.vendor_num,'.XML');

BEGIN
SELECT  
            XMLFOREST(
                XMLCONCAT(
                        XMLELEMENT("CREATEDBY",XMLATTRIBUTES('Exported By' AS "header"), 'Magellan IT'),
                        XMLELEMENT("TIMESTAMP",XMLATTRIBUTES('Date' AS "header"), TO_CHAR(SYSDATE,'MM.DD.YYYY')),
                        XMLFOREST(
                            XMLFOREST(
                                XMLFOREST(
                                    'invoice_id' AS "DBFIELD",
                                    'Invoice ID' AS "CAPTION",
                                    r_markviewimport.invoice_id AS "VALUE"
                                ) AS "DATA_ITEM"
                            ) AS "BASICDATA"
                        )        
                    ) AS "DATA_ROOT"
                ) 
FROM             
( 
SELECT DISTINCT inv.invoice_id FROM apps.ap_invoices_all inv WHERE inv.invoice_id = 4796324

) r_markviewimport

推荐答案

1)配置(以具有CREATE ANY DIRECTORY特权的用户身份登录)

1) Config (Log as user with CREATE ANY DIRECTORY privileges)

create or replace directory YOUR_DIR_NAME as '/path/../';
grant read, write on directory YOUR_DIR_NAME to your_schema;

2)将xml转换为clob并保存.

2) Convert xml to clob and save it.

declare
  v_xml_clob   clob;
begin
  select xmlserialize(document xmlelement("a", 'lalal') as clob) into v_xml_clob from dual;
  Dbms_xslprocessor.CLOB2FILE(cl => x_xml_clob, flocation => 'YOUR_DIR_NAME', fname => 'somename.xml');
end;

这篇关于如何将XML写入文件(PL/SQL)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！