Translation

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

Using XML: Index

Part One: Introduction to XML

Part Two: From XML to rows and columns

Part Three: From rows and columns to XML






Using XML 17: DBMS_XMLGEN

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.

Reference

[1] https://docs.oracle.com/cloud/latest/db121/ARPLS/d_xmlgen.htm#ARPLS374
[2] Summary of DBMS_XMLGEN Subprograms
[3] GETXMLTYPE Functions

Part16 Index

Using XML 16: XMLColAttVal

From [1]: Oracle SQL function XMLColAttVal generates a forest of XML column elements containing the values of the arguments passed in.



select XMLElement("User",
          XMLAttributes(USER_ID as "User ID"),
          XMLColAttVal(USERNAME, ORACLE_MAINTAINED as "Is_Ora_Owned", CREATED)
       ) user_details
from all_users fetch first 3 rows only

Click to enlarge

References



[1] https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-F643284F-A6AF-47A9-AF2C-58EF7E88C87F

Part15 Index Part17