XML批量插入Oracle表 [英] Xml bulk insert in oracle table

问题描述

我是oracle的新手， 我正在尝试的是， 我有一个xml，我尝试将其插入到oracle数据库表中，当我尝试将其插入时，就形成了查询.我遇到类似的错误

Im new to oracle, what im trying is, I have an xml, i try to insert the same in a oracle database table, i have formed a query, when i try to insert it. i get some Error like

错误报告- ORA-06550:第35行，第84列: PL/SQL:ORA-00933:SQL命令未正确结束 ORA-06550:第5行，第2列: PL/SQL:忽略了SQL语句 06550.00000-％s行，％s列:\ n％s" *原因:通常是PL/SQL编译错误. *动作:

Error report - ORA-06550: line 35, column 84: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 5, column 2: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

我无法弄清楚我错过了什么，建议我如何更改查询，

i couldnt able to figure out what i missed, Suggest me how to change the query,

这是我尝试工作的XML和查询.

heres my XML and Query which im trying to work.

- <call>
- <callSummary>
  <indId>100</indId> 
  <notificationNo>notification</notificationNo> 
  <orderNo>orderno</orderNo> 
  </callSummary>
- <callList>
- <callDetails>
  <maintenancetype>1</maintenancetype> 
  <serialNo>1</serialNo> 
  <unitType>unit type</unitType> 
  </callDetails>
- <callDetails>
  <maintenancetype>1</maintenancetype> 
  <serialNo>2</serialNo> 
  <unitType>unit type</unitType> 
  </callDetails>
- <callDetails>
  <maintenancetype>2</maintenancetype> 
  <serialNo>1</serialNo> 
  <unitType>unit type</unitType>
  </callDetails>
- <callDetails>
  <maintenancetype>2</maintenancetype> 
  <serialNo>2</serialNo> 
  <unitType>unit type</unitType> 
  </callDetails>
  </callList>
  </call>

我的查询是

DECLARE  
 call_xml XMLTYPE := xmltype('<call><callSummary><indId>100</indId><notificationNo>notification</notificationNo><orderNo>orderno</orderNo><</callSummary><callList><callDetails><maintenancetype>1</maintenancetype><serialNo>1</serialNo><unitType>unit type</unitType></callDetails><callDetails><maintenancetype>1</maintenancetype><serialNo>2</serialNo><unitType>unit type</unitType></callDetails><callDetails><maintenancetype>2</maintenancetype><serialNo>1</serialNo><unitType>unit type</unitType></callDetails><callDetails><maintenancetype>2</maintenancetype><serialNo>2</serialNo><unitType>unit type</unitType></callDetails></callList></call>');
 
 BEGIN
 INSERT INTO ORDER_DETAILS (
		 IND_ID,NOTIFICATION_NO,ORDER_NO,MAINT_TYPE,SERIAL_NO,UNIT_TYPE)
  SELECT 
  call_xml.value('call/callSummary/indId[1]','CLNT(3)'),
  call_xml.value('call/callSummary/notificationNo[1]','CHAR(12)'),
  call_xml.value('call/callSummary/orderNo[1]','CHAR(12)'),
  call_xml.value('call/callSummary/callList/callDetails/maintenancetype[1]','CHAR(1)'),
  call_xml.value('call/callSummary/callList/callDetails/serialNo[1]','CHAR(1)'),
  call_xml.value('call/callSummary/callList/callDetails/unitType[1]','CHAR(20)') from call_xml.nodes('call');
  
  END;

我希望您提前理解我的问题，Thanx.

I hope you understand my question, Thanx in advance.

推荐答案

您将要使用xmlsequence函数.它将允许您从XML对象中选择节点列表.如果您想使用pl/sql，请用变量替换xmltype.

You'll want to use the xmlsequence function. It will allow you to select a list of nodes from your XML object. If you'd like to use pl/sql, the replace xmltype, with your variable.

      SELECT 
  extractValue(column_value,'callSummary/indId[1]'),
  extractValue(column_value,'callSummary/notificationNo[1]'),
  extractValue(column_value,'callSummary/orderNo[1]'),
  extractValue(column_value,'callSummary/callList/callDetails/maintenancetype[1]'),
  extractValue(column_value,'callSummary/callList/callDetails/serialNo[1]'),
  extractValue(column_value,'callSummary/callList/callDetails/unitType[1]') from table (
       xmlsequence(
         extract(
           xmltype('<call><callSummary><indId>100</indId><notificationNo>notification</notificationNo><orderNo>orderno</orderNo></callSummary><callList><callDetails><maintenancetype>1</maintenancetype><serialNo>1</serialNo><unitType>unit type</unitType></callDetails><callDetails><maintenancetype>1</maintenancetype><serialNo>2</serialNo><unitType>unit type</unitType></callDetails><callDetails><maintenancetype>2</maintenancetype><serialNo>1</serialNo><unitType>unit type</unitType></callDetails><callDetails><maintenancetype>2</maintenancetype><serialNo>2</serialNo><unitType>unit type</unitType></callDetails></callList></call>'),'/call/callSummary')));

这篇关于XML批量插入Oracle表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！