Using XML 04: INSERT into table, Part 1/2

What I want to do in this post is to try to insert an XML document into a table. Because in Oracle 11g+ there is an XML version of the alert.log, I take just some lines from it and save the output in a file. I save the new file in the /tmp/mylog.xml file.

Now I create a table with just two columns. One will be the primary key, the other one will be an XML column type

 I run a very simple insert, trying to put the "this-is-a-test" string in the XML column.

The exception LPX-00210 is what exactly I expect: if you remember, a "well formed" XML document, must begin with angular parentheses (<).

In order to insert the XML content of the "mylog.log" file into MYTAB table, what I have really to do, is to run the following statement:

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

Because of this, I have to introduce "XMLType" and "bfilename" first.

BFILENAME [1]

BFILENAME ('<oracle_directory>', '<filename>')

BFILENAME returns a BFILE locator that is associated with a physical LOB binary file on the server file system.
'oracle_directory' is a database object that serves as an alias for a full path name on the server file system where the files are actually located. 'filename' is the name of the file in the server file system.

You must create the directory object and associate a BFILE value with a physical file before you can use them as arguments to BFILENAME in a SQL or PL/SQL statement.

Defining XMLType Tables and Columns [2][3][5]

You can use a simple INSERT operation in SQL or PL/SQL to load an XML document into the database. Before the document can be stored as an XMLType column or table, you must convert it into an XMLType instance using one of the XMLType constructors.

XMLType constructors allow an XMLType instance to be created from different sources, including VARCHAR, CLOB, and BFILE values.

You can use XMLType as you would any other SQL data type. For example, you can create an XMLType table or view, or an XMLType column in a relational table.

You can use XMLType in PL/SQL stored procedures for parameters, return values, and variables. XMLType is an Oracle Database object type, so you can also create a table of XMLType object instances.

By default, an XMLType table or column can contain any well-formed XML document XMLType is an abstract data type that provides different storage models to best fit your data and your use of it. 

XMLType tables and columns can be stored in these ways (there are two possibilities):

• The table is relational, with an XMLType column and a non-XMLType column.
• The table is of data type XMLType.

http://docs.oracle.com/database/122/ADXDB/how-to-use-XML-DB.htm#ADXDB6338

This section describe an XMLType constructor [4]:

constructor function XMLType(
   xmlData    IN clob
 , schema     IN varchar2 := NULL
 , validated  IN number   := 0
 , wellformed IN number   := 0
)
return self as result deterministic;

constructor function XMLType(
   xmlData    IN varchar2
 , schema     IN varchar2 := NULL
 , validated  IN number   := 0
 , wellformed IN number   := 0
)
return self as result deterministic;

constructor function XMLType(
   xmlData    IN blob
 , csid       IN number   /* The character set id of input XML data */
 , schema     IN varchar2 := NULL
 , validated  IN number   := 0
 , wellformed IN number   := 0
)
return self as result deterministic;

constructor function XMLType(
   xmlData    IN bfile
 , csid       IN number   /* The character set id of input XML data */
 , schema     IN varchar2 := NULL
 , validated  IN number   := 0
 , wellformed IN number   := 0
)
return self as result deterministic;

How you can see, XMLType constructor accept as the first parameter a CLOB, a VARCHAR2, a BinaryLOB or BinaryFILE. This is why you read above.

XMLType constructors allow an XMLType instance to be created from different sources, including VARCHAR, CLOB, and BFILE values.

Also, if you pass to the constructor a BLOB or a BFILE, you must specify the CHARACTER_SET (csid value). But if you are using a CLOB or VARCHAR2, you haven't specify this. I suppose that this is because in case of "CHARACTER", the right set is derived directly from da database NLS_CHARCTER_SET parameter.

So I can write (in the more simple form, that is, passing the first two parameters):

XMLType (xmlData IN blob, csid IN number)

