Oracle SQL*Loader 获取 CDATA 值

发布于 2024-07-16 16:47:28 字数 1398 浏览 5 评论 0原文

有人知道该怎么做吗？我知道有一种更好的方法可以在不使用 SQL*Loader 的情况下将 XML 数据加载到 Oracle，但我只是好奇如何使用它来完成此操作。我已经有一个可以将 XML 数据加载到数据库的代码，但是，如果 XML 文件具有包含 CDATA 的值，它就不会运行...

下面是控制文件代码，如果值不是 CDATA，则该代码可以工作。 ..

LOAD DATA  
INFILE FRATS.xml "str '</ROW>'"  
APPEND  
INTO TABLE "FRATERNITIES"  
(  
    DUMMY FILLER TERMINATED BY "<ROW>",  
    THE_CODE SEQUENCE (MAX, 1),  
    DUMMY2 FILLER TERMINATED BY "</COLUMN>",  
    STORE_NN_KJ ENCLOSED BY '<COLUMN NAME="THE_NAME">' AND '</COLUMN>',  
    STAFF_COUNT ENCLOSED BY '<COLUMN NAME="THE_COUNT">' AND '</COLUMN>'  
)

这是 XML 文件：
<结果> <行> <列名称=“THE_CODE”>777 CharlieOscarDelta <列名称=“THE_COUNT”>24

这是带有 CDATA 值的 XML 文件。我的控制文件不会随之运行...：
<结果> <行>

原文

Anybody knows how to do this? I know there's a better way of loading XML data to Oracle without using SQL*Loader, but I'm just curious on how this is done using it. I have already a code that can load XML data to the DB, however, it wont run if the XML file has values that contain a CDATA...

Below is the control file code which works if the values are not CDATA...

LOAD DATA  
INFILE FRATS.xml "str '</ROW>'"  
APPEND  
INTO TABLE "FRATERNITIES"  
(  
    DUMMY FILLER TERMINATED BY "<ROW>",  
    THE_CODE SEQUENCE (MAX, 1),  
    DUMMY2 FILLER TERMINATED BY "</COLUMN>",  
    STORE_NN_KJ ENCLOSED BY '<COLUMN NAME="THE_NAME">' AND '</COLUMN>',  
    STAFF_COUNT ENCLOSED BY '<COLUMN NAME="THE_COUNT">' AND '</COLUMN>'  
)

Here's the XML file:
<?xml version='1.0' encoding='MS932' ?> <RESULTS> <ROW> <COLUMN NAME="THE_CODE">777</COLUMN> <COLUMN NAME="THE_NAME">CharlieOscarDelta</COLUMN> <COLUMN NAME="THE_COUNT">24</COLUMN> </ROW> </RESULTS>

Here's the XML file with CDATA values. My control file will not run with it...:
<?xml version='1.0' encoding='MS932' ?> <RESULTS> <ROW> <COLUMN NAME="THE_CODE"><![CDATA[777]]></COLUMN> <COLUMN NAME="THE_NAME"><![CDATA[CharlieOscarDelta]]></COLUMN> <COLUMN NAME="THE_COUNT"><![CDATA[24]]></COLUMN> </ROW> </RESULTS>

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无言温柔 2024-07-23 16:47:28

你试过

STORE_NN_KJ "substr(substr(:STORE_NN_KJ,instr(:STORE_NN_KJ,'<![CDATA[')+9),0,instr(substr(:STORE_NN_KJ,instr(:STORE_NN_KJ,'<![CDATA[')+9),']]>'))" ENCLOSED BY '<COLUMN NAME="THE_NAME">' AND '</COLUMN>'

编辑
看起来我忘记了）..试试这个..

have you tried

STORE_NN_KJ "substr(substr(:STORE_NN_KJ,instr(:STORE_NN_KJ,'<![CDATA[')+9),0,instr(substr(:STORE_NN_KJ,instr(:STORE_NN_KJ,'<![CDATA[')+9),']]>'))" ENCLOSED BY '<COLUMN NAME="THE_NAME">' AND '</COLUMN>'

EDIT
Looks like I forgot a ).. Try this..

回复收藏 0 原文

~没有更多了~