The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

mercoledì, ottobre 11, 2017


I started this series on XML because of a query wrote by Connor McDonald. Please read my previous post.

Now there is just a last post where I want to talk about DBMS_XMLGEN.GETXMLTYPE.

First of all the definition of DBMS_XMLGEN package[1].

From [1]: The DBMS_XMLGEN package converts the results of a SQL query to a canonical XML format. The package takes an arbitrary SQL query as input, converts it to XML format, and returns the result as a CLOB. This package is similar to the DBMS_XMLQUERY package, except that it is written in C and compiled into the kernel. This package can only be run on the database.

You can find the list of procedure and functions of DBMS_XMLGEN package in [2]

The GETCMLTYPE function definition is taken from [3]: Gets the XML document and returns it as XMLType

As you can see, the first argument is a sqlQuery. So, in order to try a test, I just run a simple query.

First I a run the main statement and check the result

At this point, I run a really simple statement with DBMS_XMLGEN.GETXMLTYPE using the previous select.

So, GETXMLTYPE transforms the resultset of a select in an XML. The root node is "ROWSET" while the children are "ROW". Inside this children, there are other children: one of each column specified in the inner select.

Now I run the part of XML from my previous post (I just modify owner a table name because I run the statement on a different database):

At this point, you have all the instruments to correctly understand the Connor McDonald's select.


[2] Summary of DBMS_XMLGEN Subprograms
[3] GETXMLTYPE Functions

Part16 Index

Nessun commento: