Home » Developer & Programmer » JDeveloper, Java & XML » xml data in columns (11g)
xml data in columns [message #615075] Fri, 30 May 2014 00:32 Go to next message
usmanelahi
Messages: 54
Registered: July 2007
Location: Karachi
Member
hello gurus

i have a xml data i want this data in separate columns i make the query but it returns in rows please help me
the first column is header and the second column is its value

  • Attachment: testxml.sql
    (Size: 1.29KB, Downloaded 1704 times)
Re: xml data in columns [message #615078 is a reply to message #615075] Fri, 30 May 2014 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
i make the query but it returns in rows


Post your query.
And post the result you want for the xml you gave as this is not clear.

Re: xml data in columns [message #615156 is a reply to message #615075] Fri, 30 May 2014 13:46 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl12c> SELECT *
  2  FROM   XMLTABLE
  3  	      ('/document/page/row'
  4  	       PASSING XMLPARSE (DOCUMENT
  5  ('<?xml version="1.0" encoding="UTF-8"?>
  6  <document>
  7    <page index="0">
  8  	 <row>
  9  	   <column>?????CONSUMER PROTECTION DEPARTMENT</column>
 10  	 </row>
 11  	 <row>
 12  	   <column>Consumer Credit Information Report</column>
 13  	 </row>
 14  	 <row>
 15  	   <column>Report Ref No.</column>
 16  	   <column>CPD/PU-101/06-01-2014 11:01:54-17134733</column>
 17  	 </row>
 18  	 <row>
 19  	   <column>Report Date:</column>
 20  	   <column>06/01/2014</column>
 21  	 </row>
 22  	 <row>
 23  	   <column>Consumer Profile</column>
 24  	 </row>
 25  	 <row>
 26  	   <column>Mr.</column>
 27  	   <column>ADEEL  MAQBOOL</column>
 28  	 </row>
 29  	 <row>
 30  	   <column>Father/Husband Name:</column>
 31  	   <column>MAQBOOL AHMED</column>
 32  	   <column>Male</column>
 33  	   <column>Female</column>
 34  	   <column>Date Of Birth:</column>
 35  	   <column>15</column>
 36  	   <column>12</column>
 37  	   <column>1980</column>
 38  	 </row>
 39  	 <row>
 40  	   <column>Employed:</column>
 41  	   <column>Self-employeed Businessman / Professional:</column>
 42  	   <column>Others:</column>
 43  	   <column>Name amp; address of the Employer/Business:</column>
 44  	 </row>
 45    </page>
 46  </document>'))
 47  	       COLUMNS
 48  		 column1 VARCHAR2(36) PATH 'column[1]',
 49  		 column2 VARCHAR2(39) PATH 'column[2]',
 50  		 column3 VARCHAR2( 8) PATH 'column[3]',
 51  		 column4 VARCHAR2(43) PATH 'column[4]',
 52  		 column5 VARCHAR2(14) PATH 'column[5]',
 53  		 column6 VARCHAR2( 8) PATH 'column[6]',
 54  		 column7 VARCHAR2( 8) PATH 'column[7]',
 55  		 column8 VARCHAR2( 8) PATH 'column[8]')
 56  /

COLUMN1                                  COLUMN2                                            COLUMN3  COLUMN4                                     COLUMN5        COLUMN6  COLUMN7  COLUMN8
---------------------------------------- -------------------------------------------------- -------- ------------------------------------------- -------------- -------- -------- --------
?????CONSUMER PROTECTION DEPARTMENT
Consumer Credit Information Report
Report Ref No.                           CPD/PU-101/06-01-2014 11:01:54-17134733
Report Date:                             06/01/2014
Consumer Profile
Mr.                                      ADEEL  MAQBOOL
Father/Husband Name:                     MAQBOOL AHMED                                      Male     Female                                      Date Of Birth: 15       12       1980
Employed:                                Self-employeed Businessman / Profession            Others:  Name amp; address of the Employer/Business:

8 rows selected.

Previous Topic: Class not found in class in OAF
Next Topic: How to format output of query for coulmn type XMLTYPE
Goto Forum:
  


Current Time: Thu Mar 28 01:16:59 CDT 2024