Solved

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

Posted on 2013-12-04
4
1,621 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 73

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 73

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

786 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