pl/sql操作xml

SQL code
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as SYS
SQL> 
SQL> --1.使用具有dba权限用户如sys创建一个目录,如：
SQL> CREATE OR REPLACE DIRECTORY TESTDIR AS 'D:\temp\';
Directory created
SQL> --2.将读写访问权限赋给需要操作文件的用户,如test用户:
SQL> GRANT READ,WRITE ON DIRECTORY TESTDIR TO test;
Grant succeeded
SQL> 
SQL code
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as test
SQL> 
SQL> --3.登录test用户，在其下创建一个函数：
SQL> CREATE OR REPLACE FUNCTION getCompanyValue(file_path VARCHAR2, company_code VARCHAR2)
2 RETURN VARCHAR2 IS
3 --//XML解析器
4 xmlPar XMLPARSER.parser := XMLPARSER.NEWPARSER;
5 --//DOM文档对象
6 doc xmldom.DOMDocument;
7 len INTEGER;
8 personNodes xmldom.DOMNodeList;
9 chilNodes xmldom.DOMNodeList;
10 tempNode xmldom.DOMNode;
11 tempArrMap xmldom.DOMNamedNodeMap;
12 --================================
13 --以下变量用于获取XML节点的值
14 pid VARCHAR2(100);
15 companyValue VARCHAR2(100) := '';
16 tmp INTEGER;
17 --================================
18 BEGIN
19 xmlPar := xmlparser.newParser;
20 xmlparser.parse(xmlPar, file_path);
21 doc := xmlparser.getDocument(xmlPar);
22 -- 释放解析器实例
23 xmlparser.freeParser(xmlPar);
24 -- 获取所有PERSON元素
25 personNodes := xmldom.getElementsByTagName(doc, 'Company');
26 len := xmldom.getLength(personNodes);
27 --遍历所有PERSON元素
28 FOR i IN 0 .. len - 1 LOOP
29 --获取第i个PERSON
30 tempNode := xmldom.item(personNodes, i);
31 --所有属性
32 tempArrMap := xmldom.getAttributes(tempNode);
33 --获取PERSONID的值
34 pid := xmldom.getNodeValue(xmldom.getNamedItem(tempArrMap, 'CODE'));
35 IF pid = company_code THEN
36 --获取子元素的值
37 chilNodes := xmldom.getChildNodes(tempNode);
38 tmp := xmldom.GETLENGTH(chilNodes);
39 companyValue := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 0)));
40 EXIT;
41 END IF;
42 END LOOP;
43 -- 释放文档对象
44 xmldom.freeDocument(doc);
45 RETURN companyValue;
46 EXCEPTION
47 WHEN OTHERS THEN
48 DBMS_output.PUT_LINE(SQLERRM);
49 END getCompanyValue;
50 /
Function created
SQL> --4.将company.xml文件复制到数据服务器的D:\temp\目录下
SQL> --5.测试该函数
SQL> var companyValue varchar2(100);
SQL> exec :companyValue := getCompanyValue('TESTDIR\company.xml','abc');
PL/SQL procedure successfully completed
companyValue
---------
12345678944555
SQL> print companyValue
companyValue
---------
12345678944555
SQL> 
附：company.xml文件
XML code



12345678944555


1011121314156156


注意：上面是10g的演示，如果是oracle9i，则是设置utl_file_dir参数，重启数据库使参数生效，如
alter system set utl_file_dir='D:\temp' scope=spfile;
调用：
SQL>exec :companyValue := getCompanyValue('D:\temp\company.xml','abc');

 

 

 

 

 

 

 

Hi,

