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