xml到oracle DB表:遇到问题 [英] xml to oracle DB table : encountering problems
问题描述
我有一个使用Editplus(在Windows中)创建的示例xml文件.
I have a sample xml file created using Editplus( in windows).
< ?xml version="1.0" encoding="UTF-8" ?>
< badges >
< row UserId="3714" Name="Teacher" Date="2008-09-15T08:55:03.923"/>
< row UserId="994" Name="Teacher" Date="2008-09-15T08:55:03.957"/>
< / badges>
我在这里的目标是将这些信息获取到Oracle DB表中.如此处建议 https://stackoverflow.com/questions/998055?sort=newest#sort-top,我尝试执行sql命令.但是不能成功,
My goal here is to get this information into Oracle DB table. As suggested here https://stackoverflow.com/questions/998055?sort=newest#sort-top, I tried to execute the sql commands. But couldn't succeed,
========================= sql查询1 ==================== =========
========================= sql query 1 ============================
SQL> SELECT XMLTYPE(bfilename('D', 'tmp.xml'), nls_charset_id('UTF8')) xml_data FROM dual;
XML_DATA
------------------------------------------------------------
<?xml version="1.0" encoding="WINDOWS-1252"?>
<badges>
<row UserId="3714" Name
在输出中,我看到xml文件的一半被截断了.并且输出中的编码类型被视为WINDOWS-1252.有人可以解释为什么会这样吗?
In the output, I see half of the xml file got truncated. And the encoding type in output is seen as WINDOWS-1252. Could someone explain why is it happening so?
================================================ =========================
==========================================================================
=============================== sql查询2 ============== ==================
=============================== sql query 2 ===============================
SQL> SELECT UserId, Name, to_timestamp(dt, 'YYYY-MM-DD"T"HH24:MI:SS.FF3') dt
2 FROM (SELECT XMLTYPE(bfilename('D', 'tmp.xml'),
3 nls_charset_id('WINDOWS-1252')) xml_data
4 FROM dual),
5 XMLTable('for $i in /badges/row
6 return $i'
7 passing xml_data
8 columns UserId NUMBER path '@UserId',
9 Name VARCHAR2(50) path '@Name',
10 dt VARCHAR2(25) path '@Date');
================================================ ====================== 相同的查询在这里工作 https://stackoverflow.com/questions/998055?sort=newest#sort-顶部.但是对我来说却不是. 我的机器上安装了oracle 10g. 有人可以建议更正以使查询正常工作.
===================================================================== The same query was working here https://stackoverflow.com/questions/998055?sort=newest#sort-top. But for me it doesn't. I have oracle 10g installed on my machine. Could someone suggest the corrections to make the queries work.
谢谢.
推荐答案
考虑到您的第一点,您的输出仅在显示时被截断.您可以使用SET LONG
更改SQL * Plus中显示的字节数:
Considering your first point, your output is only truncated on display. You can change how many bytes are displayed in SQL*Plus with SET LONG
:
SQL> SELECT XMLTYPE(bfilename('D', 'test.xml'),
2 nls_charset_id('WINDOWS-1252')) xml_data FROM dual;
XML_DATA
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<badges>
<row UserId="3714" Name=
SQL> SET LONG 4000
SQL> /
XML_DATA
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<badges>
<row UserId="3714" Name="Teacher" Date="2008-09-15T08:55:03.923"/>
<row UserId="994" Name="Teacher" Date="2008-09-15T08:55:03.957"/>
</badges>
您已经注意到,您的字符集将根据您的NLS会话参数进行修改(即,文件将转换为客户端的字符集).
As you have noticed, your character set will be modified per your NLS session parameters (i-e: the file will be translated to the character set of your client).
第二点:
- 您正在使用哪个版本的SQL * Plus?它可能比数据库旧,并且无法识别合成句
- 您能否发布在SQL * Plus中键入的确切查询(请使用SO的CODE功能)
因为我无法使用Oracle 10.2.0.3复制:
because I can not reproduce with Oracle 10.2.0.3:
SQL> SELECT UserId, NAME, to_timestamp(dt, 'YYYY-MM-DD"T"HH24:MI:SS.FF3') dt
2 FROM (SELECT XMLTYPE(bfilename('D', 'test.xml'),
3 nls_charset_id('WINDOWS-1252')) xml_data FROM dual),
4 XMLTable('for $i in /badges/row
5 return $i'
6 passing xml_data columns UserId NUMBER path '@UserId',
7 NAME VARCHAR2(50) path '@Name',
8 dt VARCHAR2(25) path '@Date');
USERID NAME DT
---------- --------- ----------------------------
3714 Teacher 15/09/08 08:55:03,923000000
994 Teacher 15/09/08 08:55:03,957000000
更新:此XMLTable synthax必须是10gR2(10.2.*)的新功能(需要确认)
Update: This XMLTable synthax must be a new feature of the 10gR2 (10.2.*) (needs confirmation)
You can however use another method of accessing XML Data(described in another SO):
SQL> SELECT extractvalue(column_value, '/row/@UserId') "userID",
2 extractvalue(column_value, '/row/@Name') "Name",
3 extractvalue(column_value, '/row/@Date') "Date"
4 FROM TABLE(XMLSequence(XMLTYPE(bfilename('D', 'tmp.xml'),
5 nls_charset_id('WINDOWS-1252')).extract('/badges/row'))) t;
userID Name Date
------- --------- ------------------------
3718 Teacher 2008-09-15T08:55:03.923
994 Teacher 2008-09-15T08:55:03.957
这篇关于xml到oracle DB表:遇到问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!