使用 SQL Developer 将 XML 导入 Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16353311/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
XML import to Oracle with SQL Developer
提问by user2342549
I would like to import an XML file to Oracle SQL with SQL developer. The XML has more than one node, my XML structure:
我想使用 SQL 开发人员将 XML 文件导入 Oracle SQL。XML 有多个节点，我的 XML 结构：
<"<"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/>">"
First I created the table SPECTRAEXCHANGE and than I want to use the following commands:
首先，我创建了表 SPECTRAEXCHANGE，然后我想使用以下命令：
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;
etc..
等等..
And in UPDATE session I got error message: 00933. 00000 - "SQL command not properly ended"
在 UPDATE 会话中，我收到错误消息：00933. 00000 - “SQL 命令未正确结束”
And I tried this for UPDATE session:
我在 UPDATE 会话中尝试了这个：
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;
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
但不幸的是它不起作用......所以你能帮我更新部分吗？或者给我其他教程来导入具有多个节点的 xml。谢谢。米兰
采纳答案by Alex Poole
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 ...)
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:
在您的情况下是这样的，假设您要插入和更新同一个表，并将（修改后的）原始 XML 作为 SQL*Plus 变量传递以进行我的测试：
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)
Then the update could be:
那么更新可能是：
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.
我已经降低了发送器的额外级别，您可以重复该模式以添加更多，每次都向下传递相关节点。外连接(+)将允许一些不存在的东西，例如，如果您的发射机还没有被赋予频率，或者其他什么 - 您将在相关列中得到一个空值。