I've already gone through few threads in forums itself, but for my requirement nothing comes closer, I am posting my query. I've one xml file like this
<?xml version="1.0"?>
<ACCOUNT_HEADER_ACK>
<HEADER>
<STATUS_CODE>100</STATUS_CODE>
<STATUS_REMARKS>check</STATUS_REMARKS>
</HEADER>
<DETAILS> 
<DETAIL> 
<SEGMENT_NUMBER>2</SEGMENT_NUMBER>
<REMARKS>rp polytechnic</REMARKS>
</DETAIL>
<DETAIL> 
<SEGMENT_NUMBER>3</SEGMENT_NUMBER>
<REMARKS>rp polytechnic administration</REMARKS>
</DETAIL>
<DETAIL> 
<SEGMENT_NUMBER>4</SEGMENT_NUMBER>
<REMARKS>rp polytechnic finance</REMARKS>
</DETAIL>
<DETAIL> 
<SEGMENT_NUMBER>5</SEGMENT_NUMBER>
<REMARKS>rp polytechnic logistics</REMARKS>
</DETAIL>
</DETAILS>
<HEADER>
<STATUS_CODE>500</STATUS_CODE>
<STATUS_REMARKS>process exception</STATUS_REMARKS>
</HEADER>
<DETAILS> 
<DETAIL> 
<SEGMENT_NUMBER>20</SEGMENT_NUMBER>
<REMARKS> base polytechnic</REMARKS>
</DETAIL>
<DETAIL> 
<SEGMENT_NUMBER>30</SEGMENT_NUMBER>
</DETAIL>
<DETAIL> 
<SEGMENT_NUMBER>40</SEGMENT_NUMBER>
<REMARKS> base polytechnic finance</REMARKS>
</DETAIL>
<DETAIL> 
<SEGMENT_NUMBER>50</SEGMENT_NUMBER>
<REMARKS> base polytechnic logistics</REMARKS>
</DETAIL>
</DETAILS>
</ACCOUNT_HEADER_ACK>

Here the xml structure is like Master and child structure I want to insert that data into Oracle tables by using the sql*loader initally I tried to created one control file but in the control file I don't know how to terminate, so I created two control files 

load data
infile 'acct.xml' "str '</DETAIL>'"
truncate
into table xxrp_acct_detail
TRAILING NULLCOLS
(
dummy filler terminated by "<DETAIL>",
SEGMENT_NUMBER enclosed by "<SEGMENT_NUMBER>" and "</SEGMENT_NUMBER>",
REMARKS enclosed by "<REMARKS>" and "</REMARKS>"
)

load data
infile acct.xml' "str '</HEADER>'"
truncate
into table xxrp_acct_header
fields terminated by "<HEADER>"
TRAILING NULLCOLS
(
dummy filler terminated by "<HEADER>",
STATUS_CODE enclosed by "<STATUS_CODE>" and "</STATUS_CODE>",
STATUS_REMARKS enclosed by "<STATUS_REMARKS>" and "</STATUS_REMARKS>"
)

I am referring the same xml file in both the control files, where as for the for the first control file I was able to load the records but the second which I assume as header table not able to load the rest records. I am getting the below mentioned error. 

Record 2: Rejected - Error on table XXRP_ACCT_HEADER, column DUMMY.
Field in data file exceeds maximum length
Record 3: Rejected - Error on table XXRP_ACCT_HEADER, column DUMMY.
Field in data file exceeds maximum length

Actually if its possible to seggrate in one control file then it will be helpful for me. I am also open for the external table option also. Please help me in this regard.

Thanks in advance.

Regards
Nagendra

odie_63

Posts: 1,563 
Registered: 04/28/09

Re: Load xml data in Oracle table 
Posted: 2011-2-23 上午1:33    in response to: 838961

Helpful

Reply

Hi,

Using SQL*Loader as an XML parser is not what I'd call a good idea.

How about using built-in XML functionalities in the database?

What's your database version? (select * from v$version)

Here's an example working on 10.2 and upwards :

SQL> create directory test_dir as 'c:\ora11\test';
 
Directory created
 
SQL> 
SQL> create table xxrp_acct_detail (
  2   segment_number number,
  3   remarks        varchar2(100)
  4  );
 
Table created
 
SQL> 
SQL> create table xxrp_acct_header (
  2   status_code    number,
  3   status_remarks varchar2(100)
  4  );
 
Table created
 
SQL> 
SQL> DECLARE
  2  
  3   acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml')
  4                              , nls_charset_id('AL32UTF8') );
  5  
  6  BEGIN
  7  
  8   insert into xxrp_acct_header (status_code, status_remarks)
  9   select *
 10   from xmltable(
 11    '/ACCOUNT_HEADER_ACK/HEADER'
 12    passing acct_doc
 13    columns status_code    number        path 'STATUS_CODE',
 14            status_remarks varchar2(100) path 'STATUS_REMARKS'
 15   );
 16  
 17   insert into xxrp_acct_detail (segment_number, remarks)
 18   select *
 19   from xmltable(
 20    '/ACCOUNT_HEADER_ACK/DETAILS/DETAIL'
 21    passing acct_doc
 22    columns segment_number number        path 'SEGMENT_NUMBER',
 23            remarks        varchar2(100) path 'REMARKS'
 24   );
 25  
 26  END;
 27  / 
 
PL/SQL procedure successfully completed
 
