How to build XML Document from Oracle Query.

By: Arif Khan

Hi everybody. In this short blog I am going to tell you about generating XML document from Oracle SQL Query, the Query is very simple and easy to understand, one who want to generate XML document must understand the XML structure.

XML Document Structure

XML document has both logical and physical structure. Physically, it consists of storage units called entities, each refer to other entity, just like include works in the C. An XML document consists of declarations, elements, comments, character references, and processing instructions, known as the markup.

An XML document consists of three parts, in the order given below:

1. An XML declaration (which is optional, but recommended in most cases)
2. A document type declaration that refers to a Document Type Definition (DTD)  (optional, but required for validation)
3. A body (which is required)

Collectively, the XML declaration and the document type declaration are called the XML prolog.

XML Declaration

The XML declaration identifies that this is an XML document, actually it is a piece of markup (which may span multiple lines of a file). The declaration may also contains the external DTD reference for validation. Today I am not focusing on DTD, we will discuss on it some other time, for now, just think of a DTD as a set of rules that contains document structure.

XML document have hierarchical structure and interpreted as tree structure, it is known as XML tree. It must contain root element which span all of document lines, just like a parent. Every element of the document can contain child or sub elements and text attributes.

Let us write a simple basic SQL query to generate XML in Oracle.

select xmlroot(xmlElement("example_xml",
(select xmlAgg(xmlElement("MmsSpc",
                         xmlElement("Sender", 'ABC Company'),
                         xmlElement("SenderID",
                                    '03221'),
                         xmlElement("Rcvr", 'XYZ Company'),
                         xmlElement("MsgType", 'Trans Message'),
                         xmlElement("Refid", '01'),
                         xmlElement("Stmtprd", '01-Jun-2021 To 30-Jun-2021'),
                         xmlElement("Timestamp", 
                                    to_char(sysdate, 'YYYY-MM-DD') || 'T' ||
                                    to_char(sysdate, 'HH24:MM:SS'))))
  from dual)),version '1.0" encoding="UTF-8')
  from dual;

Query Explanation

With the span of Select <———-à from dual (name dual may be change with the name of your master table), I opened a XML root element and name it as “example_xml”, after that, second level child (xmlElement) is created with the help of column level subquery within root span, named it as “MmsSpc” as you already aware that within every XML element we can define its child element, same as this I created seven childs under “MmsSpc” by using xmlElement function this function takes two parameter 1^st is element name and 2^nd is element data it can be hardcoded just like my example or it can be the column of a table.

Notice that I enclosed the xmlElement “MmsSpc” by xmlAgg. XMLAGG is aggregate function, it takes a list of XML elements from one column and returns an aggregated XML document in a single cell. In the above example Query, the XMLAGG function aggregates 7 child elements of “MmsSpc” element into a single cell XML data.

In the last you can see the declaration about XML version and encoding.

Below is the output of above ORACLE Query.

Now I think that you may be able to create your own XML document from SQL Query. Practice it and make some from your data.

Hearing from you, will be my pleasure, If you have any comments or questions, please post below.