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