Home » Developer & Programmer » JDeveloper, Java & XML » Insert xml into table in plsql! (Plsql)
Insert xml into table in plsql! [message #511849] Wed, 15 June 2011 06:42 Go to next message
dophuong_cs
Messages: 92
Registered: May 2011
Location: Viet Nam
Member
Hi all, i has problem with insert file .xml into table in plsql.
I has a tables follow:


create table EMP
(
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
id NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);


Now, i has a file .xml follow:

<?xml version="1.0" encoding="utf-8" ?>
<ROWDATA>

<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>12/17/1980</HIREDATE>
<SAL>800.00</SAL>
<COMM></COMM>
<DEPTNO>20</DEPTNO>
<ID>1</ID>
</ROW>
</ROWDATA>


How to insert data from file .xml insert into the table which is only of value from the card file .xml into the corresponding fields of table "EMP", not insert CLOB data types in table "EMP".
How do I insert the data into the table "EMP", please help me?
Are In oracle there functions and procedures which support file parsing .xml?

Report message to a moderator

Re: Insert xml into table in plsql! [message #511853 is a reply to message #511849] Wed, 15 June 2011 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There are many examples in our XML forum where I move this topic, please search in it; search for "extractvalue" for instance.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
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.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

[Updated on: Wed, 15 June 2011 06:54]

Report message to a moderator

Re: Insert xml into table in plsql! [message #511897 is a reply to message #511853] Wed, 15 June 2011 18:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
I am guessing that you have more than one row in your data, so I added another row and demonstrated two different methods below. The second method is a newer method.

-- contents of file c:\my_oracle_files\your_file.xml
<?xml version="1.0" encoding="utf-8" ?>
<ROWDATA>
 <ROW>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7902</MGR>
  <HIREDATE>12/17/1980</HIREDATE>
  <SAL>800</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7902</EMPNO>
  <ENAME>FORD</ENAME>
  <JOB>ANALYST</JOB>
  <MGR>7566</MGR>
  <HIREDATE>12/03/1981</HIREDATE>
  <SAL>3000</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
</ROWDATA>


-- older method:
SCOTT@orcl_11gR2> truncate table emp
  2  /

Table truncated.

SCOTT@orcl_11gR2> select * from emp
  2  /

no rows selected

SCOTT@orcl_11gR2> create or replace directory my_dir as 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_11gR2> insert into emp
  2    (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  3  select extractvalue (column_value, '/ROW/EMPNO'),
  4  	    extractvalue (column_value, '/ROW/ENAME'),
  5  	    extractvalue (column_value, '/ROW/JOB'),
  6  	    extractvalue (column_value, '/ROW/MGR'),
  7  	    to_date (extractvalue (column_value, '/ROW/HIREDATE'), 'mm/dd/yyyy'),
  8  	    extractvalue (column_value, '/ROW/SAL'),
  9  	    extractvalue (column_value, '/ROW/COMM'),
 10  	    extractvalue (column_value, '/ROW/DEPTNO')
 11  from   table
 12  	      (xmlsequence
 13  		(extract
 14  		  (xmltype
 15  		    (bfilename ('MY_DIR', 'your_file.xml'),
 16  		     nls_charset_id ('AL32UTF8')),
 17  		   '/ROWDATA/ROW')))
 18  /

2 rows created.

SCOTT@orcl_11gR2> select * from emp
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

2 rows selected.


-- newer method:
SCOTT@orcl_11gR2> truncate table emp
  2  /

Table truncated.

SCOTT@orcl_11gR2> select * from emp
  2  /

no rows selected

SCOTT@orcl_11gR2> create or replace directory my_dir as 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_11gR2> insert into emp
  2    (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  3  select empno,
  4  	    ename,
  5  	    job,
  6  	    mgr,
  7  	    to_date (hiredate, 'mm/dd/yyyy'),
  8  	    sal,
  9  	    comm,
 10  	    deptno
 11  from   xmltable
 12  	      ('/ROWDATA/ROW'
 13  	       passing xmltype (bfilename ('MY_DIR', 'your_file.xml'), nls_charset_id ('AL32UTF8'))
 14  	       columns
 15  		 "EMPNO"    number	  path '/ROW/EMPNO',
 16  		 "ENAME"    varchar2 (10) path '/ROW/ENAME',
 17  		 "JOB"	    varchar2 ( 9) path '/ROW/JOB',
 18  		 "MGR"	    number	  path '/ROW/MGR',
 19  		 "HIREDATE" varchar2 (10) path '/ROW/HIREDATE',
 20  		 "SAL"	    number	  path '/ROW/SAL',
 21  		 "COMM"     number	  path '/ROW/COMM',
 22  		 "DEPTNO"   number	  path '/ROW/DEPTNO')
 23  /

2 rows created.

SCOTT@orcl_11gR2> select * from emp
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

2 rows selected.

SCOTT@orcl_11gR2>

[Updated on: Wed, 15 June 2011 18:22]

Report message to a moderator

Re: Insert xml into table in plsql! [message #511905 is a reply to message #511897] Wed, 15 June 2011 21:07 Go to previous messageGo to next message
dophuong_cs
Messages: 92
Registered: May 2011
Location: Viet Nam
Member
but i used Oracle database 10g R2 not 11g r2.
now, how do it?http://www.upanh.com/uploaded/s=02754383b5da7c9bebeec4337511b3b9
file errors.jpg is file presentation errors code.
  • Attachment: errors.jpg
    (Size: 68.97KB, Downloaded 1845 times)

[Updated on: Wed, 15 June 2011 21:16]

Report message to a moderator

Re: Insert xml into table in plsql! [message #511906 is a reply to message #511897] Wed, 15 June 2011 22:15 Go to previous messageGo to next message
dophuong_cs
Messages: 92
Registered: May 2011
Location: Viet Nam
Member
what does tag <ROWDATA> and tag <ROW>?
How does used it?
if i has file .xml , what do you do insert with user scott_orcl11_R2?

<?xml version="1.0" encoding="UTF-8" ?>
<Datapreparation MCESTransform="@PersonalizationDataInterface.xsl">
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>12/17/1980</HIREDATE>
<SAL>800.00</SAL>
<COMM></COMM>
<DEPTNO>20</DEPTNO>
<ID>1</ID>
</Datapreparation>
<?xml version="1.0" encoding="UTF-8" ?>
<Datapreparation MCESTransform="@PersonalizationDataInterface.xsl">
<EMPNO>7902</EMPNO>
<ENAME>FORD</ENAME>
<JOB>ANALYST</JOB>
<MGR>7566</MGR>
<HIREDATE>12/03/1981</HIREDATE>
<SAL>3000.00</SAL>
<COMM></COMM>
<DEPTNO>20</DEPTNO>
<ID>2</ID>
</Datapreparation>

[Updated on: Wed, 15 June 2011 22:39]

Report message to a moderator

Re: Insert xml into table in plsql! [message #511910 is a reply to message #511906] Thu, 16 June 2011 00:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
It doesn't matter that you are using 10g, not 11gR2. Both methods that I posted also work in Oracle 10g.

In the example that I posted, the xml file was named your_file.xml and was in directory c:\my_oracle_files on my server. You need to substitute the appropriate file name and directory on your system. Note that the directory must be on your server, not your client. Similarly, my emp table is in the scott schema. You need to use whatever user/schema the emp table is in on your system.

Rowdata and row were in the sample data that you posted. If you have something different in your data, then substitute that. I have posted another example below, using the new data that you provided, with some modifications to make it valid xml. Your xml file must be a valid xml file.

You can find syntax, explanation, and examples in the searchable online documentation.

-- contents of file c:\my_oracle_files\your_file.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<TABLE>
<Datapreparation MCESTransform="@PersonalizationDataInterface.xsl">
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>12/17/1980</HIREDATE>
<SAL>800.00</SAL>
<COMM></COMM>
<DEPTNO>20</DEPTNO>
<ID>1</ID>
</Datapreparation>
<Datapreparation MCESTransform="@PersonalizationDataInterface.xsl">
<EMPNO>7902</EMPNO>
<ENAME>FORD</ENAME>
<JOB>ANALYST</JOB>
<MGR>7566</MGR>
<HIREDATE>12/03/1981</HIREDATE>
<SAL>3000.00</SAL>
<COMM></COMM>
<DEPTNO>20</DEPTNO>
<ID>2</ID>
</Datapreparation>
</TABLE>



SCOTT@orcl_11gR2> select * from emp
  2  /

no rows selected

SCOTT@orcl_11gR2> create or replace directory my_dir as 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_11gR2> insert into emp
  2    (empno, ename, job, mgr, hiredate, sal, comm, deptno, id)
  3  select extractvalue (column_value, '/Datapreparation/EMPNO'),
  4  	    extractvalue (column_value, '/Datapreparation/ENAME'),
  5  	    extractvalue (column_value, '/Datapreparation/JOB'),
  6  	    extractvalue (column_value, '/Datapreparation/MGR'),
  7  	    to_date (extractvalue (column_value, '/Datapreparation/HIREDATE'), 'mm/dd/yyyy'),
  8  	    extractvalue (column_value, '/Datapreparation/SAL'),
  9  	    extractvalue (column_value, '/Datapreparation/COMM'),
 10  	    extractvalue (column_value, '/Datapreparation/DEPTNO'),
 11  	    extractvalue (column_value, '/Datapreparation/ID')
 12  from   table
 13  	      (xmlsequence
 14  		(extract
 15  		  (xmltype
 16  		    (bfilename ('MY_DIR', 'your_file.xml'),
 17  		     nls_charset_id ('AL32UTF8')),
 18  		   '/TABLE/Datapreparation')))
 19  /

2 rows created.

SCOTT@orcl_11gR2> select * from emp
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO         ID
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20          1
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20          2

2 rows selected.

SCOTT@orcl_11gR2>




Report message to a moderator

Re: Insert xml into table in plsql! [message #512086 is a reply to message #511910] Thu, 16 June 2011 23:14 Go to previous messageGo to next message
dophuong_cs
Messages: 92
Registered: May 2011
Location: Viet Nam
Member

Insert Into Xml_Table
(Idcardnumbe
,Fullname1line1
,Fullname1line2
,Fullname1
,Fullname2
,Dateofbirth
,Sex
,Nation
,Placeofbirthline1
,Placeofbirthline2
,Permanentresidenceline1
,Permanentresidenceline2
,Distinguishingfeaturesline1
,Distinguishingfeaturesline2
,Fathername
,Mothername
,Dateofissue
,Imageportrait
,Imageleftindexfingerprint
,Imagerightindexfingerprint
,Imagesignature
,Imagestamp
,Imagepdf417
,Id_Chi_Tiet_Lo)
Select Extractvalue(Column_Value, '/Datapreparation/IDCardNumbe')
,Extractvalue(Column_Value, '/ Datapreparation/FullName1Line1')
,Extractvalue(Column_Value, '/Datapreparation/FullName1Line2')
,Extractvalue(Column_Value, '/Datapreparation/FullName1')
,Extractvalue(Column_Value, '/Datapreparation/FullName2')
,To_Date(Extractvalue(Column_Value, '/Datapreparation/DateOfBirth'), 'mm/dd/yyyy')
,Extractvalue(Column_Value, '/Datapreparation/Sex')
,Extractvalue(Column_Value, '/Datapreparation/Nation')
,Extractvalue(Column_Value, '/Datapreparation/PlaceOfBirthLine1')
,Extractvalue(Column_Value, '/Datapreparation/PlaceOfBirthLine2')
,Extractvalue(Column_Value, '/Datapreparation/PermanentResidenceLine1')
,Extractvalue(Column_Value, '/Datapreparation/PermanentResidenceLine2')
,Extractvalue(Column_Value, '/Datapreparation/DistinguishingFeaturesLine1')
,Extractvalue(Column_Value, '/Datapreparation/DistinguishingFeaturesLine2')
,Extractvalue(Column_Value, '/Datapreparation/FatherName')
,Extractvalue(Column_Value, '/Datapreparation/MotherName')
,To_Date(Extractvalue(Column_Value, '/Datapreparation/DateOfIssue'), 'mm/dd/yyyy')
,Extractvalue(Column_Value, '/Datapreparation/ImagePortrait')
,Extractvalue(Column_Value, '/Datapreparation/ImageLeftIndexFingerprint')
,Extractvalue(Column_Value, '/Datapreparation/ImageRightIndexFingerprint')
,Extractvalue(Column_Value, '/Datapreparation/ImageSignature')
,Extractvalue(Column_Value, '/Datapreparation/ImageSignature')
,Extractvalue(Column_Value, '/Datapreparation/Imagestamp')
,Extractvalue(Column_Value, '/Datapreparation/ID_CHI_TIET_LO')
From Table(
Xmlsequence(
Extract(
Xmltype(
Bfilename('XML', 'xml;test.xml'),
Nls_Charset_Id('AL32UTF8')),
'/TABLE/Datapreparation')));


i has errors follow:
please help me, i very need.
Thanks!

[Updated on: Thu, 16 June 2011 23:19]

Report message to a moderator

Re: Insert xml into table in plsql! [message #512088 is a reply to message #512086] Thu, 16 June 2011 23:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
What error are you getting? You need to make sure that you have valid xml data in your xml file. As I said before, I made some changes to the xml that you posted to make it valid. I added the TABLE tags and removed the extra line that said:

<?xml version="1.0" encoding="UTF-8" ?>

If your xml data in your xml file is not properly formatted, then you need to fix it before trying to access it.

Report message to a moderator

Re: Insert xml into table in plsql! [message #512090 is a reply to message #512086] Thu, 16 June 2011 23:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 15 June 2011 13:52
There are many examples in our XML forum where I move this topic, please search in it; search for "extractvalue" for instance.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
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.
Also always post your Oracle version, with 4 decimals.

Regards
Michel


Use SQL*Plus and copy and paste your session, the WHOLE session.

Please help us to help you, we very need.

[Updated on: Thu, 16 June 2011 23:31]

Report message to a moderator

Re: Insert xml into table in plsql! [message #512092 is a reply to message #512088] Thu, 16 June 2011 23:33 Go to previous messageGo to next message
dophuong_cs
Messages: 92
Registered: May 2011
Location: Viet Nam
Member
file .xml
erorrs follow:
ORA-30.11: XML parsing failed
ORA-19202: Erros occurred in XML processing
LPX -00283: document encoding is UTF-16-based but default input encoding is not
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 296
ORA-06512: at line 1
  • Attachment: xml;test.txt
    (Size: 5.02KB, Downloaded 2133 times)

[Updated on: Thu, 16 June 2011 23:38]

Report message to a moderator

Re: Insert xml into table in plsql! [message #512093 is a reply to message #512092] Thu, 16 June 2011 23:40 Go to previous message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Some people cannot download files, due to security restrictions, fear of viruses and so forth, so it is better to post everything inline. I have copied and posted the xml file from your attachment below. You need to match the column names in the query with the tags in the xml and it is case sensitive. So, for example, if your tags are in mixed case like "<Table>" then you need to use "Table" in your code, not TABLE.

<?xml version="1.0" encoding="UTF-8" ?>
<Table>
<Datapreparation MCESTransform="@PersonalizationDataInterface.xsl">
 <IDCardNumber>121011010002</IDCardNumber>
 <FullName1Line1>Nguyễn Thị Thu</FullName1Line1>
 <FullName1Line2></FullName1Line2>
 <FullName2></FullName2>
 <FullName2></FullName2>
 <DateOfBirth>12 Tháng 05 Năm 1986</DateOfBirth>
<Sex>N?</Sex>
 <Nation>Kinh</Nation>
 <PlaceOfBirthLine1>Tp Hà Nội, Q. Ba Đình, P. Phúc Xá</PlaceOfBirthLine1>
 <PlaceOfBirthLine2></PlaceOfBirthLine2>
 <PermanentResidenceLine1>Tp Hà Nội, Q. Ba Đình, P. Phúc Xá</PermanentResidenceLine1>
 <PermanentResidenceLine2></PermanentResidenceLine2>
 <DistinguishingFeaturesLine1>Nốt ruồi c.4 cm </DistinguishingFeaturesLine1>
 <DistinguishingFeaturesLine2></DistinguishingFeaturesLine2>
 <FatherName>Nguyễn Hữu Vang</FatherName>
 <MotherName>Nguyễn Thị Thơm</MotherName>
 <DateOfIssue>23 Tháng 05 Năm 2011</DateOfIssue>
 <ImagePortrait>BB2Portrait.jpg</ImagePortrait>
 <ImageLeftIndexFingerprint>BB2LF.jpg</ImageLeftIndexFingerprint>
 <ImageRightIndexFingerprint>BB2RF.jpg</ImageRightIndexFingerprint>
 <ImageSignature>BB2Signature.jpg</ImageSignature>
 <ImageStamp>BB2Stamp.gif</ImageStamp>
 <ImagePDF417>BB2PDF417.tiff</ImagePDF417>
 <ID_CHI_TIET_LO>102</ID_CHI_TIET_LO>
 </Datapreparation>
<Datapreparation MCESTransform="@PersonalizationDataInterface.xsl">
 <IDCardNumber>1210110101002</IDCardNumber>
 <FullName1Line1>Nguyễn Thị Thu</FullName1Line1>
 <FullName1Line2></FullName1Line2>
 <FullName2></FullName2>
 <FullName2></FullName2>
 <DateOfBirth>12 Tháng 05 Năm 1986</DateOfBirth>
<Sex>N?</Sex>
 <Nation>Kinh</Nation>
 <PlaceOfBirthLine1>Tp Hà Nội, Q. Ba Đình, P. Phúc Xá</PlaceOfBirthLine1>
 <PlaceOfBirthLine2></PlaceOfBirthLine2>
 <PermanentResidenceLine1>Tp Hà Nội, Q. Ba Đình, P. Phúc Xá</PermanentResidenceLine1>
 <PermanentResidenceLine2></PermanentResidenceLine2>
 <DistinguishingFeaturesLine1>Nốt ruồi c.4 cm </DistinguishingFeaturesLine1>
 <DistinguishingFeaturesLine2></DistinguishingFeaturesLine2>
 <FatherName>Nguyễn Hữu Vang</FatherName>
 <MotherName>Nguyễn Thị Thơm</MotherName>
 <DateOfIssue>23 Tháng 05 Năm 2011</DateOfIssue>
 <ImagePortrait>BB2Portrait.jpg</ImagePortrait>
 <ImageLeftIndexFingerprint>BB2LF.jpg</ImageLeftIndexFingerprint>
 <ImageRightIndexFingerprint>BB2RF.jpg</ImageRightIndexFingerprint>
 <ImageSignature>BB2Signature.jpg</ImageSignature>
 <ImageStamp>BB2Stamp.gif</ImageStamp>
 <ImagePDF417>BB2PDF417.tiff</ImagePDF417>
 <ID_CHI_TIET_LO> 102</ID_CHI_TIET_LO>
 </Datapreparation>
 </Table>

Report message to a moderator

Previous Topic: easy and urgent task
Next Topic: xml parsing fail
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Feb 03 17:34:16 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.