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 Index Part06