Insert xml into XmlType field

user1945034 picture user1945034 · Oct 29, 2013 · Viewed 16.7k times · Source

I have this table in Oracle 11g:

create table tmp_test_xml (
    name_xml varchar2(4000),
    file_xml xmltype
);

At this link oracle binding xmltype, I have read that for a correct insert into a field XMLType I must to use the "Xmltype binding" as this insert:

insert into tmp_test_xml values (
          'file.xml',
          xmltype(
            '<?xml version="1.0" encoding="UTF-8"?>
            <list_book>
                <book>1</book>
                <book>2</book>
                <book>3</book>
            </list_book>'
          )
);

But, if I try to lunch this insert without the binding in XMLType, work very fine:

insert into tmp_test_xml values (
          'file.xml',
            '<?xml version="1.0" encoding="UTF-8"?>
            <list_book>
                <book>1</book>
                <book>2</book>
                <book>3</book>
            </list_book>'
);

Now, is facoltative use binding or not? What is the correct insert?

Why the second insert works?

Answer

Nagh picture Nagh · Oct 30, 2013

Oracle tries to do job for you, so if your datatype doesn't match column datatype, it attempts to convert data into correct data type.

Since you have correct xml string in your insert statement, database did conversion to xmltype for you.

And I'm not sure, but I doubt there were a word must use XMLType binding.
But you might want use it, if you need to have more control when creating XML from string.
Check XMLType constructors to get an idea.