Home » Developer & Programmer » JDeveloper, Java & XML » help in extracting xml
help in extracting xml [message #426522] Fri, 16 October 2009 05:17 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Hello,

I have XMLType column with below information

id        |       XMLCOL
------------------------
100       |  <DOC_ALERT_ATTRIBUTES>
               <DOC_ATTRIBUTE>
                <DOC_NAME>Finace_Report.xls</DOC_NAME>
                <DOC_ID>1234</DOC_ID>
                <DOC_TYPE>Data_Sheet2</DOC_TYPE>
                <DOC_LOAD_DT>12 Sep 2009 2.30  PM</DOC_LOAD_DT>
               </DOC_ATTRIBUTE>
              <DOC_ATTRIBUTE>
               <DOC_NAME>Account_Report.xls</DOC_NAME>
               <DOC_ID>1235</DOC_ID>
               <DOC_TYPE>Data_Sheet1</DOC_TYPE>
               <DOC_LOAD_DT>01 Sep 2009 2.30 PM</DOC_LOAD_DT>
              </DOC_ATTRIBUTE>
               <DOC_ATTRIBUTE>
             <DOC_NAME>Marketing_Report.xls</DOC_NAME>
               <DOC_ID>1236</DOC_ID>
               <DOC_TYPE>Data_Sheet2</DOC_TYPE>
               <DOC_LOAD_DT>03 Sep 2009 2.30 PM</DOC_LOAD_DT>
             </DOC_ATTRIBUTE>
           </DOC_ALERT_ATTRIBUTES>



output required -

id     |       Value
------------------------
100    |Finace_Report.xls,1234,Data_Sheet2,12 Sep 2009 2.30  PM
100    |Account_Report.xls,1235,Data_Sheet1,01 Sep 2009 2.30 PM
100    |Marketing_Report.xls,1236,Data_Sheet2,03 Sep 2009 2.30 PM



I tried below one -
Select id,EXTRACT (Value,
                '/DOC_ALERT_ATTRIBUTES/DOC_ATTRIBUTE'
               ).getstringval () a
from table_a
where id = 100;


and its giving me the values in one row.

Is there a way to do it in sql with multiple rows?

Regards,
Naveen
Re: help in extracting xml [message #426525 is a reply to message #426522] Fri, 16 October 2009 05:46 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You will find an example in your previous topic at:
http://www.orafaq.com/forum/mv/msg/150498/424153/102589/#msg_424153

Regards
Michel
Previous Topic: Problem with XMLElement using cast&multiset null values
Next Topic: Parsing XML document in PL/SQL
Goto Forum:
  


Current Time: Fri Mar 29 01:55:21 CDT 2024