Using XML 15: XMLAgg

From [1]: You use SQL/XML standard function XMLAgg to construct a forest of XML elements from a collection of XML elements — it is an aggregate function.

Numeric literals are not interpreted as column positions. For example, ORDER BY 1 does not mean order by the first column. Instead, numeric literals are interpreted as any other literals.

I want just try this select

select USERNAME, XMLElement("Schemas",
   XMLAgg(XMLElement("User", username),
      XMLElement("When", to_char(CREATED, 'dd/mm/yyyy hh24:mi:ss')),
      XMLElement("Id", USER_ID),
      XMLElement("OracleOwn ", ORACLE_MAINTAINED)
      )
   ) xml_schemas
from all_users fetch first 3 rows only

Ok. It's failed. This is because I have to choose only one argument

select USERNAME, XMLElement("Schemas",
   XMLAgg(XMLElement("User", username)
      )
   ) xml_schemas
from all_users fetch first 3 rows only

So I have modify the the statement

select XMLElement("Schemas",
   XMLAgg(XMLElement("User", username)
      )
   ) xml_schemas
from all_users fetch first 3 rows only

Click to enlarge

Update

2017/Oct/11 - Modified the statement because I wrote it badly

Reference

[1] https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-971232BA-B52E-44CB-86DA-E182AF7E2ACB

Part14 Index Part16

Using XML 14: XMLConcat

From [1]: You use SQL/XML standard function XMLConcat to construct an XML fragment by concatenating multiple XMLType instances.

Following an example:

select USERNAME, XMLConcat(
   XMLElement("User", username),
   XMLElement("When", to_char(CREATED, 'dd/mm/yyyy hh24:mi:ss')),

   XMLElement("Id", USER_ID),
   XMLElement("OracleOwn ", ORACLE_MAINTAINED)

) xml_concat

from all_users fetch first 3 rows only

Click to enlarge

Pay attention to the "blank" in the "OracleOnw" string: it is mantained.

References

[1] https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-3806853D-2AAA-4D2D-AD98-3E0941F7BE17

Part13 Index Part15

Using XML 13: XMLForest

You use SQL/XML standard function XMLForest to construct a forest of XML elements.

From [1]: Each of the value expressions (value_expr in Figure 8-3) is converted to XML format, and, optionally, identifier "alias" is used as the attribute identifier. For an object type or collection, the AS clause is required. For other types, the AS clause is optional

Click to enlarge

Well, I need to rename the element:

select username,
    XMLForest(username, to_char(CREATED, 'dd/mm/yyyy hh24:mi:ss') when, 
    USER_ID, ORACLE_MAINTENED) XML_forest
from all_users fetch first 3 rows only

Click to enlarge

I also can indent the functions. In following example I use XMLElement and XMLForest

Click to enlarge

Just let me reformat the output changing the column size

Click to enlarge

and I can indent the functions

Click to enlarge

References

[1] https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-D9666485-0383-4AF6-B352-B9A90AE1DCFB

Part12 Index Part14

Using XML 12: XMLAttributes

From [1]: SQL/XML standard function XMLAttributes can be used together with XMLElement, to specify attributes for the generated elements. The first argument to function XMLElement defines an identifier that names the root XML element to be created. The root-element identifier argument can be defined using a literal identifier. If an attribute value expression evaluates to NULL, then no corresponding attribute is created. The data type of an attribute value expression cannot be an object type or a collection.

This is the syntax:

I run following statement

select username, XMLElement("Schema",
   XMLAttributes(username as "User",
   to_char(CREATED, 'dd/mm/yyyy hh24:mi:ss') as "When"
   USER_ID as "Id"
   ORACLE_MANTAINED as "OracleOwn")
   ) xml_user_details
from all_users fetch first 3 rows only

As you can see, the XML in yellow rectangle, is not complete. I have to format the column, changing the value of long

References

[1] https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-868E591C-19A1-4E4A-BC2A-013181071B77

Part11 Index Part13

Using XML 11: XMLElement

From [1]. The SQL/XML standard function XMLElement constructs XML elements from relational data. It takes as arguments an element name, an optional collection of attributes for the element, and zero or more additional arguments that make up the element content. It returns an XMLType instance.

The first argument to function XMLElement defines an identifier that names the root XML element to be created. The root-element identifier argument can be defined and, if it is defined, the identifier must not be NULL or else an error is raised.

This is the syntax:

