Database Software

I've encountered peculiar behavior when using an XMLTYPE.transform() or XMLTRANSFORM along with an INSERT statement.

If the transformed XML contains comments or processing instructions outside of the root element, I get the error: "ORA-19010: Cannot insert XML fragments"

However, the exact same XML -- as XMLTYPE (no transformation) can be inserted into a table without incident. I've noticed that with the transform() method I can get the CLOB value and then cast it back to an XMLTYPE and everything works.

e.g . using either of following methods
SELECT XMLTYPE(XMLSERIALIZE(CONTENT XMLTYPE(…).transform(…))
or
SELECT XMLTYPE(XMLTYPE(…).transform(…).GetClobVal())

So there appears to be a workable solution, but can anyone explain why under one instance the XML is considered a fragment and under the other it is valid. In both cases the XML text appears to be identical.

Is this a known bug?

Our Oracle version: 10.2,0.4

Full Example:

-- Create table
CREATE TABLE my_XML
(id NUMBER NOT NULL,
cml XMLTYPE NOT NULL)
/

-- a transform method having comments or processing instructions in the result produces error: ORA-19010: Cannot insert XML fragments
INSERT INTO my_xml
(
SELECT 1, XMLTYPE('<!-- My MXL Comment --> <MyElement/><?My Processing Instruction?>').transform(a.xsl) new_xml
FROM (SELECT XMLType('<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="*|@*|comment()|processing-instruction()|text()">
<xsl:copy-of select="."/>
</xsl:template>

</xsl:stylesheet>') xsl
FROM dual) a)
/

-- using XMLSERIALIZE to transform the XML as a CLOB and then calling XMLTYPE around the result avoids the error
-- and preserves the comments and processing instructions -- no apparent differnce in the XML
INSERT INTO my_xml
(
SELECT 2, XMLTYPE(XMLSERIALIZE(CONTENT XMLTYPE('<!-- My MXL Comment --> <MyElement/><?My Processing Instruction?>').transform(a.xsl))
)
new_xml
FROM (SELECT XMLType('<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="*|@*|comment()|processing-instruction()|text()">
<xsl:copy-of select="."/>
</xsl:template>

</xsl:stylesheet>') xsl
FROM dual) a)
/

-- using getClobVal() to transform the XML as a CLOB and then calling XMLTYPE around the result avoids the error
-- and preserves the comments and processing instructions -- no apparent differnce in the XML
INSERT INTO my_xml
(
SELECT 3, XMLTYPE(XMLTYPE('<!-- My MXL Comment --> <MyElement/><?My Processing Instruction?>').transform(a.xsl).GetClobVal())new_xml
FROM (SELECT XMLType('<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="*|@*|comment()|processing-instruction()|text()">
<xsl:copy-of select="."/>
</xsl:template>

</xsl:stylesheet>') xsl
FROM dual) a)
/

-- Regular XMLTYPE containing coimmemnts and processing instruction pose no problems
INSERT INTO my_xml
(
SELECT 4, XMLTYPE('<!-- My MXL Comment --> <MyElement/><?My Processing Instruction?>') xml
FROM dual)
/

-- Select values from temp table
SELECT *
FROM my_xml
/