Home » Developer & Programmer » JDeveloper, Java & XML » help needed in xml (windows xp, oracle 10.2.0.1)
help needed in xml [message #456467] Tue, 18 May 2010 02:09 Go to next message
lvrr
Messages: 5
Registered: July 2008
Junior Member
Hi



i am new to xml . i have one table DEPT_XML_TAB


desc DEPT_XML_TAB;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE of SYS.XMLTYPE



select * from DEPT_XML_TAB;

SYS_NC_ROWINFO$
--------------------------------------------------------------------------------
<Department deptno="100">
<DeptName>Sports</DeptName>
<EmployeeList>
<Employee empno="200">
<Ename>John</Ename>
<Salary>33333</Salary>
</Employee>
<Employee empno="300">
<Ename>Jack</Ename>
<Salary>333444</Salary>
</Employee>
</EmployeeList>
</Department>

<Department deptno="200">
<DeptName>Sports</DeptName>
<EmployeeList>
<Employee empno="400">
<Ename>Marlin</Ename>
<Salary>20000</Salary>
</Employee>
</EmployeeList>
</Department>




now i need to select this xml values and parse , then i need to insert into another table ( take DEPT_XML_TAB1) in pl/sql




I hope i have given full information about my problem. Please let me know if yo are not clear and need further information.

Please help me out.. I have been working on this since 2 days. May be it's very easy for u But i am not able make it work.

Thanks in Advance!
Re: help needed in xml [message #456474 is a reply to message #456467] Tue, 18 May 2010 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I hope i have given full information about my problem.

No, you didn't give the structure of table DEPT_XML_TAB1.
There are many exemples in the "JDeveloper, Java & XML" forums did you read and try them?

Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Before Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Re: help needed in xml [message #456480 is a reply to message #456467] Tue, 18 May 2010 02:33 Go to previous messageGo to next message
_jum
Messages: 548
Registered: February 2008
Senior Member
Your XML needs a ROOT-element (here <XDATA/>)!
Start with this SELECT, read XMLTYPE and try to select (EXTRACT, EXTRACTVALUE) the data, :
SELECT XMLTYPE('
<XDATA>
  <Department deptno="100">
    <DeptName>Sports</DeptName>
    <EmployeeList>
      <Employee empno="200">
        <Ename>John</Ename>
        <Salary>33333</Salary>
      </Employee>
      <Employee empno="300">
        <Ename>Jack</Ename>
        <Salary>333444</Salary>
      </Employee>
    </EmployeeList>
  </Department>
  <Department deptno="200">
    <DeptName>Sports</DeptName>
    <EmployeeList>
      <Employee empno="400">
        <Ename>Marlin</Ename>
        <Salary>20000</Salary>
      </Employee>
    </EmployeeList>
  </Department>
</XDATA>') FROM dual;

[Updated on: Tue, 18 May 2010 02:34]

Report message to a moderator

Re: help needed in xml [message #456542 is a reply to message #456480] Tue, 18 May 2010 07:08 Go to previous messageGo to next message
lvrr
Messages: 5
Registered: July 2008
Junior Member
Hi

Thank u for your reply.

can u give small example regarding this ..

if there is any exemples in the "JDeveloper, Java & XML" forums then please highlight to me
Re: help needed in xml [message #456543 is a reply to message #456542] Tue, 18 May 2010 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, there are many examples in this forum, just search for the words _jum gave.
And you can provide you an example with your table and data if you provide them (read my previous post).

Regards
Michel
Re: help needed in xml [message #456561 is a reply to message #456543] Tue, 18 May 2010 08:40 Go to previous messageGo to next message
lvrr
Messages: 5
Registered: July 2008
Junior Member
Hi Michel ,

I am not expecting a sarcastic reply from u . But the problem is i am new to xmldb and i am unaware of so many aspects .


ok.. leave it



My table structure


CREATE TABLE mytable( the_xml clob)
/

INSERT INTO mytable 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>');

1 row created.

SQL> commit;
Commit complete.


Now i need to select this xml datas and parse ( please explain what is mean parse and why its needed) .finally insert to another table say mytable1

Re: help needed in xml [message #456565 is a reply to message #456561] Tue, 18 May 2010 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the TARGET table DDL?

Regards
Michel
Re: help needed in xml [message #456680 is a reply to message #456565] Wed, 19 May 2010 04:09 Go to previous messageGo to next message
lvrr
Messages: 5
Registered: July 2008
Junior Member
my target table structure

Create table mytable2 of xmltype;
Re: help needed in xml [message #456690 is a reply to message #456680] Wed, 19 May 2010 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE TABLE mytable( the_xml clob)
  2  /

Table created.

SQL> 
SQL> INSERT INTO mytable values ('<CONTRACT>
  2  <CON_NUMBER>1</CON_NUMBER>
  3  <PRI_DATE>30-NOV-07</PRI_DATE>
  4  <END_DATE>20-NOV-08</END_DATE>
  5  <START_DATE>30-NOV-07</START_DATE>
  6  <PROD_CHARGE>0</PROD_CHARGE>
  7  <ANNUAL_CHARGE>0</ANNUAL_CHARGE>
  8  <PRODUCT>
  9  <PRODUCT_NO>2</PRODUCT_NO>
 10  <DATE_ADDED>30-NOV-07</DATE_ADDED>
 11  <PRIC_DATE>30-NOV-07</PRIC_DATE>
 12  <PRICE>20</PRICE>
 13  <DISCOUNT>10</DISCOUNT>
 14  <CON_NUMBER>1</CON_NUMBER>
 15  <APPLIANCE>
 16  <PRODUCT_NO>2</PRODUCT_NO>
 17  <APP_NO>3</APP_NO>
 18  <APP_NAME>HEATER</APP_NAME>
 19  <PRODUCT_NO>2</PRODUCT_NO>
 20  <APP_NO>2</APP_NO>
 21  <APP_NAME>FAN</APP_NAME>
 22  </APPLIANCE>
 23  </PRODUCT>
 24  <PRODUCT>
 25  <PRODUCT_NO>1</PRODUCT_NO>
 26  <DATE_ADDED>30-NOV-07</DATE_ADDED>
 27  <PRIC_DATE>30-NOV-07</PRIC_DATE>
 28  <PRICE>30</PRICE>
 29  <DISCOUNT>40</DISCOUNT>
 30  <CON_NUMBER>1</CON_NUMBER>
 31  <APPLIANCE>
 32  <PRODUCT_NO>1</PRODUCT_NO>
 33  <APP_NO>1</APP_NO>
 34  <APP_NAME>LIGHT</APP_NAME>
 35  </APPLIANCE>
 36  </PRODUCT>
 37  </CONTRACT>');

1 row created.

SQL> Create table mytable2 of xmltype; 

Table created.

SQL> insert into mytable2 select xmltype(the_xml) from mytable;

1 row created.

Regards
Michel
Re: help needed in xml [message #456722 is a reply to message #456690] Wed, 19 May 2010 06:05 Go to previous messageGo to next message
lvrr
Messages: 5
Registered: July 2008
Junior Member
Hi

I need to select datas (PRODUCT_NO,PRIC_DATE,PRICE,APP_NO,APP_NAME,CON_NUMBER,PRI_DATE,END_DATE) from mytable

USING
{XMLTable /row PASSING object_value COLUMNS } command


explain this command and help me to come out from this mess
Re: help needed in xml [message #456760 is a reply to message #456722] Wed, 19 May 2010 07:51 Go to previous messageGo to next message
_jum
Messages: 548
Registered: February 2008
Senior Member
With the table definiton @michel gave, try:
SELECT extractvalue(column_value,'//PRODUCT/PRODUCT_NO') pno, 
       extractvalue(column_value,'//PRODUCT/DISCOUNT')   dis
  FROM mytable, TABLE(XMLSequence(extract(XMLTYPE(the_xml),'//PRODUCT')));

pno     dis
--------------
2	10
1	40

And necesseraly read the fine manuel to make progress...

[Updated on: Wed, 19 May 2010 07:52]

Report message to a moderator

Re: help needed in xml [message #456772 is a reply to message #456722] Wed, 19 May 2010 08:17 Go to previous message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
lvrr wrote on Wed, 19 May 2010 13:05
Hi

I need to select datas (PRODUCT_NO,PRIC_DATE,PRICE,APP_NO,APP_NAME,CON_NUMBER,PRI_DATE,END_DATE) from mytable

USING
{XMLTable /row PASSING object_value COLUMNS } command


explain this command and help me to come out from this mess

Why? What I posted fit the requirements you posted.

Regards
Michel

Previous Topic: select the text of one node
Next Topic: XML parsing
Goto Forum:
  


Current Time: Sun Jul 21 16:07:29 CDT 2019