I want to do a very simple test. I use the ALL_USERS view, in order to the run the first select.

Let's reformat the output

Ok. How you can see, the XMLElement, transform the column in a XML. Let's see more on the ALL_USERS view:

I add a second column in my select (green and red).

Because of DATE data type, I change to output using the TO_CHAR function

Really simple. But I can put an XMLElement (gray and green) inside another one (red)

So I obtain a more complex XML.

[1] https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-868E591C-19A1-4E4A-BC2A-013181071B77

Part10 Index Part12

Using XML 10: Comparing functions

In this post, I want just compare the EXTRACT and EXTRACTVALUE functions with the new XMLQuery and XMLTable functions, respectively.

What I want to show is summarized in this table

Old Syntax	New Syntax
EXTRACT	XMLQuery
EXTRACTVALUE	XMLTabel

But wait a moment. Really, EXTRACTVALUE extracted the value while in the previous example the XMLTable was the same as XMLQuery, they is EXTRACT function.

The example for XMLTable operator not used all parameters. I not used the "COLUMN" option.

It's the time.

SQL> SELECT rownum, t.*, tx.* from mytab t, 

  2> XMLTable('/alertlog/msg[@pid="65370"]' 

  3> PASSING t.XML_COLUMN COLUMNS message path 'msg', text path 'txt') tx

Click to enlarge

Now you can say that XMLTable is like the EXTRACTVALUE function. So the table I wrote above is more correctly like this one:

Old Syntax	New Syntax
EXTRACT(<xmltype>, <row-pattern>)	XMLQuery(<row-pattern>          PASSING <col-name>          RETURNING CONTENT)
EXTRACTVALUE(<col-name>, <row-pattern>)	XMLTabel(<row-pattern>          PASSING <expr>          COLUMNS <column> PATH <string>)

At this point I'm (almost) ready to modify a statement wrote by Connor McDonald. Yes. My goal when I decided to write these posts was to understand a particular statement that McDonald wrote in him video [1]

The statement I used is a little different: I just modified some aliases and the way to quote the string (in green the modifications)

with xml 
  as (
     select
       table_name
     , dbms_xmlgen.getxmltype(
          q'[select 
             table_name
           , partition_name
           , high_value
     from 
       dba_tab_partitions
     where 
       table_name=']'||table_name||q'[']') as xml_output
    from dba_tables t
    where table_name in ('COMPOSITE_RNG_LIST')
    and   owner='FRTD_CORP'
    )
select
   extractValue(rws.object_value, '/ROW/TABLE_NAME') table_name,
   extractValue(rws.object_value, '/ROW/PARTITION_NAME') partition_name,
   extractValue(rws.object_value, '/ROW/HIGH_VALUE') high_value
   from xml x,  
   table(xmlsequence(extract(x.xml_output, 'ROWSET/ROW'))) rws
order by 1,2,3;

Click to enlarge

What I don't discussed yet is the DBMS_XMLGEN.GETXMLTYPE. I'm going to investigate this function in the 3th part ("From rows/columns To XML" [3]) of this collection on XML.

For now, from [2], we know that "the PL/SQL package DBMS_XMLGEN creates XML documents from SQL query results". It should be enough in order to understand the meaning of the above statement.

Before to go ahead, I need to know the format of my XML source: it comes from CTE (Common Table Expression). So I run just this statement

Click to enlarge

Comparing this output with the one I used in my examples, you can build following map

McDonald case	My example
ROWSET	alertlog
ROW	msg
TABLE_NAME	txt
PARTITION_NAME	txt
HIGH_VALUE	txt

Based on all you saw untill now, I can rewrite the above statement like this one (in a red square the two core pieces)

with xml 
   as (
      select
         table_name
       , dbms_xmlgen.getxmltype(
            q'[select 
               table_name
             , partition_name
             , high_value
      from 
          dba_tab_partitions
      where 
          table_name= ']'||table_name||q'[']') as xml_output
      from dba_tables t
      where table_name in ('COMPOSITE_RNG_LIST')
      and owner='FRTD_CORP'
      )
select 
      rws.table_name
    , rws.partition_name
    , rws.high_value
from 
      xml x
    , xmltable('/ROWSET/ROW'
               PASSING
                       x.xml_output 
               COLUMNS 
                       table_name PATH 'TABLE_NAME'
                     , partition_name PATH 'PARTITION_NAME'
                     , high_value PATH 'HIGH_VALUE') rws