where (I'm using the AL32UTF8 CHARACTER_SET in my DBs)

xmlData ::= BFILENAME ('<oracle_directory>', '<filename>') (see BFILENAME above)

csid    ::= NLS_CHARSET_ID('AL32UTF8')

I can rewrite the XMLType like this

XMLType (BFILENAME ('<oracle_directory>', '<filename>'), NLS_CHARSET_ID ('AL32UTF8'))

At this point, I'm ready to insert my alert log file in MYTAB table. First I create the Oracle directory

then I populate the table

Should I expect the LPX-00225 exception? Maybe yes.

(To be continued)

References
[1] https://docs.oracle.com/database/122/SQLRF/BFILENAME.htm#SQLRF00610
[2] http://docs.oracle.com/database/122/ADXDB/how-to-use-XML-DB.htm#ADXDB4049
[3] http://docs.oracle.com/database/122/ADXDB/how-to-use-XML-DB.htm#ADXDB6338
[4] https://docs.oracle.com/database/122/ARPLS/XMLTYPE.htm#ARPLS71992
[5] https://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb01int.htm#BABCCCJI

Part03 Index Part05

Using XML 03: Introduction, Part 3/3

In this post I continue the introduction of an XML document.

XML Namespaces

XML Namespaces provide a method to avoid element name conflicts.

Please, see [1]: they explain in really simple way what the "XML Namespace are.

XML and XPath [2][3]

The XPath language is based on a tree representation of the XML document, and provides the ability to navigate around the tree, selecting nodes by a variety of criteria. It is used to navigate through elements and attributes.

Based on the previosu XML document, you can generalyze it as follow:

XML documents have a hierarchical structure and can conceptually be interpreted as a tree structure, called an XML tree.

XML documents must contain a root element (one that is the parent of all other elements). All elements in an XML document can contain sub elements, text and attributes. The tree represented by an XML document starts at the root element and branches to the lowest level of elements.

XPath defines a syntax named XPath expressions that identifies one or more internal components (elements, attributes, etc.) of an XML document. In popular use (though not in the official specification), an XPath expression is often referred to simply as "an XPath".

Syntax and semantics (XPath 1.0) [2]

The most important kind of expression in XPath is a location path. A location path consists of a sequence of location steps. Each location step has three components:

• An axis. An "axis" specifiers indicate navigation direction within the tree representation of the XML document. The axes available are

https://en.wikipedia.org/wiki/XPath#Axis_specifiers

• A node test

https://en.wikipedia.org/wiki/XPath#Node_tests

• Zero or more predicates.

https://en.wikipedia.org/wiki/XPath#Predicates

In the table below we have listed some XPath expressions and the result of the expressions:

References

[1] https://www.w3schools.com/xml/xml_namespaces.asp
[2] https://en.wikipedia.org/wiki/XPath
[3] https://www.w3schools.com/xml/xml_xpath.asp
[4] https://en.m.wikipedia.org/wiki/XML_tree

Part02 Index Part 04

Using XML 02: Introduction, Part 2/3

In the previous post, I introduced an XML file, in its more simple form. Here I would like to be a little more formal. As usual, check the References section to read the sources for more information.

Consider following XML document (I took the information in [1] and [2])

XML Tree Structure

How wrote in [3], a general structure of an XML document is

<root>
  <child>
    <subchild>.....</subchild>
  </child>
</root>


The terms parent, child, and sibling are used to describe the relationships between elements. Parent have children. Children have parents. Siblings are children on the same level (brothers and sisters). All elements can have text content (Death Times Three) and attributes (category="Nero Wolfe").

Self-Describing Syntax

XML uses a much self-describing syntax.

A prolog defines the XML version and the character encoding:

<?xml version="1.0" encoding="UTF-8"?&gt;

The next line is the root element of the document:

<bibliography>

The next line starts a <book> element:

<book category="Nero Wolfe">

The <book> elements have 3 child elements: <title>, <publisher>, <year>.

<Title lang="en">Death Times Three</Title>

<Publisher>Bantam Books</Publisher>

<year month="December">1985</year>

The next line ends the book element:

</book>

XML Syntax Rules [5]

The syntax rules of XML are very simple and logical. The rules are easy to learn and easy to use. If an XML document conforms to the syntax rules below, then it said to be "Well Formed".

XML Documents Must Have a Root Element

<bibliography>
 <book category="Nero Wolfe">

  <Title lang="en">Death Times Threegt;/Title>
  <Publisher>Bantam Books>/Publisher>
  <year month="December">1985>/year>
 </book>
<bibliography>

The XML Prolog

The XML prolog is optional. If it exists, it must come first in the document: 

<?xml version="1.0" encoding="UTF-8"?>

All XML Elements Must Have a Closing Tag

<bibliography>
 <book category="Nero Wolfe">
  <Title lang="en">Death Times Three</Title>
  <Publisher>Bantam Books</Publisher>
  <year month="December">1985</year>
 </book>
</bibliography>

XML Tags are Case Sensitive

<bibliography>
 <book category="Nero Wolfe">
  <Title lang="en">Death Times Three</Title>
  <Publisher>Bantam Books</Publisher>
  <year month="December">1985</year>
 </book>
</bibliography>

XML Elements Must be Properly Nested

<bibliography>
 <book category="Nero Wolfe">
  <Title lang="en">Death Times Three</Title>
  <Publisher>Bantam Books</Publisher>
  <year month="December">1985</year>
 </book>
</bibliography>

XML Attribute Values Must be Quoted

<bibliography>
 <book category="Nero Wolfe">
  <Title lang="en">Death Times Three</Title>
  <Publisher>Bantam Books</Publisher>
  <year month="December">1985</year>
 </book>
</bibliography>

You can use double quotes or single quotes. If the attribute value itself contains double quotes you can use single quotes.

Entity References

Some characters have a special meaning in XML.

You have to use the letters (the column on the left) instead of the symbols (central column).

Comments in XML

The XML comment is optional. If it exists, it must follow rules

• <!-- This is a comment -->
• Two dashes in the middle of a comment are not allowed.

XML Stores New Line as LF [5]

• Windows applications store a new line as carriage return and line feed (CR+LF)
• Unix and Mac OSX uses LF
• Old Mac systems use CR
• XML stores a new line as LF

References
[1] https://en.wikipedia.org/wiki/Rex_Stout_bibliography
[1] https://it.wikipedia.org/wiki/Rex_Stout
[2] https://en.m.wikipedia.org/wiki/XML_tree
[3] https://www.w3schools.com/xml/xml_tree.asp
[4] https://www.w3schools.com/xml/xml_namespaces.asp
[5] https://www.w3schools.com/xml/xpath_syntax.asp
[6] https://www.w3schools.com/xml/xml_xpath.asp
[7] https://en.m.wikipedia.org/wiki/XML_tree
[8] https://en.wikipedia.org/wiki/XPat

Part01 Index Part03

Using XML 01: Introduction, Part 1/3

I don't know very much about XML. Anyway, there are some cases where as DBA, I have to use it. For example, if I want to read the HIGH_VALUE and other LONG columns or if I want to read the hints used by a SQL profile. So, with the following series, I would like to understand better how to use XML. Which I write here is just a minimum of knowledge on XML. I do not pretend to explain to you how to use XML in all its form.

What you read, in this first part is a collection of information taken from several links. You can find them in the References section.

Introduction to XML

Extensible Markup Language (XML) is a simple, very flexible text format derived from SGML (ISO 8879).

XML code is similar to Hypertext Markup Language (HTML). Both XML and HTML contain markup symbols to describe page or file contents. HTML code describes Web page content (mainly text and graphic images) only in terms of how it is to be displayed and interacted with.

Also, while HTML is not case-sensitive, XML it is.

The basic building block of an XML document is an element, defined by tags. An element has a beginning and an ending tag. All elements in an XML document are contained in an outermost element known as the root element. XML can also support nested elements or elements within elements. This ability allows XML to support hierarchical structures. Element names describe the content of the element, and the structure describes the relationship between the elements.

XML is much more accurate than HTML when it comes to closing tags: each one always has a closure.

Tags can also be defined as empty tags but their syntax is <TAG/>

Elements can be better characterized by specifying a list of attributes.

So we have a "start-tag" (STag), an "end-tag" (ETag) and a "tags for for Empty Elements" (EmptyElemTag). We can define them as

STag ::= <Name (Attribute)>
ETag ::= </Name>
EmptyElemTag ::= <Name (Attribute)/>

(As DBA, common queries do not use the EmptyElemTag, but I report it because it is a simple concept and it shows the difference against HTML).

There is always a prologue and is very important: it contains useful guidelines for programs that will use the document. Specifically, the XML document begins with '<' ?xml version=“1.0”? '>'

XML example:

The text: "Rex Stout was an American author of many yellow novels featuring famous Nero Wolfe and his aide and storyteller Archie Goodwin."

become

<?xml version=“1.0”?><author nationality="US" ><name>Rex</name> <surname>Stout</surname> </author> was an American author of many<genere> yellow novels </genere>featuring famous <protagonist><name>Nero</name> <surname>Wolfe&</surname></ protagonist> and his aide and storyteller <storyteller> <protagonist><name>Archie</name> <surname>Goodwin</surname></protagonist></storyteller>.

Graphic view of an XML tree

How to decide whether to represent an entity component as an attribute or as a separate entity? In general, it is difficult or even impossible to make an objective choice, as there is no clear advantage in either solution. In these cases, the programmer is free to use the form he or she prefers.

References

[1] https://www.w3.org/XML/
[2] https://www.w3.org/TR/REC-xml/
[3] http://searchmicroservices.techtarget.com/definition/XML-Extensible-Markup-Language
[4] http://www.diit.unict.it/users/alongheu/tpa/aa0910/tpa_lezione14_XML.pdf (ITA)
[5] https://www.youtube.com/watch?v=yKHQQXKdfOM
[6] http://kerryosborne.oracle-guy.com/2009/07/why-isnt-oracle-using-my-outline-profile-baseline/

Index Part02