SQL & PL/SQL

Hi,

I have an Oracle table called WMLogger. It has one CLOB column called 'BODY' that contains XML data. It also has a KEYID column that mistakenly has the wrong value in some cases. I am trying to create an UPDATE statement (or even a good select statement) to replace the KEYID with an XML attribute found in the BODY's XML.

Here's an example of the XML:

<INTERFACE MESSAGE_TYPE="RL" EVENT_DTTM = "13-Sep-2012 13:40:43.01" BUSINESS_UNIT="10258" SequenceNumber="0" xmlns="http://Corning.COF.MESSC.PLANT_TO_PS.Schema/Plant.Superschema">

<CONTAINER CONTAINER_ID_CI="800000000001" COUNT="1" CONTAINER_TYPE="" BOX_TYPE="">

<DETAIL>

<CNTR_DETAIL ID="800000000001" SLOT_ID="" ZONE="R01R02" SIZE="2" />

</DETAIL>

</CONTAINER>

</INTERFACE>

This SELECT statement seems to return no rows:

select l.KEYID,t.*
FROM WMLOGGER l,
XMLTABLE ('/CONTAINER[1]'
          PASSING XMLTYPE(l.BODY)
          COLUMNS CONTAINER_ID_CI VARCHAR2(20) PATH '@CONTAINER_ID_CI') t
WHERE l.TAG = 'RL';

I have also tried '/INTERFACE/CONTAINER[1}' and still get no rows returned. Any suggestions?

Thanks,

David

BANNER                                                        

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production                          

CORE 10.2.0.4.0 Production                                        

TNS for Linux: Version 10.2.0.4.0 - Production                  

NLSRTL Version 10.2.0.4.0 - Production