How to convert XMLTYPE to VARCHAR in ORACLE?

Question

I have two columns in my table (TRANSACTION) in ORACLE, which are XMLTYPE (XML_IN and XML_OUT). My procedure does not work, because I do not know how to convert it to VARCHAR or something like that (I just think this is a mistake). My procedure:

PROCEDURE SEARCH_XML
(
    P_ID_TRANSACTION IN TRANSACTION.ID_TRANSACTION%TYPE,

    P_CURSOR OUT T_CURSOR
)
IS
BEGIN

    OPEN P_CURSOR FOR

    SELECT T.XML_IN, T.XML_OUT
    FROM TRANSACTION T
    WHERE T.ID_TRANSACTION = P_ID_TRANSACTION;

END SEARCH_XML;

When I call this procedure, the error message in VisualStudio2008 is: "An unsupported USERDEFINED USERDEFINED data type is encountered." Any idea how this works?

Answer 1

XMLType : getStringVal() getClobVal(), XML ( VARCHAR2 CLOB ). , XML 4000 (), , , getClobVal() :

PROCEDURE SEARCH_XML
(
    P_ID_TRANSACTION IN TRANSACTION.ID_TRANSACTION%TYPE,

    P_CURSOR OUT T_CURSOR
)
IS
BEGIN
  OPEN P_CURSOR FOR
    SELECT T.XML_IN.getClobVal() AS XML_IN,
           T.XML_OUT.getClobVal() AS XML_OUT
    FROM TRANSACTION T
    WHERE T.ID_TRANSACTION = P_ID_TRANSACTION;
END SEARCH_XML;