asked on

How do I return XMLTYPE data from Oracle 11g R2 via .NET 2008?

Im working on a POC for the Oracle XMLTYPE data type but ExecuteReader is erroring with: Unsupported Oracle data type USERDEFINED encountered.
Im trying to get 1 row of (basic) test data using:
      "SELECT XMLTest FROM Test"
from a table which was created by:
      CREATE TABLE TEST
      (
       ID NUMBER,
       TEXT CHAR(3 BYTE),
       XMLTEST SYS.XMLTYPE
      );
      CREATE INDEX TEST_IDX1 ON TEST
      (ID);

The INSERT statement for the data Im trying to get was:
      INSERT INTO TEST (ID, TEXT, XMLTEST)
      VALUES (1, 'na', '<TEST>1 2 3 4 5</TEST>');
      COMMIT;

Im using the native System.Data.OracleClient namespace, .NET 2008, to access Oracle 11g R2.

Im expecting to get back "<TEST>1 2 3 4 5</TEST>". If I request the TEXT field then I correctly get back "na".

Can this be done? If so, how?
In other words, using .NET 2008, how do I get data from a XMLTYPE field?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

Don't mean to hog any space, but I wanted to thank sdstuber for showing me something new. If the query is submitted as -
select xmltest.getstringval() from test, it will fail with an invalid identifier error. To circumvent this we always had to include an alias like -
select t.xmltest.getstringval() from test t - to get it to work
Now I see that using the parentheses works fine.
Learn something new everyday :-)
Thanks.

kev1970

ASKER

Perfect! However, I had to change it slightly because I was trying to get the value of xmltest therefore:
select (xmltest).getclobval() xmltest from test

Sean Stuber

aliasing the method results doesn't change anything with respect to the value returned