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.

martedì, settembre 19, 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

  • XMLElement
  • XMLAttributes
  • XMLForest
  • XMLConcat
  • XMLAgg
  • XMLCOLATTVAL
  • DBMS_XMLGEN





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:

XMLElement([NAME] identifier, [value_expr_element [AS alias]])

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






lunedì, settembre 11, 2017

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 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 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 Part09

        giovedì, agosto 31, 2017

        Using XML 07: SELECT, Part 2/3

        Because the EXTRACT [1] and EXTRACTVALUE [2] functions are deprecated in 11gR2 [10], you have to use the XMLQuery [3] and XMLTable [4] (respectively).

        XMLQuery [1]

        XMLQUERY lets you query XML data in SQL statements. It takes an XQuery expression as a string literal, an optional context item, and other bind variables and returns the result of evaluating the XQuery expression.

        The more simple syntax is

        XMLQuery(XQuery_string PASSING expr RETURNING CONTENT)

        where [1]

        • XQuery_string is a complete XQuery expression, including prolog
        • The expr is an expression returning an XMLType or an instance of a SQL scalar data type that is used as the context for evaluating the XQuery expression. 
        • RETURNING CONTENT indicates that the result from the XQuery evaluation is either an XML 1.0 document or a document fragment conforming to the XML 1.0 semantics.
        or in simple words
        • XMLQueryis the search path
        • "expr" is the XML column of the table 
        Here there are 3 examples, so you can compare the output, with the one of EXTRACT function provided in the previous post

        SQL> SELECT rownum, t.*, 
          2> XMLQuery('/alertlog' PASSING t.XML_COLUMN  RETURNING CONTENT) extract FROM mytab t


        SQL> SELECT rownum, t.*, 
          2> XMLQuery('/alertlog/msg' PASSING t.XML_COLUMN  RETURNING CONTENT) extract FROM mytab t


        SQL> SELECT rownum, t.*,
          2> XMLQuery('/alertlog/msg/txt' PASSING t.XML_COLUMN  RETURNING CONTENT) extract FROM mytab t


        Here other two examples



        As you can see, also if there is a little difference in the syntax between XMLQuery and EXTRACT function, the outptu is the same. The XMLQuery function returns the TAGs. The same thing of the EXTRACT function.

        In the next post XMLTable


        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] http://allthingsoracle.com/xquery-for-absolute-beginners-part-1-xpath/
        [6] http://allthingsoracle.com/xquery-for-absolute-beginners-part-2-xquery-and-the-oracle-database/
        [7] https://www.youtube.com/watch?v=n_dEb6myFH4
        [8] https://www.youtube.com/watch?v=5d4fZVxFx20
        [9] Pro Oracle SQL, pag 481 written by Morton, Osborne, Shamsudeen [Apress]
        [10] http://www.oracle.com/technetwork/database/features/xmldb/xmlqueryoptimize11gr2-168036.pdf (PDF)

        mercoledì, agosto 30, 2017

        Using XML 06: SELECT, Part 1/3

        In the last two posts [3][4], I was shown how to insert a record (an XML document) in an XML column. Here I want to show how to select data inside an XML column

        In order to SELECT data, you have to use some functions. Two of these are EXTRACT [1] and EXTRACTVALUE [2]

        EXTRACT [1]

        It applies to a VARCHAR2 XPath string and returns an XMLType instance containing an XML fragment. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.

        EXTRACT (<XMLType_instance><XPATH_string>)

        I start using just EXTRACT(XML_COLUMN, '/alertlog')


        The result is the entire XML content. This is because I chosen the root node. So, what I expect if I use EXTRACT(XML_COLUMN, '/alertlog/msg') is the EXTRACT column without the root node. And in fact:


        A similar things I expect if I use EXTRACT(XML_COLUMN, '/alertlog/msg/txt'). I mean, the output should be only the "txt" element.


        In all cases, only one row is returned (yes there is one row in the table).


        EXTRACTVALUE [2]

        The EXTRACTVALUE function takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node. The result must be a single node and be either a text node, attribute, or element. If the result is an element, then the element must have a single text node as its child, and it is this value that the function returns. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.

        EXTRACTVALUE (<XMLType_instance><XPATH_string>)


        I have to follow the manual above: The result must be a single node and be either a text node, attribute, or element. If the result is an element, then the element must have a single text node as its child, and it is this value that the function returns. This is why I had previous exception. If I specify just an element, then...


        Also this, is a valid statement


        As you see in this case, only onw row is returned. Anyway, the documentation says:

        The EXTRACT (XML) function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the XMLQUERY function instead. See XMLQUERY for more information [1]

        The EXTRACTVALUE function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the XMLTABLE function, or the XMLCAST and XMLQUERY functions instead. See XMLTABLE, XMLCAST, and XMLQUERY for more information [2]

        In the next post. we see how to use the XMLQuery function in order to obtain the same result

        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://orasal.blogspot.it/2017/08/using-xml-04-insert-into-table.html
        [4] https://orasal.blogspot.it/2017/08/using-xml-04-insert-into-table-part-22.html

        Part05 Part07

        martedì, agosto 29, 2017

        Using XML 05: INSERT into table, Part 2/2

        In the last post, I tried to insert an extract of the alertlog.xml into an XML column

        INSERT INTO mytab VALUES (1, XMLType(bfilename('MYXMLDIR', 'mylog.xml'), nls_charset_id('AL32UTF8')));

        and I had the following exception

        ERROR at line 1:
        ORA-31061: XDB error: XML event error
        ORA-19202: Error occurred in XML processing
        In line 1 of orastream:
        LPX-00225: end-element tag "txt" does not match start-element tag ""

        Really I aspected this error, because I'm not sure if my "tail" command created a "well formed" XML alert log file. So I have to check it.


        Here you see that the extract of the alertlog.xml begin with two closed TAG "</txt>" and "</msg>", so I have to remove those first two lines and then reinsert my log file. This is how the file starts after the modifications:


        Now I can do a new insert


        Ouch! Another error: LPX-00245. If I check the contents of the log.xml....


        ...there is a TAG </msg>", but it appears more times, so it isn't a root node. I have to add a root TAG as the first and last line. I choose "<alertlog></alertlog>" TAG


        At this point, I expect that my log.xml file is well formed and that the new insert should be OK.


        Yessss. I inserted my first XML row. Now I want to see what there is in this column


        Only the first character are shown. This is because the XML is managed by SQL*Plus as a LONG type, so I have to modify the "long" settings


        (Some output was deleted)



        Conclusions (until now)

        1. The insert of an XML document into an XML column will insert just one row.
        2. The XML type check automatically if the XML document is well formed [1]

        References

        [1] https://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb01int.htm#BABCCCJI

        Part 04 Part06