How to retrieve data from xml code using oracle sql query.

How to retrieve data from xml code using oracle sql query.
AJITPADHYAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor Commented:
use EXTRACT or EXTACTVALUE with xpath


or XMLQUERY if you know FLWOR syntax
0
 
slightwv (䄆 Netminder) Commented:
I agree but wanted to add:

EXTRACT returns the 'raw' encoded XML values (ex/ Can't).
EXTRACTVALUE returns the decoded values  (ex/ Can't).

Sample data and expected results would help a lot.
0
 
AJITPADHYAuthor Commented:
please provide me step by step procedure & pdf documents for the entire process.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
slightwv (䄆 Netminder) Commented:
Please provide your requirements.  Before we can provide the steps we need to know what you want.

I asked for some sample data and expected results.

Otherwise, it's the docs:
EXTRACT:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions060.htm#SQLRF00640

EXTRACTVALUE:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions061.htm#SQLRF06173
0
 
AJITPADHYAuthor Commented:
retrieve objective id,group code from the attachment xml code using sql query.
xmlquery.txt
0
 
slightwv (䄆 Netminder) Commented:
Will you only ever have a single objectiveid and groupcode in the XML?

As long as the XML is in an Oracle XMLTYPE column try this:

select extract(col1,'//ObjectiveId/text()') objectiveid,
      extract(col1,'//GroupCode/text()') groupcode
from tab1;
0
 
sdstuberConnect With a Mentor Commented:



SELECT EXTRACTVALUE(VALUE(o), '//ObjectiveEORow/ObjectiveId'),
       EXTRACTVALUE(VALUE(o), '//ObjectiveEORow/GroupCode')
  FROM yourtable,
       XMLTABLE('//ObjectiveEORow'
                PASSING xmltype(yourxmlstring)
               ) o
0
 
sdstuberConnect With a Mentor Commented:
if "yourxmlstring"  is already an XMLTYPE

then you can reference that column directly rather than using the XMLTYPE constructor
otherwise the query would be the same


PASSING yourxmltypecolumn

instead of

PASSING xmltype(yourxmlstring)

0
 
slightwv (䄆 Netminder) Commented:
Looks like the objective ids repeat and there is potential for the groupcodes to repeat as well.

What are you wanting to do with the repeating values once you retrieve them?  Maybe loop though the values and insert into a table?

If so, we would need to know what values go with what in the table.

Does each id and code for every repeating ObjectiveEORow become it's own row?
0
 
AJITPADHYAuthor Commented:
Thanks
0
 
sdstuberCommented:
why the B?

You know that's a penalty, right?  What else did you want?
0
All Courses

From novice to tech pro — start learning today.