How do I display result of EXTRACT in PL/SQL
451861Dec 29 2005 — edited Dec 30 2005I'm new to Oracle. I've begun experimenting with the XML functions. I was able to test existsNode and extractValue and see my results OK using DBMS_OUTPUT.PUT_LINE(<some variable of type X>) but I'm failing with EXTRACT. I currently think that extract returns an XML fragment of data type XMLType. I've failed to figure out how to display this fragment so see if my extract is doing what I expect. I've tried TO_CHAR(), TO_CLOB, and EXTRACT('/Root/text()').getStringVal() with no success. Could one of you point me in the right direction? My test is below.
DECLARE XMLDoc XMLTYPE;
XMLOut XMLType;
BEGIN
XMLDoc := XMLTYPE('<Root>
<Trans_Info Name="ORIGINAL_AUTH_AMOUNT" Value="100.00" Mod_Date="2005-11-18" Trans_Id="100000" Xi_Id="100"></Trans_Info>
<Trans_Info Name="Web Auth ID" Value="Houston" Mod_Date="2005-11-18" Trans_Id="100000" Xi_Id="100"></Trans_Info>
<Trans_Info Name="3_SHIP_TO_STATE" Value="Of Mind" Mod_Date="2005-07-28" Trans_Id="100000" Xi_Id="100"></Trans_Info>
</Root>');
SELECT EXTRACT(VALUE(x),'/Root')
INTO XMLOut
FROM TABLE(XMLSEQUENCE(XMLDoc.EXTRACT('/Root'))) x;
DBMS_OUTPUT.PUT_LINE(XMLOut);
END;