Oracle – Output in XML format and tag customization

DBMS XMLGEN is a PL/SQL package that allows a programmer to extract data in XML format from Oracle database tables.

Select DBMS_XMLGEN.GETXML('Select FIRST_NM FIRSTNAME, MDL_NM MIDDLENAME, LAST_NM LASTNAME, PHON_NR PHONENUMBER FROM SchemaName.EmployeeTable WHERE Emp_ID = 987654321') from dual;

The resultant XML data is below:

<?xml version="1.0" encoding="UTF-8"?>

<ROWSET>

   <ROW>

      <FIRSTNAME>Ho</FIRSTNAME>

      <MIDDLENAME>Chin</MIDDLENAME>

      <LASTNAME>Minh</LASTNAME>     

      <PHONENUMBER>9989798</PHONENUMBER>

   </ROW>

</ROWSET>

We notice that the ‘RowSet’ and ‘Row’ tags are default root and row elements. However we have the option to customize them.

DECLARE

   ctx DBMS_XMLGEN.ctxHandle;

   XML CLOB;

   P_Emp_Id number;

BEGIN

  P_Emp_Id:= 987654321;

   ctx := dbms_xmlgen.newcontext('SELECT FIRST_NM FirstName, MDL_NM MiddleName, LAST_NM LastName, PHON_NR PhoneNumber FROM SchemaName.EmployeeTable  WHERE Emp_ID ='||P_ Emp_Id||'');

   DBMS_XMLGEN.SETROWTAG(CTX, 'ContactPerson');

   dbms_xmlGEN.setRowSetTag(CTX, 'EmployeeApplication');

   XML := DBMS_XMLGEN.GETXML(CTX);

   DBMS_OUTPUT.PUT_LINE(SUBSTR(XML,1,1255));

END;

The output with customized names for RowSet & Row tag: 

<?xml version="1.0" encoding="UTF-8"?>

<EmployeeApplication>

   <ContactPerson>

      <FIRSTNAME>Ho</FIRSTNAME>

      <MIDDLENAME>Chin</MIDDLENAME>

      <LASTNAME>Minh</LASTNAME>      

      <PHONENUMBER>9989798</PHONENUMBER>

   </ContactPerson>

</EmployeeApplication>

We notice that the inner XML tags are caps by default. That can be customized by adding the tag names under double quotes

Select DBMS_XMLGEN.GETXML('Select FIRST_NM “FirstName”, MDL_NM “MiddleName”, LAST_NM “LastName”, PHON_NR “PhoneNumber” FROM SchemaName.EmployeeTable WHERE Emp_ID = 987654321') from dual;

The resultant XML data is below:

<?xml version="1.0" encoding="UTF-8"?>

<ROWSET>

   <ROW>

      <FirstName>Ho</FirstName >

      <MiddleName>Chin</MiddleName >

      <LastName>Minh</LastName >     

      <PhoneNumber>9989798</PhoneNumber >

   </ROW>

</ROWSET>