Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-12-04
4
Medium Priority
?
1,847 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
  • 2
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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ā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Suggested Courses

879 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