OraFAQ Forum: JDeveloper, Java & XML » Parse xml and fetch the values

Home » Developer & Programmer » JDeveloper, Java & XML » Parse xml and fetch the values

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Parse xml and fetch the values [message #285937]

Thu, 06 December 2007 02:32

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

Report message to a moderator

Re: Parse xml and fetch the values [message #285984 is a reply to message #285937]

Thu, 06 December 2007 04:21

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

Report message to a moderator

Re: Parse xml and fetch the values [message #286005 is a reply to message #285984]

Thu, 06 December 2007 05:05

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

Report message to a moderator

Re: Parse xml and fetch the values [message #286078 is a reply to message #286005]

Thu, 06 December 2007 07:19

Maaher
Messages: 7065
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

Report message to a moderator

Re: Parse xml and fetch the values [message #286112 is a reply to message #286005]

Thu, 06 December 2007 08:35

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);
..

Report message to a moderator

Re: Parse xml and fetch the values [message #295594 is a reply to message #285937]

Tue, 22 January 2008 18:48

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

Report message to a moderator

Re: Parse xml and fetch the values [message #295598 is a reply to message #285937]

Tue, 22 January 2008 19:54

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Parse xml and fetch the values [message #295814 is a reply to message #295598]

Wed, 23 January 2008 09:47

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>

Report message to a moderator

Re: Parse xml and fetch the values [message #296133 is a reply to message #285937]

Thu, 24 January 2008 15:39

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

Report message to a moderator

Re: Parse xml and fetch the values [message #296136 is a reply to message #296133]

Thu, 24 January 2008 16:59

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

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 00:37:14 CST 2024