order by 1,2,3;

Click to enlarge

Pay attention on the case of you are searching. If you remember, XML is case sensitive, so you must specify ROWSET, ROW and others in upper case. Infact if you run the previous statement using the search in lower case you found noting:

XML is case sensitive

For semplicity, the following picture I just put together both "select" pieces about old and new syntax.

Click to enlarge

Update

2017/Sep/10 - Added the section about Connor McDonald.

2017/Sep/11 - Added the case sensitive example

Reference

[1] https://www.youtube.com/watch?v=yKHQQXKdfOM&t=82s
[2] https://docs.oracle.com/database/122/ARPLS/DBMS_XMLGEN.htm#ARPLS374

[3] http://orasal.blogspot.com/2017/09/using-xml-argument-index.html

Part09 Index Part11

Using XML 09: SELECT, Part 3b/3

In the previous post I saw that the XQuery_string '/alertlog' return just one row. So my question is "what if I change the row-pattern from '/alertlog' to /alertlog/msg or /alertlog/msg/txt"?

Here the answere

XQuery_string: /alertlog/msg

As you can see, in this case there will be 6 rows: one for each element. And this is exactly what I want

SQL> SELECT rownum, t.*, tx.* from mytab t, 

  2> XMLTable('/alertlog/msg' PASSING t.XML_COLUMN) tx

XQuery_string: /alertlog/msg/txt

Also in this case I have 6 rows. But here I have only the <txt> element (it was my request)

SQL> SELECT rownum, t.*, tx.* from mytab t, 

  2> XMLTable('/alertlog/msg/txt' PASSING t.XML_COLUMN) tx

I skip the output between line 2 and 5.

XQuery_string: more examples

Here other two example: similar to the previous posts

SQL> SELECT rownum, t.*, tx.* from mytab t, 

  2> XMLTable('/alertlog/msg/[@pid="65370"]' PASSING t.XML_COLUMN) tx

SQL> SELECT rownum, t.*, tx.* from mytab t, 

  2> XMLTable('/alertlog/msg/[@pid="65263"]' PASSING t.XML_COLUMN) tx

and if I modify the search

SQL> SELECT rownum, t.*, tx.* from mytab t, 

  2> XMLTable('/alertlog/msg/[@pid="65370"]/txt' PASSING t.XML_COLUMN) tx

Part08 Index Part10

Using XML 08: SELECT, Part 3a/3

Continune from previous post

XMLTable [4]

XMLTable decompose the result of an XQuery evaluation into relational rows and columns. Or: the XMLTABLE operator allows us to split the XML data into rows and project columns on to it [13]. You can query the result returned by the function as a virtual relational table using SQL.

XMLTABLE (<XQuery_string> <XMLTABLE_options>)
  |- <XMLTABLE_options>   => <XML_passing_clause> [COLUMNS <XML_table_colums>]
  |- <XML_passing_clause> => PASSING <expr> [AS <identifier>]
  |- <XML_table_colums>   => <column> [<datatype>|XMLTYPE] [PATH <string>] [DEFAULT <expr>]

where [4][5]

• <XQuery_string> is a literal string. XQuery_string is sometimes called the row pattern of the XMLTable call. It is a complete XQuery expression. The value of XQuery_string serves as input to the XMLTable function; it is this XQuery result that is decomposed and stored as relational data.
• The <expr> in the XML_passing_clause is an expression returning an XMLType or an instance of a SQL scalar data type (that is, not an object or collection data type) that is used as the context for evaluating the XQuery expression. You can specify only one expr in the PASSING clause without an identifier. Each expression expr can be a table or view column value, a PL/SQL variable, or a bind variables with proper casting. The result of evaluating each expr is bound to the corresponding identifier in the XQuery_string. If any expr that is not followed by an AS clause, then the result of evaluating that expression is used as the context item for evaluating the XQuery_string
• The optional COLUMNS clause defines the columns of the virtual table to be created by XMLTable. If you omit the COLUMNS clause, then XMLTable returns a row with a single XMLType pseudocolumn named COLUMN_VALUE. For each resulting column you must specify the column data type, which can be XMLType or any other data type. If the column data type is XMLType, then specify the XMLTYPE clause.
• If you omit PATH, then the XQuery expression column is assumed. For example:

          XMLTable(... COLUMNS xyz)

          is equivalent to

          XMLTable(... COLUMNS xyz PATH 'XYZ')

          You can use different PATH clauses to split the XQuery result into different virtual-table columns.

