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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 767
  • Last Modified:

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

How to retrieve data from xml code using oracle sql query.
0
AJITPADHY
Asked:
AJITPADHY
  • 4
  • 4
  • 3
3 Solutions
 
sdstuberCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
sdstuberCommented:



SELECT EXTRACTVALUE(VALUE(o), '//ObjectiveEORow/ObjectiveId'),
       EXTRACTVALUE(VALUE(o), '//ObjectiveEORow/GroupCode')
  FROM yourtable,
       XMLTABLE('//ObjectiveEORow'
                PASSING xmltype(yourxmlstring)
               ) o
0
 
sdstuberCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now