Solved

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

Posted on 2011-02-23
11
761 Views
Last Modified: 2013-12-25
How to retrieve data from xml code using oracle sql query.
0
Comment
Question by:AJITPADHY
  • 4
  • 4
  • 3
11 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34962521
use EXTRACT or EXTACTVALUE with xpath


or XMLQUERY if you know FLWOR syntax
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34962563
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
 

Author Comment

by:AJITPADHY
ID: 34962867
please provide me step by step procedure & pdf documents for the entire process.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34962900
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
 

Author Comment

by:AJITPADHY
ID: 34963225
retrieve objective id,group code from the attachment xml code using sql query.
xmlquery.txt
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34963416
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 34963418



SELECT EXTRACTVALUE(VALUE(o), '//ObjectiveEORow/ObjectiveId'),
       EXTRACTVALUE(VALUE(o), '//ObjectiveEORow/GroupCode')
  FROM yourtable,
       XMLTABLE('//ObjectiveEORow'
                PASSING xmltype(yourxmlstring)
               ) o
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 34963447
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34963475
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
 

Author Closing Comment

by:AJITPADHY
ID: 35125651
Thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35127160
why the B?

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

679 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