XML Generate-from Cyber

Plsql NOTE--------XML Generate

Using XML in PL/SQL, Oracle provides several components that make it easy for developers to take advantage of XML technology. These components include:
1.XML analysis Program. That is used to parse, construct, and validate XML documents.
2.XPath engine. It is a utility that uses XPath (another element of the XML standard) to describe the syntax for searching XML documents in memory. SLT processor. It supports XSLT in Oracle databases, allowing you to convert XML documents to other formats.
3.XML SQL utility. You can use SQL to generate XML documents that allow you to easily insert XML-based data in an Oracle database table.
For PL/SQL developers, the XML parser is the most important component. It enables you to parse, manipulate, and transform XML documents in an Oracle database. The XML parser consists of a set of APIs (application programming interfaces).

=============================================================================================================== ====================================================================

XML generate

Generate Test.xml with the following content

12345678910

<staff content = "name and id">    <member>      <name>Arwen</name>    <eno>123</eno>  </member>  <member>      <name>Tom</name>    <eno>456</eno>  </member>   </staff>

1. Generate the XML Plsql code:

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253

declare    doc  XMLDOM.DOMDOCUMENT;    doc_node  XMLDOM.DOMNODE;    root_node  XMLDOM.DOMNODE;    user_node XMLDOM.DOMNODE;    item_node XMLDOM.DOMNODE;    root_elmt XMLDOM.DOMELEMENT;    user_elmt XMLDOM.DOMELEMENT;    item_elmt XMLDOM.DOMELEMENT;    item_text XMLDOM.DOMTEXT;begin   doc := XMLDOM.NEWDOMDOCUMENT;   xmldom.setVersion(doc, ‘1.0‘);   xmldom.setCharset(doc, ‘UTF-8‘);   --根节点   doc_node := XMLDOM.MAKENODE(doc);   root_elmt := XMLDOM.CREATEELEMENT(doc,‘staff‘);   XMLDOM.SETATTRIBUTE(root_elmt,‘content ‘,‘name and id‘);   root_node:=XMLDOM.APPENDCHILD(doc_node, XMLDOM.MAKENODE(root_elmt));    --节点1   user_elmt := XMLDOM.CREATEELEMENT(doc,‘member‘);   user_node :=XMLDOM.APPENDCHILD(root_node, XMLDOM.MAKENODE(user_elmt));      item_elmt :=XMLDOM.CREATEELEMENT(doc,‘name‘);     item_node :=XMLDOM.APPENDCHILD(user_node, XMLDOM.MAKENODE(item_elmt));     item_text := XMLDOM.CREATETEXTNODE(doc,‘Arwen‘);     item_node:=XMLDOM.APPENDCHILD(item_node, XMLDOM.MAKENODE(item_text));         item_elmt :=XMLDOM.CREATEELEMENT(doc,‘eno‘);     item_node :=XMLDOM.APPENDCHILD(user_node, XMLDOM.MAKENODE(item_elmt));     item_text := XMLDOM.CREATETEXTNODE(doc,‘123‘);     item_node:=XMLDOM.APPENDCHILD(item_node, XMLDOM.MAKENODE(item_text));         --节点2     user_elmt := XMLDOM.CREATEELEMENT(doc,‘member‘);     user_node :=XMLDOM.APPENDCHILD(root_node, XMLDOM.MAKENODE(user_elmt));      item_elmt :=XMLDOM.CREATEELEMENT(doc,‘name‘);     item_node :=XMLDOM.APPENDCHILD(user_node, XMLDOM.MAKENODE(item_elmt));     item_text := XMLDOM.CREATETEXTNODE(doc,‘tom‘);     item_node:=XMLDOM.APPENDCHILD(item_node, XMLDOM.MAKENODE(item_text));         item_elmt :=XMLDOM.CREATEELEMENT(doc,‘eno‘);     item_node :=XMLDOM.APPENDCHILD(user_node, XMLDOM.MAKENODE(item_elmt));     item_text := XMLDOM.CREATETEXTNODE(doc,‘456‘);     item_node:=XMLDOM.APPENDCHILD(item_node, XMLDOM.MAKENODE(item_text));         --写入操作系统文件中     XMLDOM.WRITETOFILE(doc,‘DIR‘||‘\Test.xml‘);--注意必须先创建一个文件目录dir     --可以通过语句 : create or replace directory dir as ‘d:\temp‘        XMLDOM.FREEDOCUMENT(doc); end;

2. Write the XML encode CLOB to the file:

1234567891011121314151617181920212223242526

