Generating XML using DBMS_XMLGEN

DBMS_XMLGEN Oracle package is used to convert the result of sql query to a XML format and returns it as a CLOB.

First, Create a sql as below.

stmt_   VARCHAR2(32000) ; 
ctx_    dbms_xmlgen.ctxHandle;
xml_    XmlType;
role_   VARCHAR2(20) := 'ENDUSER';

 stmt_ := 'SELECT
               t.role,
               t.description,
               t.created CREATED_DATE,
               CURSOR(SELECT grant_id
                        FROM system_grant_tab
                        WHERE role ='''|| role_||'''
                        ORDER BY nlssort(grant_id, ''nls_sort=binary'')) SYSTEM_GRANTS
    FROM ROLE_TAB t
           WHERE t.role = '''||role_||'''';

Create a new context for sql statement.
ctx_ := Dbms_Xmlgen.newContext(stmt_);

For null handling, three constants are introduced from this package as below.

DROP_NULLS CONSTANT NUMBER := 0 Example: <SYATEM_GRANTS></SYSTEM_GRANTS>

 NULL_ATTR CONSTANT NUMBER := 1  Example: xsi:nil="true".

 EMPTY_TAG CONSTANT NUMBER := 2; Example: <SYSTEM_GRANTS/>

dbms_xmlgen.setNullHandling(ctx_,2);

Using SetRowStTag Procedure you can set a name for the root element of the document. But you can pass NULL hear also.
dbms_xmlgen.setRowSetTag(ctx_, NULL);

Using SetRowTag Procedure you can set a name for the ROW element. If you donot set this, default value is set as ROW. But, if you pass NULL to hear ROW element will not be presentd.
dbms_xmlgen.setRowTag(ctx_, 'PERMISSION_SET_STRUCTURE');

Then you need to generate xml as a sys.XMLType. There are two overloads for this method. You can pass context or sqlquery.
xml_ := dbms_xmlgen.getXMLType(ctx_);

Then it is required to close context for releasing all the resources associated with it.
Dbms_Xmlgen.Closecontext(ctx_);

Now you can print the result.
Dbms_Output.put_line(xml_.getClobVal)

Then output will be as below

 <PERMISSION_SET_STRUCTURE>
  <ROLE>ENDUSER</ROLE>
  <DESCRIPTION>All required grants for End User</DESCRIPTION>
  <CREATED_DATE>13-SEP-16</CREATED_DATE>
  <SYSTEM_GRANTS>
   <SYSTEM_GRANTS_ROW>
    <GRANT_ID>ADMIN</GRANT_ID>
   </SYSTEM_GRANTS_ROW>
   <SYSTEM_GRANTS_ROW>
    <GRANT_ID>CONNECT</GRANT_ID>
   </SYSTEM_GRANTS_ROW>
   <SYSTEM_GRANTS_ROW>
    <GRANT_ID>DEBUGGER</GRANT_ID>
   </SYSTEM_GRANTS_ROW>
   <SYSTEM_GRANTS_ROW>
    <GRANT_ID>DEFINE SQL</GRANT_ID>
   </SYSTEM_GRANTS_ROW>
   <SYSTEM_GRANTS_ROW>
    <GRANT_ID>DOCUMENT ADMIN</GRANT_ID>
   </SYSTEM_GRANTS_ROW>
   <SYSTEM_GRANTS_ROW>
    <GRANT_ID>SITE ADMIN</GRANT_ID>
   </SYSTEM_GRANTS_ROW>
   <SYSTEM_GRANTS_ROW>
    <GRANT_ID>SHIP ADMIN</GRANT_ID>
   </SYSTEM_GRANTS_ROW>
  </SYSTEM_GRANTS>
 </PERMISSION_SET_STRUCTURE>












Comments

Post a Comment

Popular posts from this blog

How to add standard and custom Metadata to PDF using iTextSharp

In a previous blog, I explained How to add custom metadata using Syncfusion library. But this package gives so many difficulties when adding metadata to pdf. Such problems are,    1. When once add custom meta data to a pdf, it does not able to change or remove those custom meta data from the pdf.    2. It does not compatible with meta data search engines. It does not show custom meta data properly when those are searched through the search engines. Due to these reasons, Syncfusion is not good library for custom meta data. But, iTextSharp provides flexible framework to deal with custom meta data as well as standard meta data for PDF. Below I have provided the source code to show usage of iTextSharp library. You can download iTextShrp.dll from here                              PdfReader pdf = new PdfReader(input_path); ...
Read more

How to set and get Metadata to image using BitmapMetadata class in C#

When images are considered, those are storing their Meta data using different types of Meta containers such as EXIF, Adobe XMP, IPTC, Photoshop Image resource and Text descriptions. This every container has their specific unique rules to encode (store) and decode (extract) Meta data. As well as these each Meta container is storing specific Meta information. Exif Meta containers are mostly storing location information and GPS (Global Positioning System) records. As well as, Adobe XMP is responsible to store Dublin core Meta data which provides standard Meta tags that can be used to describe digital objects basically and IPTC is storing Meta information about application records. Meta data extractor is mainly considering these Exif, IPTC and adobe XMP Meta containers when it is extracting Meta data from images. As well as, these different Meta data containers are considered; they are structuring Meta tags in different dir...
Read more

How to generate direct line token to start a new conversation between your own client application and bot framework in Java

You can enable the communication between your bot framework and your client application using Direct Line API. When you add your client application as a site to communicate with your bot framework through azure portal, bot framework will generate secret keys. Your client application can use these generated secret keys to authenticate direct line requests issued to your bot framework. Let me explain how we can generate token to start a  conversations between your own client and bot framework. When we send a request to direct line for generating token,  if the request is successful it will send the conversation id and token that is valid to that conversation id. If client application already has a earlier used conversation Id, you can pass that id as an input to this method for generating token. If there is no conversation id, you can pass null value.  Return type of this method is a Map to retrieve generated token and conversation Id which is valid for that to...
Read more