In a more simple way

XMLTABLE (<row-pattern> 
          PASSING <XMLType>
          COLUMNS <column_name> <datatype> PATH <string>) 


Ok. Let's start

The row-pattern is '/alertlog'; the PASSING clause is......well, I try "SELECT * FROM MYTAB" (because Each expression expr can be a table or view column value)

SQL> SELECT * FROM XMLTable('/alertlog' PASSING (select * from mytab))

SQL> SELECT * FROM XMLTable('/alertlog' PASSING (select KEY_COLUMN, XML_COLUMN from mytab))

De

Ok. <exp> can be a table or view column name, but it must return an XML or an instance of a SQL scalar data type. So I have to try something simple. Because MYTAB table is

I specify just the MYTAB.XML_COLUMN column

SQL> SELECT * FROM XMLTable('/alertlog' PASSING (select XML_COLUMN from mytab))

Remember that if you omit the COLUMNS clause, then XMLTable returns a row with a single XMLType pseudocolumn named COLUMN_VALUE.

I want to know how many rows are returned, so I use thr ROWNUM pseudo column

SQL> col COLUMN_VALUE for a60
SQL> SELECT rownum, t.*
SQL> FROM XMLTable('/alertlog' PASSING (select XML_COLUMN from mytab)) t

Perfect. Now because I want all columns of MYTAB, the only way I have in my mind is to join the XMLTable with MYTAB, but I'not  not sure: I try

SQL> SELECT tk.key_column, tx.*
SQL> FROM XMLTable('/alertlog' PASSING (select XML_COULMN from mytab)) tx, mytab t;

I tried to use the the new syntax in Oracle: the "JOIN" clause. See the Appendix section at the end of this post.

In the previous SELECT, I specified "tk.key_column" and "tx.xml_column". I would like not specify the columns, so I change previous statement just aa little

SQL> SELECT * FROM XMLTable('/alertlog' PASSING t.XML_COLUMN) tx, mytab t

I want to try a stupid thing: I want to change the order between XMLTable and MYTAB. Why? Because in the exception ORA-00904, Oracle told me that the "T.XML_COLUMN" is an invalid identifier. It's strange, because I specified the "T" table just after the comma in the FROM clause.

 SQL> SELECT * FROM mytab t, XMLTable('/alertlog' PASSING t.XML_COLUMN) tx 

Great, but I want to show also the row numebr

 SQL> SELECT rownum, t.*, tx.*
 SQL> FROM mytab t, XMLTable('/alertlog' PASSING t.XML_COLUMN) tx 

As usual, one row is returned.

What if I change the XQuery_string/row-pattern, from /alertlog to /alertlog/msg?

(to be continued)




Appendix

If I use the JOIN syntax I had following error

It's new for me. The problem is the absence of ON clause. In fact if I use it with the condition "1=1", then it works

It non depend on the join that include the XMLTable: the ON condition is mandatory for the syntax. Well, as I told before, it's the first time I incurred in the errore. This is because I always used the ON.

Update

2017/Sep/11 - Update the Reference section adding [13] and [14]

References

[1] https://docs.oracle.com/database/122/SQLRF/EXTRACT-XML.htm#SQLRF00640
[2] https://docs.oracle.com/database/122/SQLRF/EXTRACTVALUE.htm#SQLRF06173
[3] https://docs.oracle.com/database/122/SQLRF/XMLQUERY.htm#SQLRF06209
[4] https://docs.oracle.com/database/122/SQLRF/XMLTABLE.htm#SQLRF06232
[5] https://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#ADXDB5121
[6] http://allthingsoracle.com/xquery-for-absolute-beginners-part-1-xpath/
[7] http://allthingsoracle.com/xquery-for-absolute-beginners-part-2-xquery-and-the-oracle-database/
[8] https://www.youtube.com/watch?v=n_dEb6myFH4
[9] https://www.youtube.com/watch?v=5d4fZVxFx20
[10] Pro Oracle SQL, pag 481 written by Morton, Osborne, Shamsudeen [Apress]
[11] http://www.ateam-oracle.com/using-xmltable-and-xmltype-to-extract-html-clob-data/
[12] http://www.oradev.com/xml_functions.html
[13] https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql
[14] XMLTable (PDF)

Part07 Index Part09