declare    l_f utl_file.file_type;    clobpart varchar2(2048);    offset integer;    l_xmltype XMLTYPE;    cloblen integer;    response_clob CLOB;    resp_domdoc DBMS_XMLDOM.DOMDOCUMENT;begin    l_f  := utl_file.fopen(‘TEST_DIR‘,l_full_file_name,‘w‘);    utl_file.put_line(l_f,‘<?xml version="1.0" encoding="utf-8" standalone="yes"?>‘||chr(13));    resp_domdoc := SoapEncPrcnScnrPckgRead.encd_root_prcn_scnr_pckg_read(p_psp_resp_doc);    l_xmltype := dbms_xmldom.getXmlType(resp_domdoc);    dbms_xmldom.freeDocument(resp_domdoc);    response_clob := l_xmltype.getClobVal;    cloblen := LENGTH(response_clob);    while offset < cloblen    loop        clobPart := dbms_lob.substr(response_clob, 1024, offset);        utl_file.put(l_f, clobPart);        offset := offset + 1024;    end loop;    utl_file.fflush(l_f);    utl_file.fclose(l_f);end;/

 3. Using xmldom and Utl_file
     to generate XML files is very convenient and can meet the general application. But XMLDOM has a drawback, is to generate all the XML file content in memory at once, It is then written to the disk file. If the XML file is too large, say there is a table with a few g, you want to save it as an XML file. This may result in insufficient memory to generate the file failure. What's the whole thing?
     The first thing you can think of is to use Utl_file to generate files. The contents of
     are all manually written in XML format and then saved as XML suffixes. This is true. But there's a problem. If some node content contains five reserved characters of XML (&,<,> "Respectively is and number, less than, greater than, single quote, double quotation mark), if we open the XML file as text is not visible in the node content of these reserved words, are converted to the corresponding &amp, &gt, &lt, &apos, & quot. Node specifies the above Arwen or 123, if the name (A&r<w>e ' n ") is saved in the XML file should be changed to (a&ampr&gtw&lte&aposn& quot). If the xmldom will be converted by default, we don't have to. If you use Utl_file, you must manually write the code to convert it.
     If there is a table staff (name VARCHAR2 (), Eno integer), there are several g of content, to be converted to the format of the beginning of the XML file.
    --generates XML code where the header and tail of the XML file are written directly to the file, the node content is generated with XMLDOM, then written to the CLOB variable, and the CLOB variable value utl_ The file is written to the XML files.

DECLARE Staffinfo utl_file.  File_type;  V_temp Clob;  Cursor C_table_info is select Name,eno from staff;  V_name VARCHAR2 (30);  V_eno integer; Doc XMLDOM.  DOMDOCUMENT; Doc_node XMLDOM.  Domnode; Root_node XMLDOM.  Domnode; User_node XMLDOM.  Domnode; Item_node XMLDOM.  Domnode; ROOT_ELMT XMLDOM.  DomElement; USER_ELMT XMLDOM.  DomElement; ITEM_ELMT XMLDOM.  DomElement; Item_text XMLDOM. Domtext;begin--xml Header staffinfo: =utl_file.fopen_nchar (' DIR ', ' test.xml ', ' W ', 32767);-- As I said earlier, you must create a directory before you can utl_file.    Put_line_nchar (staffinfo, ' <staff content = ' name and Id ' > '); Utl_file. FFLUSH (Staffinfo);--Writes directly to the disk file and does not stay in memory utl_file.        FCLOSE (Staffinfo);        Open c_table_info;            Loop fetch c_table_info into V_name,v_eno;       Exit when C_table_info%notfound; --xml node Doc: = XMLDOM.        Newdomdocument;      Xmldom.setcharset (Doc, ' UTF-8 '); Doc_node: = XMLDOM.      Makenode (DOC); USER_ELMT: = XMLDOM.           CreateElement (Doc, ' member '); usEr_node: =xmldom. AppendChild (Doc_node, XMLDOM.      Makenode (USER_ELMT)); ITEM_ELMT: =xmldom.      CreateElement (Doc, ' name '); Item_node: =xmldom. AppendChild (User_node, XMLDOM.      Makenode (ITEM_ELMT)); Item_text: = XMLDOM.      createTextNode (Doc,v_name); Item_node:=xmldom. AppendChild (Item_node, XMLDOM.      Makenode (Item_text)); ITEM_ELMT: =xmldom.      CreateElement (Doc, ' Eno '); Item_node: =xmldom. AppendChild (User_node, XMLDOM.      Makenode (ITEM_ELMT)); Item_text: = XMLDOM.      createTextNode (Doc, ' Eno '); Item_node:=xmldom. AppendChild (Item_node, XMLDOM.      Makenode (Item_text));        V_temp: = "; --Writes to the temporary variable v_temp in XMLDOM.      Writetoclob (doc,v_temp); Staffinfo: =utl_file.fopen_nchar (' DIR ', ' test.xml ', ' a ', 32767);--in a mode the content is added after the file, and W overwrites the previous content Utl_file.      Put_line_nchar (Staffinfo, v_temp); Utl_file.      FFLUSH (Staffinfo); Utl_file.      FCLOSE (Staffinfo); XMLDOM.    Freedocument (DOC);    End Loop;    Close C_table_info; --xml tail Staffinfo: =utl_file.fopen_nchar (' DIR ', ' test.xml ',' A ', 32767); Utl_file.    Put_line_nchar (Staffinfo, ' </staff> '); Utl_file.    FFLUSH (Staffinfo); Utl_file. FCLOSE (staffinfo); end;

XML Generate-from Cyber