Using XML: Index

Part One: Introduction to XML

• Introduction, Part 01: What is this?
• Introduction, Part 02: Anatomy of a document
• Introduction, Part 03: Path

Part Two: From XML to rows and columns

• Insert, Part 01: Passing values
• Insert, Part 02: Insert via SQL*Plus
• Select, Part 01: EXTRACT and EXTRACTVALUE
• Select Part 02: XMLQuery
• Select, Part 03a: XMLTable
• Select, Part 03b: XMLTable (continued)
• Recap

Part Three: From rows and columns to XML

• XMLElement
• XMLAttributes
• XMLForest
• XMLConcat
• XMLAgg
• XMLColAttVal
• DBMS_XMLGEN.GETXMLTYPE

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