使用SQL Developer将XML导入Oracle [英] XML import to Oracle with SQL Developer
问题描述
我想用SQL开发人员将XML文件导入Oracle SQL。 XML有多个节点，我的XML结构：
I would like to import an XML file to Oracle SQL with SQL developer. The XML has more than one node, my XML structure:
<"<"SPECTRAEXCHANGE>">"
<"<"APPLICATION>">"
<"<"SV_SV_ID>">"kClong<"<"/SV_SV_ID/>">"
<"<"SS_SS_ID>">"kClong<"<"/SS_SS_ID/>">"
<"<"AP_NAME>">"kCstring (64)<"<"/AP_NAME/>">"
<"<"AP_PRJ_IDENT>">"kCstring (32)<"<"/AP_PRJ_IDENT/>">"
<"<"STATION>">"
<"<"TCS_NAME>">"kCstring (64)<"<"/TCS_NAME/>">"
<"<"TCS_CALL>">"kCstring (256)<"<"/TCS_CALL/>">"
<"<"HORIZONTAL_ELEVATIONS>">"
<"<"HORIZONTAL_ELEVATION>">"
<"<"HE_AZIMUT>">"kCdouble<"<"/HE_AZIMUT/>">"
<"<"HE_ELEVATION>">"kCdouble<"<"/HE_ELEVATION/>">"
<"<"/HORIZONTAL_ELEVATION/>">"
<"<"/HORIZONTAL_ELEVATIONS/>">"
<"<"TRANSMITTER>">"
<"<"EQP_EQUIP_NAME>">"kCstring (128)<"<"/EQP_EQUIP_NAME/>">"
<"<"EQP_EQUIP_TYPE>">"kCstring (16)<"<"/EQP_EQUIP_TYPE/>">"
<"<"FREQUENCY>">"
<"<"EFL_FREQ>">"kCdouble<"<"/EFL_FREQ/>">"
<"<"COORDINATED_FREQUENCY>">"
<"<"COF_DAT>">"kWrDate<"<"/COF_DAT/>">"
<"<"/COORDINATED_FREQUENCY/>">"
<"<"/FREQUENCY/>">"
<"<"/TRANSMITTER/>">"
<"<"/STATION/>">"
<"<"/APPLICATION/>">"
<"<"/SPECTRAEXCHANGE/>">"
首先我创建了表SPECTRAEXCHANGE，而不是我想使用以下命令：
First I created the table SPECTRAEXCHANGE and than I want to use the following commands:
INSERT INTO TABLENAME(SV_SV_ID,
SS_SS_ID,
AP_NAME,
AP_PRJ_IDENT )
WITH t AS (SELECT xmltype(bfilename('TEST_DIR','yourXMLFileName.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual)
SELECT
extractValue(value(x),'/APPLICATION/SV_SV_ID') SV_SV_ID /* value will be kClong */
,extractValue(value(x),'/APPLICATION/SS_SS_ID') SS_SS_ID /* value will be kClong*/
,extractValue(value(x),'APPLICATION/AP_NAME') AP_NAME /* value will be kCstring (64)*/
,extractValue(value(x),'APPLICATION/AP_PRJ_IDENT') AP_PRJ_IDENT
FROM t,TABLE(XMLSequence(extract(t.xmlcol,'/SPECTRAEXCHANGE/APPLICATION'))) x;
UPDATE SPECTRAEXCHANGE SET TCS_NAME = extractValue(value(x),'/APPLICATION/STATION/TCS_NAME'),
TCS_CALL = extractValue(value(x),'/APPLICATION/STATION/TCS_CALL')
WITH t AS (SELECT xmltype(bfilename('TEST_DIR','yourXMLFileName.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual)
FROM t,TABLE(XMLSequence(extract(t.xmlcol,'/SPECTRAEXCHANGE/APPLICATION/STATION'))) x;
等..
并且在UPDATE session我收到错误消息：
00933. 00000 - SQL命令未正确结束
And in UPDATE session I got error message: 00933. 00000 - "SQL command not properly ended"
我尝试了UPDATE会话：
And I tried this for UPDATE session:
UPDATE SPECTRAEXCHANGE SET
TCS_NAME = x.TCS_NAME,
TCS_CALL = x.TCS_CALL
WITH t AS (SELECT xmltype(bfilename('TEST_DIR','yourXMLFileName.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual)
SELECT
extractValue(value(x),'APPLICATION/STATION/TCS_NAME') TCS_NAME
,extractValue(value(x),'APPLICATION/STATION/TCS_CALL') TCS_CALL
FROM t,TABLE(XMLSequence(extract(t.xmlcol,'/SPECTRAEXCHANGE/APPLICATION/STATION'))) x;
但不幸的是它不起作用...
所以你可以帮我更新部分？或者给我其他教程来导入多个节点的xml。
谢谢。
米兰
but unfortunatly it doesn't work... so can you help me with update part? or give me other tutorial to import xml with more than one node. thanks. Milan
推荐答案
您正在尝试更新子查询中的两列，但您的语法错误;它应该更像是：
You're trying to update two columns from a subquery, but your syntax is wrong; it should be more like:
update tablename set (col1 = val1, col2 = val2)
select (val1, val 2 from ...)
在你的情况下，假设你正在插入和更新同一个表，并将（修改的）原始XML作为SQL * Plus var传递给我的测试：
In your case something like this, assuming you're inserting and updating the same table, and passing the (modified) raw XML as an SQL*Plus var for my testing:
create table spectraexchange(sv_sv_id varchar2(15), ss_ss_id varchar2(15),
ap_name varchar2(15), ap_prj_ident varchar2(15),
tcs_name varchar2(15), tcs_call varchar2(15));
Table created.
insert into spectraexchange(sv_sv_id, ss_ss_id, ap_name, ap_prj_ident)
select extractvalue(value(x), 'APPLICATION/SV_SV_ID') sv_sv_id,
extractvalue(value(x), 'APPLICATION/SS_SS_ID') ss_ss_id,
extractvalue(value(x), 'APPLICATION/AP_NAME') ap_name,
extractvalue(value(x), 'APPLICATION/AP_PRJ_IDENT') ap_prj_ident
from (
select xmltype(:raw_xml) xmlcol from dual
) t
cross join table(XMLSequence(extract(t.xmlcol,
'/SPECTRAEXCHANGE/APPLICATION'))) x;
1 row created.
select * from spectraexchange;
SV_SV_ID SS_SS_ID AP_NAME AP_PRJ_IDENT TCS_NAME TCS_CALL
--------------- --------------- --------------- --------------- --------------- ---------------
kClong kClong kCstring (64) kCstring (32)
然后更新可能是：
update spectraexchange
set (tcs_name, tcs_call) = (
select extractvalue(value(x), 'STATION/TCS_NAME'),
extractvalue(value(x), 'STATION/TCS_CALL')
from (
select xmltype(:raw_xml) xmlcol from dual
) t
cross join table(XMLSequence(extract(t.xmlcol,
'/SPECTRAEXCHANGE/APPLICATION/STATION'))) x
);
1 row updated.
select * from spectraexchange;
SV_SV_ID SS_SS_ID AP_NAME AP_PRJ_IDENT TCS_NAME TCS_CALL
--------------- --------------- --------------- --------------- --------------- ---------------
kClong kClong kCstring (64) kCstring (32) kCstring (64) kCstring (256)
如果当然这也假定每个应用程序有一个工作站，否则你需要多个连接表来保持我猜的关系;只有一个应用程序或您的更新需要以某种方式相关。但是更新似乎毫无意义，你可以在插入时全部完成：
If course this also assumes one station per application, otherwise you'll need multiple joined tables to hold the relationships I guess; and only one application or your update would need to be correlated somehow. But then the update seems pointless, you could do it all on insert:
insert into spectraexchange(sv_sv_id, ss_ss_id, ap_name, ap_prj_ident,
tcs_name, tcs_call)
select extractvalue(value(x), 'APPLICATION/SV_SV_ID') sv_sv_id,
extractvalue(value(x), 'APPLICATION/SS_SS_ID') ss_ss_id,
extractvalue(value(x), 'APPLICATION/AP_NAME') ap_name,
extractvalue(value(x), 'APPLICATION/AP_PRJ_IDENT') ap_prj_ident,
extractvalue(value(x), 'APPLICATION/STATION/TCS_NAME') tcs_name,
extractvalue(value(x), 'APPLICATION/STATION/TCS_CALL') tcs_call
from (
select xmltype(:raw_xml) xmlcol from dual
) t
cross join table(XMLSequence(extract(t.xmlcol,
'/SPECTRAEXCHANGE/APPLICATION'))) x;
...（仅适用于一对一关系）所以我很遗憾图片中的内容。
... (which only works with one-to-one relationships) so clearly I'm missing something from the picture.
根据您的评论，您有一对多的关系并且您正在插入所有内容在一个表（！？）中，您可以这样做：
Based on your comments that you have one-to-many relationships and you're inserting everything into one table (!?), you can do this instead:
insert into spectra exchange ( ... columns ... )
select a.sv_sv_id, a.ss_ss_id, a.ap_name, a.ap_prj_ident,
s.tcs_name, s.tcs_call,
t.eqp_equip_name, t.eqp_equip_type
from (select xmltype(:raw_xml) xmlcol from dual) r
cross join xmltable('/SPECTRAEXCHANGE/APPLICATION' passing r.xmlcol
columns sv_sv_id varchar2(15) path 'SV_SV_ID',
ss_ss_id varchar2(15) path 'SS_SS_ID',
ap_name varchar2(15) path 'AP_NAME',
ap_prj_ident varchar2(15) path 'AP_PRJ_IDENT',
stations xmltype path 'STATION'
) (+) a
cross join xmltable('/STATION' passing a.stations
columns tcs_name varchar2(15) path 'TCS_NAME',
tcs_call varchar2(15) path 'TCS_CALL',
transmitter xmltype path 'TRANSMITTER'
) (+) s
cross join xmltable('/TRANSMITTER' passing s.transmitter
columns eqp_equip_name varchar2(15) path 'EQP_EQUIP_NAME',
eqp_equip_type varchar2(15) path 'EQP_EQUIP_TYPE',
frequency xmltype path 'FREQUENCY'
) (+) t
/
我已经降低了发送器的额外水平，你可以重复模式添加更多，每次都传递相关节点。外连接（+）将允许一些不存在的东西，例如如果你有一个尚未给出频率的发射机，或者其他什么 - 你会在相关列中得到一个空值。
I've gone down an extra level to transmitter, and you can just repeat the pattern to add more, passing the relevant node down each time. The outer joins (+) will allow for some things not existing, e.g. if you have a transmitter that hasn't been given a frequency yet, or whatever - you'll get a null in the relevant columns.
这篇关于使用SQL Developer将XML导入Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
