Error in using XMLSERIALIZE function in DB2 Triggers

[Medha Kulkarni]

Hi all,

I have defined a trigger on update of a non-xml column of a table. This table also has some XML column & trigger copies contents of this XML column to another table having XML column using XMLSERIALIZE function. After executing this trigger I get error:
"DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1242N An XML feature is not supported in the context where it is used.
Reason code = "6". LINE NUMBER=14. SQLSTATE=42997"

Is it that XMLSERIALIZE function cant be used in triggers?

The trigger looks something like this:

1. Trigger is defined on a following table:
DB2ADMIN.XML_TAB(
ID INTEGER,
REQUEST_TYPE INTEGER,
STATUS INTEGER,
REQ_DETAILS XML
)
2. Trigger populates following table:
DB2ADMIN.LOG_RESULT(
ID INTEGER,
ACTION_NAME VARCHAR(255),
ACTION_DETAILS XML
)

3. Create Trigger statement is as follow:

CREATE TRIGGER MY_TRG
AFTER UPDATE OF STATUS ON DB2ADMIN.XML_TAB
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
IF N.REQUEST_TYPE = 1 AND N.STATUS = 1 THEN
INSERT INTO DB2ADMIN.LOG_RESULT (ACTION_NAME,ACTION_DETAILS) VALUES
('APPROVE REQUEST', XMLSERIALIZE(N.XML_COL as VARCHAR(255)));
END IF;
END@

I also tried to insert XML value directly to target table WITHOUT using XMLSERIALIZE function & again got same error. i.e. :
"DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1242N An XML feature is not supported in the context where it is used.
Reason code = "6". LINE NUMBER=12. SQLSTATE=42997"

Any inputs on this will be really appreciated.

Thanks and Regards,
Medha.

[Knut Stolze]

Post by Medha Kulkarni
Hi all,
I have defined a trigger on update of a non-xml column of a table. This
table also has some XML column & trigger copies contents of this XML
column to another table having XML column using XMLSERIALIZE function.
"DB21034E The command was processed as an SQL statement because it was not a
SQL1242N An XML feature is not supported in the context where it is used.
Reason code = "6". LINE NUMBER=14. SQLSTATE=42997"
Is it that XMLSERIALIZE function cant be used in triggers?
DB2ADMIN.XML_TAB(
ID INTEGER,
REQUEST_TYPE INTEGER,
STATUS INTEGER,
REQ_DETAILS XML
)
DB2ADMIN.LOG_RESULT(
ID INTEGER,
ACTION_NAME VARCHAR(255),
ACTION_DETAILS XML
)
CREATE TRIGGER MY_TRG
AFTER UPDATE OF STATUS ON DB2ADMIN.XML_TAB
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
IF N.REQUEST_TYPE = 1 AND N.STATUS = 1 THEN
INSERT INTO DB2ADMIN.LOG_RESULT (ACTION_NAME,ACTION_DETAILS) VALUES
('APPROVE REQUEST', XMLSERIALIZE(N.XML_COL as VARCHAR(255)));
END IF;
I also tried to insert XML value directly to target table WITHOUT using
"DB21034E The command was processed as an SQL statement because it was not a
SQL1242N An XML feature is not supported in the context where it is used.
Reason code = "6". LINE NUMBER=12. SQLSTATE=42997"

Your problem is that you try to use XML values in triggers in a way that is
not yet supported. Have a look at the error message SQL1242. This is what
it says for reason code 6:

6 References to transitions variables of data type XML are not supported in
a trigger definition.