OraFAQ Forum: JDeveloper, Java & XML » Transform XML to relational data.....

Home » Developer & Programmer » JDeveloper, Java & XML » Transform XML to relational data.....

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

Transform XML to relational data..... [message #149225]

Wed, 30 November 2005 11:53

peddi_praveen
Messages: 2
Registered: November 2005

Junior Member

Guys,
Pl help me in below issue....
my program expects "xml data" as input param. (xmltype param)
xml structure looks like this....
'<?xml version = "1.0"?>
<ROWSET>
<ROW num="1">
<COLUMNNAME>COL1</COLUMNNAME>
<DATATYPE>number</DATATYPE>
<LENGTH>10</LENGTH>
<PRECISION></PRECISION>
<SCALE></SCALE>
</ROW>
<ROW num="2">
<COLUMNNAME>COL2</COLUMNNAME>
<DATATYPE>number</DATATYPE>
<LENGTH>10</LENGTH>
<PRECISION>6</PRECISION>
<SCALE>4</SCALE>
</ROW>
</ROWSET>'

i need to convert this into relational structure as
columnname, datatype, length,precision, scale
col1, number, 10, ,
col2, number, 10, 6, 4

Q1: how do i convert into relational structure....
q2: i need to store this data in a table.

seeking ur help,
praveen

Report message to a moderator

Re: Transform XML to relational data..... [message #149737 is a reply to message #149225]

Sat, 03 December 2005 14:27

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Hello there.

Here's one way, using the XPath EXTRACT / EXTRACTVALUE mechanisms
available from 9i :

SQL> DECLARE
  2    x  XMLTYPE := XMLTYPE('<?xml version = "1.0"?>
  3  <ROWSET>
  4  <ROW num="1">
  5  <COLUMNNAME>COL1</COLUMNNAME>
  6  <DATATYPE>number</DATATYPE>
  7  <LENGTH>10</LENGTH>
  8  <PRECISION></PRECISION>
  9  <SCALE></SCALE>
 10  </ROW>
 11  <ROW num="2">
 12  <COLUMNNAME>COL2</COLUMNNAME>
 13  <DATATYPE>number</DATATYPE>
 14  <LENGTH>10</LENGTH>
 15  <PRECISION>6</PRECISION>
 16  <SCALE>4</SCALE>
 17  </ROW>
 18  </ROWSET>');
 19  BEGIN
 20    FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '/ROW/COLUMNNAME') column_name,
 21                      EXTRACTVALUE(VALUE(t), '/ROW/DATATYPE') datatype,
 22                      EXTRACTVALUE(VALUE(t), '/ROW/LENGTH') length,
 23                      EXTRACTVALUE(VALUE(t), '/ROW/PRECISION') precision,
 24                      EXTRACTVALUE(VALUE(t), '/ROW/SCALE') scale
 25                 FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/ROWSET/ROW'))) t )
 26    LOOP
 27      dbms_output.put_line(i.column_name || ' ' || i.datatype || ' ' || i.length || ' ' || 
 28                           i.precision || ' ' || i.scale);
 29    END LOOP;
 30  END;
 31  /
COL1 number 10
COL2 number 10 6 4

PL/SQL procedure successfully completed.

Regards

Report message to a moderator

Previous Topic:	Oracle 10g JDeveloper
Next Topic:	namespace prefix "xsi" is not declared

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 11:25:55 CDT 2024