xml input into temp table.

[joe pribele]

I have this stored procedure that takes some xml as input. What I need
to is use xml as a table so that I can join other tables to the xml
and get information back that matches the criteria.

I can use dxxshredxml to put it into a regular table and everything
works great. but when I try to put it into a temp table it doesn't
work. I get an error saying Session.ACCESSMASK doesn't have a column
name ACCESS_MASK_ID.

Do I have to use a temp table ? or is there some way I can make joins
to the xml directly ?

Thanks in advance.

Here is the my test procedure:

CREATE PROCEDURE WEBADM.ACCESS_MASK ( IN xmlDoc CLOB(1M) )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
-- xmlDoc access masks list
------------------------------------------------------------------------
P1: BEGIN
declare dadDoc clob(1M);
declare retCode integer;
declare retMsg varchar(1000);

DECLARE GLOBAL TEMPORARY TABLE SESSION.ACCESS_MASK (
ACCESS_MASK_ID INTEGER,
DLR_CD VARCHAR(5),
RGN_CD VARCHAR(5),
BRN_CD VARCHAR(5),
REP_CD VARCHAR(5)
)
ON COMMIT PRESERVE ROWS;

-- get Dad file
call WEBADM.DWM_GET_DAD_FILE('ACCESS_MASK',dadDoc);

-- parse xml into database
call DB2XML.DXXSHREDXML(dadDoc, xmlDoc, retCode,retMsg);

drop table SESSION.ACCESS_MASK;
END P1

here is the DAD:

<?xml version="1.0"?>
<!DOCTYPE DAD SYSTEM "/opt/IBM/db2/V8.1/db2xml/dtd/dad.dtd">
<DAD>
<validation>NO</validation>
<Xcollection>
<prolog>?xml version="1.0"?</prolog>
<root_node>
<element_node name="ACCESSMASKS">
<element_node name="ACCESSMASK">
<RDB_node>
<table name="SESSION.ACCESS_MASK" key="ACCESS_MASK_ID"/>
</RDB_node>
<attribute_node name="ACCESS_MASK_ID">
<RDB_node>
<table name="SESSION.ACCESS_MASK"/>
<column name="ACCESS_MASK_ID" type="integer"/>
</RDB_node>
</attribute_node>
<attribute_node name="DLR_CD">
<RDB_node>
<table name="SESSION.ACCESS_MASK"/>
<column name="DLR_CD" type="varchar(5)"/>
</RDB_node>
</attribute_node>
<attribute_node name="RGN_CD">
<RDB_node>
<table name="SESSION.ACCESS_MASK"/>
<column name="RGN_CD" type="varchar(5)"/>
</RDB_node>
</attribute_node>
<attribute_node name="BRN_CD">
<RDB_node>
<table name="SESSION.ACCESS_MASK"/>
<column name="BRN_CD" type="varchar(5)"/>
</RDB_node>
</attribute_node>
<attribute_node name="REP_CD">
<RDB_node>
<table name="SESSION.ACCESS_MASK"/>
<column name="REP_CD" type="varchar(5)"/>
</RDB_node>
</attribute_node>
</element_node>
</element_node>
</root_node>
</Xcollection>
</DAD>