Solved

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

Posted on 2013-12-04
4
1,740 Views
Last Modified: 2013-12-04
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?
0
Comment
Question by:kev1970
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39695416
select (xmltest).getstringval() from test  

which will return varchar2 type data (i.e. text)
and, if you put a very large xml document into xmltest then

select (xmltest).getclobval() from test

which will return a clob (also text, but bigger capacity)
0
 
LVL 32

Expert Comment

by:awking00
ID: 39695854
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.
0
 

Author Closing Comment

by:kev1970
ID: 39695918
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39695932
aliasing the method results doesn't change anything with respect to the value returned
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question