Home » Developer & Programmer » JDeveloper, Java & XML » Parse xml and fetch the values
Parse xml and fetch the values [message #285937] Thu, 06 December 2007 02:32 Go to next message
rkumudha
Messages: 10
Registered: November 2007
Location: India
Junior Member
Hi,

I'am new to the XML stuff's and i have a requirement which is to extract the values from a XMl.

We use Oracle version 9.2.0.1.0.

here is the sample XML,

<CONTRACT>
<CON_NUMBER>1</CON_NUMBER>
<PRI_DATE>30-NOV-07</PRI_DATE>
<END_DATE>20-NOV-08</END_DATE>
<START_DATE>30-NOV-07</START_DATE>
<PROD_CHARGE>0</TOT_PROD_CHARGE>
<ANNUAL_CHARGE>0</ANNUAL_CHARGE>
<PRODUCT>
<PRODUCT_NO>2</PRODUCT_NO>
<DATE_ADDED>30-NOV-07</DATE_ADDED>
<PRIC_DATE>30-NOV-07</PRIC_DATE>
<PRICE>20</PRICE>
<DISCOUNT>10</DISCOUNT>
<CON_NUMBER>1</CON_NUMBER>
<APPLIANCE>
<PRODUCT_NO>2</PRODUCT_NO>
<APP_NO>3</APP_NO>
<APP_NAME>HEATER</APP_NAME>
<PRODUCT_NO>2</PRODUCT_NO>
<APP_NO>2</APP_NO>
<APP_NAME>FAN</APP_NAME>
</APPLIANCE>
</PRODUCT>
<PRODUCT>
<PRODUCT_NO>1</PRODUCT_NO>
<DATE_ADDED>30-NOV-07</DATE_ADDED>
<PRIC_DATE>30-NOV-07</PRIC_DATE>
<PRICE>30</PRICE>
<DISCOUNT>40</DISCOUNT>
<CON_NUMBER>1</CON_NUMBER>
<APPLIANCE>
<PRODUCT_NO>1</PRODUCT_NO>
<APP_NO>1</APP_NO>
<APP_NAME>LIGHT</APP_NAME>
</APPLIANCE>
</PRODUCT>
</CONTRACT>

The Above xml has a parent 'CONTRACT' and two child 'PRODUCT', here we have two product for a contract.
my requirement is to extract the PRICE, DISCOUNT and the PRODUCT_NO of each product using PL/SQL.

anyone please help me in this regard.

Thanks,
Sathiya
Re: Parse xml and fetch the values [message #285984 is a reply to message #285937] Thu, 06 December 2007 04:21 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hi,

Okay, first thing, <PROD_CHARGE>0</TOT_PROD_CHARGE> is obviously
incorrect XML, so I corrected this first.

This is one way of extracting the information you require :
SQL> DECLARE
  2    x  XMLTYPE := XMLTYPE('<CONTRACT>
  3  <CON_NUMBER>1</CON_NUMBER>
  4  <PRI_DATE>30-NOV-07</PRI_DATE>
  5  <END_DATE>20-NOV-08</END_DATE>
  6  <START_DATE>30-NOV-07</START_DATE>
  7  <PROD_CHARGE>0</PROD_CHARGE>
  8  <ANNUAL_CHARGE>0</ANNUAL_CHARGE>
  9  <PRODUCT>
 10    <PRODUCT_NO>2</PRODUCT_NO>
 11    <DATE_ADDED>30-NOV-07</DATE_ADDED>
 12    <PRIC_DATE>30-NOV-07</PRIC_DATE>
 13    <PRICE>20</PRICE>
 14    <DISCOUNT>10</DISCOUNT>
 15    <CON_NUMBER>1</CON_NUMBER>
 16    <APPLIANCE>
 17      <PRODUCT_NO>2</PRODUCT_NO>
 18      <APP_NO>3</APP_NO>
 19      <APP_NAME>HEATER</APP_NAME>
 20      <PRODUCT_NO>2</PRODUCT_NO>
 21      <APP_NO>2</APP_NO>
 22      <APP_NAME>FAN</APP_NAME>
 23    </APPLIANCE>
 24  </PRODUCT>
 25  <PRODUCT>
 26    <PRODUCT_NO>1</PRODUCT_NO>
 27    <DATE_ADDED>30-NOV-07</DATE_ADDED>
 28    <PRIC_DATE>30-NOV-07</PRIC_DATE>
 29    <PRICE>30</PRICE>
 30    <DISCOUNT>40</DISCOUNT>
 31    <CON_NUMBER>1</CON_NUMBER>
 32    <APPLIANCE>
 33      <PRODUCT_NO>1</PRODUCT_NO>
 34      <APP_NO>1</APP_NO>
 35      <APP_NAME>LIGHT</APP_NAME>
 36    </APPLIANCE>
 37  </PRODUCT>
 38  </CONTRACT>');
 39  BEGIN
 40    FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '/PRODUCT/CON_NUMBER') con_number,
 41                      EXTRACTVALUE(VALUE(t), '/PRODUCT/PRICE') price,
 42                      EXTRACTVALUE(VALUE(t), '/PRODUCT/DISCOUNT') discount,
 43                      EXTRACTVALUE(VALUE(t), '/PRODUCT/PRODUCT_NO') product_no
 44                 FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/CONTRACT/PRODUCT'))) t )
 45    LOOP
 46      dbms_output.put_line('CONTRACT : ' || i.con_number ||
 47                           ': PRICE : ' || i.price ||
 48                           ': DISCOUNT : ' || i.discount ||
 49                           ': PRODUCT : ' || i.product_no);
 50    END LOOP;
 51  END;
 52  /
CONTRACT : 1: PRICE : 20: DISCOUNT : 10: PRODUCT : 2
CONTRACT : 1: PRICE : 30: DISCOUNT : 40: PRODUCT : 1

PL/SQL procedure successfully completed.


Regards
Re: Parse xml and fetch the values [message #286005 is a reply to message #285984] Thu, 06 December 2007 05:05 Go to previous messageGo to next message
rkumudha
Messages: 10
Registered: November 2007
Location: India
Junior Member

Hi,

Your response helped me a lot.Thanks a lot!!

I have just one clarification, the XML which you declared as a xmltype is kept in the databse as a CLOB variable.

How can i use this clob and convert it into XMLTYPE to extract the values or how to take this CLOB variable and use it.

thanks,
Sathiya
Re: Parse xml and fetch the values [message #286078 is a reply to message #286005] Thu, 06 December 2007 07:19 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Here's a small sample script:
col price format a20
CREATE TABLE yourtable( the_xml clob)
/

INSERT INTO yourtable VALUES ('<CONTRACT>
  <CON_NUMBER>1</CON_NUMBER>
  <PRI_DATE>30-NOV-07</PRI_DATE>
  <END_DATE>20-NOV-08</END_DATE>
  <START_DATE>30-NOV-07</START_DATE>
  <PROD_CHARGE>0</PROD_CHARGE>
  <ANNUAL_CHARGE>0</ANNUAL_CHARGE>
  <PRODUCT>
    <PRODUCT_NO>2</PRODUCT_NO>
    <DATE_ADDED>30-NOV-07</DATE_ADDED>
    <PRIC_DATE>30-NOV-07</PRIC_DATE>
    <PRICE>20</PRICE>
    <DISCOUNT>10</DISCOUNT>
    <CON_NUMBER>1</CON_NUMBER>
    <APPLIANCE>
      <PRODUCT_NO>2</PRODUCT_NO>
      <APP_NO>3</APP_NO>
      <APP_NAME>HEATER</APP_NAME>
      <PRODUCT_NO>2</PRODUCT_NO>
      <APP_NO>2</APP_NO>
      <APP_NAME>FAN</APP_NAME>
    </APPLIANCE>
  </PRODUCT>
  <PRODUCT>
    <PRODUCT_NO>1</PRODUCT_NO>
    <DATE_ADDED>30-NOV-07</DATE_ADDED>
    <PRIC_DATE>30-NOV-07</PRIC_DATE>
    <PRICE>30</PRICE>
    <DISCOUNT>40</DISCOUNT>
    <CON_NUMBER>1</CON_NUMBER>
    <APPLIANCE>
      <PRODUCT_NO>1</PRODUCT_NO>
      <APP_NO>1</APP_NO>
      <APP_NAME>LIGHT</APP_NAME>
    </APPLIANCE>
  </PRODUCT>
</CONTRACT>');

SELECT extractvalue(value(x),'/PRICE') "Price"
FROM  yourtable m
   ,  TABLE(XMLSequence(extract(xmltype(m.the_xml),'/CONTRACT/PRODUCT/PRICE'))) x
/
DROP TABLE yourtable
/
When I run it, I get:
SQL> @orafaq

Table created.


1 row created.


Price
--------------------
20
30


Table dropped.

SQL>
MHE
Re: Parse xml and fetch the values [message #286112 is a reply to message #286005] Thu, 06 December 2007 08:35 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hi.

You just use (as Maaher and I did in our examples), just wrap the CLOB in an XMLTYPE constructor, i.e.
DECLARE
  x  XMLTYPE := XMLTYPE(:clob_value);
..
Re: Parse xml and fetch the values [message #295594 is a reply to message #285937] Tue, 22 January 2008 18:48 Go to previous messageGo to next message
cakask
Messages: 11
Registered: January 2008
Junior Member
I was able to almost get my vsn working using what McHadder posted but something is wrong.

Background - I based my test on the example given, but with my xml in my table:
(Oracle 9.2.0.8.0) and testing in SQL Plus

DECLARE
BEGIN
FOR i IN ( SELECT extractvalue(VALUE(t), '/G-REC/Hdr/Dtl/TRAN-TYPE') TRAN,
extractvalue(VALUE(t), '/G-REC/Hdr/Dtl/VEND-ID') VEND,
extractvalue(VALUE(t), '/G-REC/Hdr/Dtl/NAME') VENDNM,
extractvalue(VALUE(t), '/G-REC/Hdr/Dtl/UNIQUE-KEY') UK
FROM TABLE (select xmlsequence(extract(XML_DATA,'/G-REC/Hdr/Dtl'))
from cak_xml_table) t
)
LOOP
dbms_output.put_line('TRAN : ' || i.TRAN ||
': VEND : ' || i.VEND ||
': NAME : ' || i.VENDNM ||
': UNIQUE-KEY : ' || i.UK);
END LOOP;
END;

The data looks like this - but there are actually 90 occurrence of the Detail:
<?xml version = '1.0'?>
<G-REC>
<Hdr>
<BATCH-UNIQUE-ID>A110</BATCH-UNIQUE-ID>
<ADD-COUNT>0</ADD-COUNT>
<MODIFY-COUNT>2</MODIFY-COUNT>
<DELETE-COUNT>0</DELETE-COUNT>
<REACTIVATE-COUNT>0</REACTIVATE-COUNT>
<TRANSACTION-DATE>20080103</TRANSACTION-DATE>
<ACTION-TIME>065844</ACTION-TIME>
<Dtl>
<TRAN-TYPE>M</TRAN-TYPE>
<VEND-ID>112</VEND-ID>
<NAME>ACME</NAME>
<UNIQUE-KEY>A110</UNIQUE-KEY>
</Dtl>
<Dtl>
<TRAN-TYPE>M</TRAN-TYPE>
<VEND-ID>F214F.</VEND-ID>
<NAME>MAIN STREET SUPPLIES</NAME>
<UNIQUE-KEY>A111</UNIQUE-KEY>
</Dtl>
</Hdr>
</G-RECS>


BUT the Output looks like 90 rows of this
So no data and the wrong results

TRAN : : VEND : : NAME : : UNIQUE-KEY :

So my questions:
1. can anyone see what is wrong?

2. what DOES XMLSEQUENCE doing? -
because if I just run that part it produces so odd list that pulls only a few of the columns but some strange looking output.

3. If I do the following command - I get all of the XML lines for VEND-ID but all strung together and it include the tags.

select extract( XML_DATA, '/G-RECS/Hdr/Dtl/VEND-ID' ) AS VEND
from cak_xml_table

<VEND-ID>112</VEND-ID>
<VEND-ID>F140C.</VEND-ID> --(cak - ...etc for the )

Do y'all have any suggestion on how to "segment" that? Beacuse if I could get each VEND_ID I'd have the Unique Key
I need to do the extractvalues like you've shown but with something like the following for each Dtl column:
'/G-REC/Hdr/Dtl[VEND-ID = "' || v_vend || '"]/NAME'??

TIA
Re: Parse xml and fetch the values [message #295598 is a reply to message #285937] Tue, 22 January 2008 19:54 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
<?xml version = '1.0'?>
<G-REC>
.
.
.
</G-RECS>

The XML is malformed.

Either
<?xml version = '1.0'?>
<G-RECS>
</G-RECS>

or

<?xml version = '1.0'?>
<G-REC>
</G-REC>

Here is a free clue.
Most modern browsers will successfully read XML or spew error when the contents is malformed.


Re: Parse xml and fetch the values [message #295814 is a reply to message #295598] Wed, 23 January 2008 09:47 Go to previous messageGo to next message
cakask
Messages: 11
Registered: January 2008
Junior Member
That was just a typo when I edited the XML after pasting it - the XML is correct, I edited it down because there was too much data and some of it might be considered private.

My questions still stand.

The XML was generated using dbms_xmlquery.getXml and the recipients have validated it.

<G-RECS>
<Hdr>
<BATCH-UNIQUE-ID>A110</BATCH-UNIQUE-ID>
<ADD-COUNT>0</ADD-COUNT>
<MODIFY-COUNT>2</MODIFY-COUNT>
<DELETE-COUNT>0</DELETE-COUNT>
<REACTIVATE-COUNT>0</REACTIVATE-COUNT>
<TRANSACTION-DATE>20080103</TRANSACTION-DATE>
<ACTION-TIME>065844</ACTION-TIME>
<Dtl>
<TRAN-TYPE>M</TRAN-TYPE>
<VEND-ID>112</VEND-ID>
<NAME>ACME</NAME>
<UNIQUE-KEY>A110</UNIQUE-KEY>
</Dtl>
<Dtl>
<TRAN-TYPE>M</TRAN-TYPE>
<VEND-ID>F214F.</VEND-ID>
<NAME>MAIN STREET SUPPLIES</NAME>
<UNIQUE-KEY>A111</UNIQUE-KEY>
</Dtl>
</Hdr>
</G-RECS>
Re: Parse xml and fetch the values [message #296133 is a reply to message #285937] Thu, 24 January 2008 15:39 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

Well, for a start, the XML tag G-REC has now become G-RECS, so the xpath would have to change to reflect this. Also, the xpath for the extractvalue functions is wrong... if you look at my previous posting, once you've extracted from the main XML, you are no longer at the "G-RECS" level, you have extracted all <Dtl> elements, so you need to modify the xpath to reflect this as well, i.e. note, this is not tested, but wil give you the idea :
...
BEGIN
FOR i IN ( 
SELECT 
  extractvalue(VALUE(t), '/Dtl/TRAN-TYPE') TRAN,
  extractvalue(VALUE(t), '/Dtl/VEND-ID') VEND,
  extractvalue(VALUE(t), '/Dtl/NAME') VENDNM,
  extractvalue(VALUE(t), '/Dtl/UNIQUE-KEY') UK
FROM TABLE (select xmlsequence(extract(XML_DATA,'/G-RECS/Hdr/Dtl'))
from cak_xml_table) t
)
LOOP
dbms_output.put_line('TRAN : ' || i.TRAN ||
': VEND : ' || i.VEND ||
': NAME : ' || i.VENDNM ||
': UNIQUE-KEY : ' || i.UK);
END LOOP;
END;

Regards
Re: Parse xml and fetch the values [message #296136 is a reply to message #296133] Thu, 24 January 2008 16:59 Go to previous messageGo to next message
cakask
Messages: 11
Registered: January 2008
Junior Member
To begin with - MCHADDER - Thank you very much!

Once I changed the path I got the fields!

Now to try to understand (my #2 above), since you provided an answer and seem to understand do you also the EXTRACT and EXTRACT VALUE ( I only understand the EXTRACTVALUE).
Do you by any chance know the answer to #2 ? shown below again.

Because to really be able to manipulate this type of XML data - it would really help if this was clear.

select xmlsequence(extract(XML_DATA,'/G-REC/Hdr/Dtl'))
from cak_xml_table


2. what DOES XMLSEQUENCE doing? -
because if I just run that part it produces so odd list that pulls only a few of the columns but some strange looking output.

Again, Thank You - I am sure your answer will help others also.

[Updated on: Thu, 24 January 2008 17:05]

Report message to a moderator

Re: Parse xml and fetch the values [message #296307 is a reply to message #285937] Fri, 25 January 2008 12:10 Go to previous message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

Glad you got it working....

check the Oracle docs for information on XMLSEQUENCE, or check out
http://www.oratechinfo.co.uk/sqlxml.html#xmlsequence.

Basically, it converts an XMLTYPE document into a VARRAY of XMLTYPE elements, that you can then EXTRACT or EXTRACTVALUE from.

Think of it as you end up with a collection of all <Dtl> elements, one per "row" in the collection, that you then get all values from .

Regards
Previous Topic: JDBC and Hung Update Operations
Next Topic: Query Generating XML output
Goto Forum:
  


Current Time: Fri Dec 09 09:40:20 CST 2016

Total time taken to generate the page: 0.05526 seconds