SQL> select * from xxrp_acct_header;
 
STATUS_CODE STATUS_REMARKS
----------- --------------------------------------------------------------------------------
        100 check
        500 process exception
 
SQL> select * from xxrp_acct_detail;
 
SEGMENT_NUMBER REMARKS
-------------- --------------------------------------------------------------------------------
             2 rp polytechnic
             3 rp polytechnic administration
             4 rp polytechnic finance
             5 rp polytechnic logistics
            20  base polytechnic
            30 
            40  base polytechnic finance
            50  base polytechnic logistics

838961

Posts: 64 
Registered: 02/22/11

Re: Load xml data in Oracle table 
Posted: 2011-2-23 上午2:20    in response to: odie_63

Reply

Hi odie,

I've read your earlier resolved messages/threads where you have exceptionally helped out many of guys like me. Hats off for that !!

Now in my below requirement acutally in begining I was in fact in thought of making it two tables so that it helps but the then I will be missing the link between the two tables I regret to my own solution. Can you please help me out in giving the solution like this. I am totally newbie to xml kind integration platform.

The proposed one is like this 

The table (combined two tables into single so that I can have refernce of each column), my db version is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

create table xxrp_acct_details( 
status_code number,
status_remarks varchar2(100),
segment_number number,
remarks varchar2(100)
);

And reading the same 'acct.xml ' I need to write a script

Now my final result should fetch me like this

select * from xxrp_acct_details

Statuscode status remarks segement remarks

---

---

---

---

100 check 2 rp polytechnic
100 check 3 rp polytechnic administration
100 check 4 rp polytechnic finance
100 check 5 rp polytechnic logistics
500 process exception 20 base polytechnic
500 process exception 30 
500 process exception 40 base polytechnic finance
500 process exception 50 base polytechnic logistics

Please let me know how can I write pl/sql script for this scenario.

Thanks in Advance for your great help!!!

Regards
Nagendra

odie_63

Posts: 1,563 
Registered: 04/28/09

Re: Load xml data in Oracle table 
Posted: 2011-2-23 上午4:01    in response to: 838961

Correct

Reply

Here are two possible solutions : 

1) Reading headers and details using two separate XMLTables : 

DECLARE
 
 acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') );
 
BEGIN
 
 insert into xxrp_acct_details (status_code, status_remarks, segment_number, remarks)
 select x1.status_code, 
        x1.status_remarks,
        x2.segment_number, 
        x2.remarks
 from xmltable(
  '/ACCOUNT_HEADER_ACK/HEADER'
  passing acct_doc
  columns header_no      for ordinality,
          status_code    number        path 'STATUS_CODE',
          status_remarks varchar2(100) path 'STATUS_REMARKS'
 ) x1,
 xmltable(
  '$d/ACCOUNT_HEADER_ACK/DETAILS[$hn]/DETAIL'
  passing acct_doc as "d",
          x1.header_no as "hn"
  columns segment_number number        path 'SEGMENT_NUMBER',
          remarks        varchar2(100) path 'REMARKS'
 ) x2
 ;
 
END;

The first one (aliased X1) extracts all headers into separate rows. The generated column HEADER_NO is used to keep track of the header rank inside the document.
Then we join a second XMLTable (X2), passing it HEADER_NO, so that we can access the corresponding DETAIL elements.

2) Reading with a single XMLTable, but a little more complex XQuery : 

DECLARE
 
 acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') );
 
BEGIN
 
 insert into xxrp_acct_details (status_code, status_remarks, segment_number, remarks)
 select x.*
 from xmltable(
  'for $i in /ACCOUNT_HEADER_ACK/HEADER
   return 
    for $j in $i/following-sibling::DETAILS[1]/DETAIL
    return element r {$i, $j}'
  passing acct_doc
  columns status_code    number        path 'HEADER/STATUS_CODE',
          status_remarks varchar2(100) path 'HEADER/STATUS_REMARKS',
          segment_number number        path 'DETAIL/SEGMENT_NUMBER',
          remarks        varchar2(100) path 'DETAIL/REMARKS'
 ) x
 ;
 
END;

Here, we use an XQuery to extract the info we need.
Basically, it's the same logic as above but with two nested loops that access each HEADER, then each DETAILS located immediately after in document order.

Here's the link to the documentation regarding XMLTable and XQuery in Oracle : 
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb_xquery.htm#CBAGCBGJ

838961

Posts: 64 
Registered: 02/22/11

