Avatar of kev1970
kev1970Flag for United Kingdom of Great Britain and Northern Ireland 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?
C#Oracle Database

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
ASKER
kev1970

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes