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

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

<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:

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 '); --root node 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)); --Node 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)); --Node 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)); --Writes the XMLDOM in the operating system file. WriteToFile (Doc, ' DIR ' | | ' \test.xml ');--note You must first create a file directory Dir--you can use the statement: Createor replace directory dir as ' D:\temp ' XMLDOM. Freedocument (DOC); End

2. Write the XML encode CLOB to the file:

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);    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, 1024x768, offset);        Utl_file.put (L_f, clobpart);        Offset: = offset + 1024x768;    End Loop;    Utl_file.fflush (L_f);    

 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;

Plsql NOTE--------XML generate