Re: Load xml data in Oracle table 
Posted: 2011-2-23 下午5:41    in response to: odie_63

Reply

Hi Odie,

Its really brilliant piece of work from your side. It was nearly matching my solution. I will use the same logic for rest of the programs, if in case of any doubts in further I will seek your help ..:).

Once again thanks for your great effort.

Regards
Nagendra

841557

Posts: 1 
Registered: 03/03/11

Re: Load xml data in Oracle table 
Posted: 2011-3-3 上午11:31    in response to: odie_63

Reply

Hi Odie,

I appreciate your suggestions..!
well UI am facing an issue with loading xml data in to oracle table...
the problem here is there are elements repeating in xmls and but its not consistantl.

to get the data from repeating elements I wrote a procedure as follows

create or replace PROCEDURE xyz is
--declaring a cursor

CURSOR abc_CURSOR IS

select CLOB_ID

from table_CLOB ;

var_error_id NUMBER(22);

var_error_data varchar2(4000);

cnt INTEGER(2);

BEGIN

--opening a cursor

open abc_CURSOR;

LOOP

--fetching records from a cursor

fetch abc_CURSOR into var_error_id;
--cnt := 0

--loop 
--cnt:= cnt+1;
for cnt in 1..3 loop

SELECT CLOB_ID
/*||'|'||xmltype(tnameclob).extract('array/errInfo[cnt]/text()').getStringVal()
||'|'||xmltype(tname_clob).extract('array/errInfo[1]/errorCode/text()').getStringVal() 
|'|'||xmltype(tname_clob).extract('array/errInfo[1]/type/text()').getStringVal() */
||'|'||xmltype(tname_clob).extract('array/errInfo[cnt]/Replaced/text()').getStringVal() 
||'|'||xmltype(tname_clob).extract('array/errInfo[cnt]/sentCode/text()').getStringVal() 
||'|'||KEY
into var_error_data
FROM (SELECT CLOB_ID,
Nvl((REGEXP_REPLACE(tname_CLOB,'[^'||CHR (32)||'-'||CHR(127)||']','')),'<?xml version="1.0" encoding="UTF-8"?> <NULL> </NULL>')tname_CLOB,
KEY FROM clob e where clob_id = var_error_id )A
;

DBMS_OUTPUT.put_line(cnt);
DBMS_OUTPUT.put_line(var_error_data);

--end loop;

--testing exit conditions

EXIT when abc_CURSOR%NOTFOUND;

END LOOP;

--closing the cursor

close abc_CURSOR;

DBMS_OUTPUT.put_line('DONE');

END;

but the the problem ..if I entered [1] in the sql I am getting all the first elements ..if I enter [2] .. getting 2nd elements..
but if I enter [cnt] ..xml is not parsing and getting null values.. i.e

|784910||||2011-02-24 14:29:05|13872576
1
784913||||2011-02-24 14:29:07|13872583
2
784919||||2011-02-24 14:29:14|13872598
3

could you please let me know what was my mistake in it..

Dan G

Posts: 1 
Registered: 05/11/11

Re: Load xml data in Oracle table 
Posted: 2011-5-11 上午10:35    in response to: odie_63

Reply

Odie - ok, safe to say "you da man"... how does this method perform on very large (10-15MB) xml files? I just wish Oracle would update sql*loader to ingest xml as the data source... would make this 10,000x easier... 

odie_63

Posts: 1,563 
Registered: 04/28/09

Re: Load xml data in Oracle table 
Posted: 2011-5-11 下午12:11    in response to: Dan G

Reply

how does this method perform on very large (10-15MB) xml files?

See the XML DB FAQ here : http://forums.oracle.com/forums/thread.jspa?threadID=410714&tstart=0

Object-Relational and binary XML storages are optimized for efficient loading and data access.

Raj Rammohan

Posts: 16 
Registered: 01/17/08

Re: Load xml data in Oracle table 
Posted: 2011-5-19 下午2:42    in response to: odie_63

Reply

Hi odie,

it's a very nice explanation. anybody can understand what's that

will you please tell me how we can do the same in Oracle 9i EE 9.2.0.8.0.

Thanks a lot
Ram

user12187987

Posts: 1 
Registered: 06/30/11

Re: Load xml data in Oracle table 
Posted: 2011-6-30 上午2:04    in response to: odie_63

Reply

Hi,
It is nice coding.
because I am working in xml data file uploading process.
so thanks for very good documents.

Thanks & Regards

Ram Babu Mandal
09958422606