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.ACCESSM ASK 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_M ASK ( 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('ACCES S_MASK',dadDoc) ;

-- parse xml into database
call DB2XML.DXXSHRED XML(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>N O</validation>
<Xcollection>
<prolog>?xml version="1.0"?</prolog>
<root_node>
<element_node name="ACCESSMAS KS">
<element_node name="ACCESSMAS K">
<RDB_node>
<table name="SESSION.A CCESS_MASK" key="ACCESS_MAS K_ID"/>
</RDB_node>
<attribute_no de name="ACCESS_MA SK_ID">
<RDB_node>
<table name="SESSION.A CCESS_MASK"/>
<column name="ACCESS_MA SK_ID" type="integer"/>
</RDB_node>
</attribute_node>
<attribute_no de name="DLR_CD">
<RDB_node>
<table name="SESSION.A CCESS_MASK"/>
<column name="DLR_CD" type="varchar(5 )"/>
</RDB_node>
</attribute_node>
<attribute_no de name="RGN_CD">
<RDB_node>
<table name="SESSION.A CCESS_MASK"/>
<column name="RGN_CD" type="varchar(5 )"/>
</RDB_node>
</attribute_node>
<attribute_no de name="BRN_CD">
<RDB_node>
<table name="SESSION.A CCESS_MASK"/>
<column name="BRN_CD" type="varchar(5 )"/>
</RDB_node>
</attribute_node>
<attribute_no de name="REP_CD">
<RDB_node>
<table name="SESSION.A CCESS_MASK"/>
<column name="REP_CD" type="varchar(5 )"/>
</RDB_node>
</attribute_node>
</element_node>
</element_node>
</root_node>
</Xcollection>
</DAD>