XMLSERIALIZE required on XML insert?

#1

Hello,

I have a question about inserting XML data constructed from relational
data. I don't understand why an explicit XMLSERIALIZE of the XML-
building query seems to be necessary. This looks to be a waste of
resources as the whole XML document needs to be reparsed again on
insert.

Some sample code to recreate my 'problem':

create table srctab (a integer, b varchar(10));
create table xmltab (a integer, xmlcol xml);

insert into srctab values (1,'one'), (2,'two');

--> here I generate one xml document per record in the table

select
a,
xmlelement(
name "tag",
xmlforest(
a as "a",
b as "b"
)
)
from srctab;

--> result... returns type XML

-- A 2
-- ----------- --------------------------------
-- 1 <tag><a>1</a>one</tag>
-- 2 <tag><a>2</a>two</tag>

--> Inserting in the table...

insert into xmltab (a, xmlcol)
select
a,
xmlelement(
name "tag",
xmlforest(
a as "a",
b as "b"
)
)
from srctab;

--> returns "SQL20345N The XML value is not a well-formed document
with a single root element."
--> Weird, as both documents look well-formed to me
--> let's try serializing and re-parsing it again (in this example
implicit, explicit works as well)

insert into xmltab (a, xmlcol)
select
a,
XMLSERIALIZE(
xmlelement(
name "tag",
xmlforest(
a as "a",
b as "b"
)
) as clob(2G))
from srctab;

-->DB20000I The SQL command completed successfully.

It works this way but this seems like a waste of resources, no? The
optimized statement also shows this double effort:

SELECT Q2.A,
$INTERNAL_XMLPARSE$(Q2.$C1)
FROM (
SELECT Q1.A,
$INTERNAL_XMLSERIALIZE$

Kind regards,

Frederik

#2

On Oct 27, 12:19*pm, Frederik Engelen <engelenfrede... (AT) gmail (DOT) com>
wrote:

Quote:

Hello,

I have a question about inserting XML data constructed from relational
data. I don't understand why an explicit XMLSERIALIZE of the XML-
building query seems to be necessary. This looks to be a waste of
resources as the whole XML document needs to be reparsed again on
insert.

Some sample code to recreate my 'problem':

create table srctab (a integer, b varchar(10));
create table xmltab (a integer, xmlcol xml);

insert into srctab values (1,'one'), (2,'two');

--> here I generate one xml document per record in the table

select
* * * * a,
* * * * xmlelement(
* * * * * * * * name "tag",
* * * * * * * * xmlforest(
* * * * * * * * * * * * a as "a",
* * * * * * * * * * * * b as "b"
* * * * * * * * )
* * * * )
from srctab;

--> result... returns type XML

-- A * * * * * 2
-- ----------- --------------------------------
-- * * * * * 1 <tag><a>1</a>one</tag
-- * * * * * 2 <tag><a>2</a>two</tag

--> Inserting in the table...

insert into xmltab (a, xmlcol)
select
* * * * a,
* * * * xmlelement(
* * * * * * * * name "tag",
* * * * * * * * xmlforest(
* * * * * * * * * * * * a as "a",
* * * * * * * * * * * * b as "b"
* * * * * * * * )
* * * * )
from srctab;

--> returns "SQL20345N *The XML value is not a well-formed document
with a single root element."
--> Weird, as both documents look well-formed to me
--> let's try serializing and re-parsing it again (in this example
implicit, explicit works as well)

insert into xmltab (a, xmlcol)
select
* * * * a,
* * * * XMLSERIALIZE(
* * * * xmlelement(
* * * * * * * * name "tag",
* * * * * * * * xmlforest(
* * * * * * * * * * * * a as "a",
* * * * * * * * * * * * b as "b"
* * * * * * * * )
* * * * ) as clob(2G))
from srctab;

-->DB20000I *The SQL command completed successfully.

It works this way but this seems like a waste of resources, no? The
optimized statement also shows this double effort:

SELECT Q2.A,
* * $INTERNAL_XMLPARSE$(Q2.$C1)
FROM (
SELECT Q1.A,
* * $INTERNAL_XMLSERIALIZE$

Kind regards,

Frederik

The XMLSERIALIZE is NOT required and it would be indeed a waste of
resources...

What is missing for your constructed XML to be a well-formed document
is the non-visible document node. Wrap an XMLDOCUMENT function call
around your construction and it should work.

Henrik

BTW: In XQuery there is a "document { }" for that purpose.

#3

On Oct 27, 12:19*pm, Frederik Engelen <engelenfrede... (AT) gmail (DOT) com>
wrote:

Quote:

Hello,

I have a question about inserting XML data constructed from relational
data. I don't understand why an explicit XMLSERIALIZE of the XML-
building query seems to be necessary. This looks to be a waste of
resources as the whole XML document needs to be reparsed again on
insert.

Some sample code to recreate my 'problem':

create table srctab (a integer, b varchar(10));
create table xmltab (a integer, xmlcol xml);

insert into srctab values (1,'one'), (2,'two');

--> here I generate one xml document per record in the table

select
* * * * a,
* * * * xmlelement(
* * * * * * * * name "tag",
* * * * * * * * xmlforest(
* * * * * * * * * * * * a as "a",
* * * * * * * * * * * * b as "b"
* * * * * * * * )
* * * * )
from srctab;

--> result... returns type XML

-- A * * * * * 2
-- ----------- --------------------------------
-- * * * * * 1 <tag><a>1</a>one</tag
-- * * * * * 2 <tag><a>2</a>two</tag

--> Inserting in the table...

insert into xmltab (a, xmlcol)
select
* * * * a,
* * * * xmlelement(
* * * * * * * * name "tag",
* * * * * * * * xmlforest(
* * * * * * * * * * * * a as "a",
* * * * * * * * * * * * b as "b"
* * * * * * * * )
* * * * )
from srctab;

--> returns "SQL20345N *The XML value is not a well-formed document
with a single root element."
--> Weird, as both documents look well-formed to me
--> let's try serializing and re-parsing it again (in this example
implicit, explicit works as well)

