Retrieving specific xml tags from clob type oracle table column(containing XML) in C# gridview

Basically its a search criteria page that might return n records from oracle in a gridview.
Untill now, I was fetching only individual varchar columns from oracle into C# gridview.But, the table contains a clob type column also, with xml type data in it.So basically each row in the table has xml type data in a specific column.Now, as per enhancement, I need to specifically pick up few feilds, that would appear as additional columns on the gridview.The oracle stored procedure that i have in hand simply returns select query as a cursor to C#.But, i need a workable approach as a whole on parsing of the xml (may it be on .net or oracle) , returning specific tags from the clob along with other columns from the table and finally displaying them on gridview.Overall, We are talking about retrieving around 5-6 normal varchar type columns and couple of specific tags that are incuded in each clob xml fields in each row.

I need a complete working example\code.
Please help ASAP.
DevildibAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
We normally don't write code for people.  This is a Q&A site.  If you need complete code you should look into hiring a consultant.

That said:
If you want to extract it at the database level, I would suggest you look at XMLTABLE.  It will treat the XML as a table object and let you extract specific fields.

The online docs talk about this:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions253.htm#SQLRF06232

There are examples in the docs and all over the Internet.

If you want to do it at the C# side, you should be able to bind the XML directly.

There are examples of this all over the Internet as well:
http://www.codeproject.com/Questions/385556/How-to-get-xml-data-into-grid-view

If you only want a few specific nodes, maybe LINQ.

A lot depends on your specific requirements.

For example:  If the XML is 1 Gig and you only need 4 values, I would extract them at the database layer.  This way you don't have to bring all the data across the wire.

Also, the database server tends to have more horsepower to process data than a web server.

If you have a specific question, aside from asking us to write it all for you, please ask.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DevildibAuthor Commented:
My specific requirement:
As i said, its a search criteria based weboage that needs to disolay data in gridview.we are going to limit a search to max 5000 records.the page will have 5 to 6 normal columns shoung in gridview aling with two other columns that would come from the clob type column from oracle table, which contains xml.whats the best approach for parsing xml in this scenario.parsing on db side or parsing on dot net side.if its db, then can you kindly provide an xmltable example as you said, to retrieve normal columns along with the atleast two specific xml tags from the clin type column?
0
DevildibAuthor Commented:
My specific requirement:,(updated)
As i said, its a search criteria based webpage that needs to display data in gridview.we are going to limit a search to max 5000 records.the page will have 5 to 6 normal columns showing in gridview aling with two other columns that would come from the clob type column from oracle table, which contains xml.whats the best approach for parsing xml in this scenario.parsing on db side or parsing on dot net side.if its db, then can you kindly provide an xmltable example as you said, to retrieve normal columns along with the atleast two specific xml tags from the clob type column?
0
slightwv (䄆 Netminder) Commented:
Depends on how large the XML in the CLOB is.

I'm a database guy so I tend to do everything in the database.  That may not always be the best approach.  I would try two or three methods and pick the one that works the best for me.

There is an example of XMLTABLE in the documentation link I provided.

For anything more, please provide some sample data and expected results.

Why limit a search?  I have a search based on stored XML and like Google and many other search engines, I paginate.  I do this at the database layer so I only pull what I need to display back to the web page.

All I need to do this is the search criteria, start page and the page size.  The SQL limits what I bring back.
0
slightwv (䄆 Netminder) Commented:
I would gladly post an example of using XMLTABLE.

However, to make it different than the example in the online documentation, I would need sample data and expected results.

If you can provide that, I can probably provide a working example.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.