insert into xmltab (a, xmlcol)
select
* * * * a,
* * * * XMLSERIALIZE(
* * * * xmlelement(
* * * * * * * * name "tag",
* * * * * * * * xmlforest(
* * * * * * * * * * * * a as "a",
* * * * * * * * * * * * b as "b"
* * * * * * * * )
* * * * ) as clob(2G))
from srctab;

-->DB20000I *The SQL command completed successfully.

It works this way but this seems like a waste of resources, no? The
optimized statement also shows this double effort:

SELECT Q2.A,
* * $INTERNAL_XMLPARSE$(Q2.$C1)
FROM (
SELECT Q1.A,
* * $INTERNAL_XMLSERIALIZE$

Kind regards,

Frederik

[Not sure if my first reply came through]

Using XMLSERIALIZE is indeed a waste of resources and it is not
required. Just wrap the XMLDOCUMENT function around your construction.
It adds the missing non-visible document node to the top of your
document. With that it is well-formed...

Henrik

#4

[it seems that my earlier replies didn't came through]
Hi Frederik,

using XMLSERIALIZE is indeed a waste of resources. Just wrap a call to XMLDOCUMENT around your constructed XML and it will add the non-visible document node and make it well-formed. XQuery has the "document { }" expression for that purpose.

Henrik

#5

On 28 okt, 09:46, Henrik Loeser <henrik.loe... (AT) gmail (DOT) com> wrote:

Quote:

On Oct 27, 12:19*pm, Frederik Engelen <engelenfrede... (AT) gmail (DOT) com
wrote:

Hello,

I have a question about inserting XML data constructed from relational
data. I don't understand why an explicit XMLSERIALIZE of the XML-
building query seems to be necessary. This looks to be a waste of
resources as the whole XML document needs to be reparsed again on
insert.

Some sample code to recreate my 'problem':

create table srctab (a integer, b varchar(10));
create table xmltab (a integer, xmlcol xml);

insert into srctab values (1,'one'), (2,'two');

--> here I generate one xml document per record in the table

select
* * * * a,
* * * * xmlelement(
* * * * * * * * name "tag",
* * * * * * * * xmlforest(
* * * * * * * * * * * * a as "a",
* * * * * * * * * * * * b as "b"
* * * * * * * * )
* * * * )
from srctab;

--> result... returns type XML

-- A * * * * * 2
-- ----------- --------------------------------
-- * * * * * 1 <tag><a>1</a>one</tag
-- * * * * * 2 <tag><a>2</a>two</tag

--> Inserting in the table...

insert into xmltab (a, xmlcol)
select
* * * * a,
* * * * xmlelement(
* * * * * * * * name "tag",
* * * * * * * * xmlforest(
* * * * * * * * * * * * a as "a",
* * * * * * * * * * * * b as "b"
* * * * * * * * )
* * * * )
from srctab;

--> returns "SQL20345N *The XML value is not a well-formed document
with a single root element."
--> Weird, as both documents look well-formed to me
--> let's try serializing and re-parsing it again (in this example
implicit, explicit works as well)

insert into xmltab (a, xmlcol)
select
* * * * a,
* * * * XMLSERIALIZE(
* * * * xmlelement(
* * * * * * * * name "tag",
* * * * * * * * xmlforest(
* * * * * * * * * * * * a as "a",
* * * * * * * * * * * * b as "b"
* * * * * * * * )
* * * * ) as clob(2G))
from srctab;

-->DB20000I *The SQL command completed successfully.

It works this way but this seems like a waste of resources, no? The
optimized statement also shows this double effort:

SELECT Q2.A,
* * $INTERNAL_XMLPARSE$(Q2.$C1)
FROM (
SELECT Q1.A,
* * $INTERNAL_XMLSERIALIZE$

Kind regards,

Frederik

The XMLSERIALIZE is NOT required and it would be indeed a waste of
resources...

What is missing for your constructed XML to be a well-formed document
is the non-visible document node. Wrap an XMLDOCUMENT function call
around your construction and it should work.

Henrik

BTW: In XQuery there is a "document { }" for that purpose.- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Thanks Henrik, I might've asked it directly to you as well ;-)

--
Frederik

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

XMLSERIALIZE required on XML insert?

comp.databases.ibm-db2 comp.databases.ibm-db2

XMLSERIALIZE required on XML insert? - 10-27-2011 , 05:19 AM

Re: XMLSERIALIZE required on XML insert? - 10-28-2011 , 02:46 AM

Re: XMLSERIALIZE required on XML insert? - 10-28-2011 , 02:51 AM

Re: XMLSERIALIZE required on XML insert? - 10-28-2011 , 02:58 AM

Re: XMLSERIALIZE required on XML insert? - 10-28-2011 , 03:12 AM