select xml问题

Question

详细如下：
oracle数据库中一表：reader ，表中有4个字段是（id，name，memo，XML），该XML字段是xml数据集，格式是：<TITLE>java编程思想</TITLE>
<AUTHOR>某某某</AUTHOR>
<PRICE>CNY25.00</PRICE>
<ISBN>7-219-04913-7</ISBN>
<ADDRESS>北京</ADDRESS>
<PUBLISHER>电子工业出版社</PUBLISHER>
<PUBDATE>2004</PUBDATE>
<CLASSNO>D669.8</CLASSNO>
<TITLE>java编程思想</TITLE>现在我对reader查询，检索项是XML字段的<TITLE>标签和<ISBN>标签
select * from reader where XML.........????????
请问这条sql怎么写。谢谢！

Answer 1

SELECT  trim(replace(replace(REGEXP_SUBSTR(v_sxml,'<TITLE>.*?</TITLE>'),'<TITLE>',''),'</TITLE>','')),
        trim(replace(replace(REGEXP_SUBSTR(v_sxml,'<ISBN>.*?</ISBN>'),'<ISBN>',''),'</ISBN>',''))
FROM DUAL;-- 其中：v_sxml 是你的 xml字符串

Answer 2

谢谢 luoyoumou
期待更好地解决方法。

Answer 3

-- 例如：
select trim(replace(replace(REGEXP_SUBSTR('<TITLE>java编程思想</TITLE><AUTHOR>某某某</AUTHOR><PRICE>CNY25.00</PRICE><ISBN>7-219-04913-7</ISBN><ADDRESS>北京</ADDRESS><PUBLISHER>电子工业出版社</PUBLISHER><PUBDATE>2004</PUBDATE><CLASSNO>D669.8</CLASSNO>',
       '<TITLE>.*?</TITLE>'),'<TITLE>',''),'</TITLE>','')) as title,
       trim(replace(replace(REGEXP_SUBSTR('<TITLE>java编程思想</TITLE><AUTHOR>某某某</AUTHOR><PRICE>CNY25.00</PRICE><ISBN>7-219-04913-7</ISBN><ADDRESS>北京</ADDRESS><PUBLISHER>电子工业出版社</PUBLISHER><PUBDATE>2004</PUBDATE><CLASSNO>D669.8</CLASSNO>',
       '<ISBN>.*?</ISBN>'),'<ISBN>',''),'</ISBN>','')) as isbn
from dual;

Answer 4

create table t_get_xml
(content xmltype);insert into t_get_xml
values('<BOOK><TITLE>java编程思想</TITLE>
<AUTHOR>某某某</AUTHOR>
<PRICE>CNY25.00</PRICE>
<ISBN>7-219-04913-7</ISBN>
<ADDRESS>北京</ADDRESS>
<PUBLISHER>电子工业出版社</PUBLISHER>
<PUBDATE>2004</PUBDATE>
<CLASSNO>D669.8</CLASSNO></BOOK>');SQL> SELECT extractvalue(t.content, '/BOOK/TITLE/text()'),
  2         extractvalue(t.content, '/BOOK/ISBN/text()')
  3    FROM t_get_xml t;
 
EXTRACTVALUE(T.CONTENT,'/BOOK/                                                   EXTRACTVALUE(T.CONTENT,'/BOOK/
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
java编程思想                                                                     7-219-04913-7
 
SQL> 

Answer 5

谢谢各位。
本人水平有限。根据上面的解决方法，还是不会写。
oracle数据库中一表：reader ，表中有4个字段是（id，name，memo，XML），该XML字段是xml数据集，格式是：<TITLE>java编程思想</TITLE>
<AUTHOR>某某某</AUTHOR>
<PRICE>CNY25.00</PRICE>
<ISBN>7-219-04913-7</ISBN>
<ADDRESS>北京</ADDRESS>
<PUBLISHER>电子工业出版社</PUBLISHER>
<PUBDATE>2004</PUBDATE>
<CLASSNO>D669.8</CLASSNO>现在我对reader查询，检索项是XML字段的<TITLE>标签和<ISBN>标签
注意：reader表中有数万条记录，XML字段也就数万条。
如何根据XML字段中的<TITLE>标签和<ISBN>标签检索。
select * from reader where title=''   ??????
or select * from reader where isbn=''   ?????
怎样写呢？
谢谢。

Answer 6

刚才测试过，
1 java编程思想 7-219-04913-7但是我想问的是
select id，name，memo，title，isbn，AUTHOR from reader where title='' ??????
or select id，name，memo，title，isbn，AUTHOR from reader where isbn='' ?????
其中title，isbn是检索项。
该sql怎么写。
谢谢。

Answer 7

把extractvalue放到条件里去做判断不就可以了

Answer 8

[code=SQL]SQL> SELECT extractvalue(t.content, '/BOOK/TITLE/text()'),
  2             extractvalue(t.content, '/BOOK/ISBN/text()')
  3        FROM t_get_xml t;
 
EXTRACTVALUE(T.CONTENT,'/BOOK/                                                   EXTRACTVALUE(T.CONTENT,'/BOOK/
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
java编程思想                                                                     7-219-04913-7
java编程思想2                                                                    7-219-04913-7
 
SQL> 
SQL> SELECT extractvalue(t.content, '/BOOK/TITLE/text()'),
  2             extractvalue(t.content, '/BOOK/ISBN/text()')
  3        FROM t_get_xml t
  4        where extractvalue(t.content, '/BOOK/TITLE/text()')='java编程思想' ;
 
EXTRACTVALUE(T.CONTENT,'/BOOK/                                                   EXTRACTVALUE(T.CONTENT,'/BOOK/
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
java编程思想                                                                     7-219-04913-7
 
SQL> [code]

Answer 9

SQL> SELECT extractvalue(t.content, '/BOOK/TITLE/text()'),
  2 extractvalue(t.content, '/BOOK/ISBN/text()')
  3 FROM t_get_xml t;
 
EXTRACTVALUE(T.CONTENT,'/BOOK/ EXTRACTVALUE(T.CONTENT,'/BOOK/
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
java编程思想 7-219-04913-7
java编程思想2 7-219-04913-7
 
SQL>  
SQL> SELECT extractvalue(t.content, '/BOOK/TITLE/text()'),
  2 extractvalue(t.content, '/BOOK/ISBN/text()')
  3 FROM t_get_xml t
  4 where extractvalue(t.content, '/BOOK/TITLE/text()')='java编程思想' ;
 
EXTRACTVALUE(T.CONTENT,'/BOOK/ EXTRACTVALUE(T.CONTENT,'/BOOK/
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
java编程思想 7-219-04913-7
 
SQL> 

Answer 10

我按照您的思路，写的代码
create table t_get_xml
(content xmltype);insert into t_get_xml
values('<BOOK>select DCXML from view_holding</BOOK>')
注：只能插入一条记录。
其中view_holding这个视图里有10万条记录，而执行以下查询只能查找一条记录。
这怎能实现检索功能呢？
select * from t_get_xmlSELECT extractvalue(t.content, '/BOOK/TITLE/text()'), extractvalue(t.content, '/BOOK/ISBN/text()')
FROM t_get_xml t
where extractvalue(t.content, '/BOOK/TITLE/text()') like '%计算机%' ;
谢谢！！

Answer 11

SELECT extractvalue(t.DCXML, '/BOOK/TITLE'), extractvalue(t.DCXML, '/BOOK/ISBN')
FROM view_holding t
where extractvalue(t.DCXML, '/BOOK/TITLE') like '%计算机%' ;对于extractvalue来说，不需要'/text()'
你的t_get_xml只有一条记录，在BOOK里面有一条SELECT语句没有什么意义，而应该查view_holding。另外，如果view_holding.DCXML的数据类型不是xmltype的话,应该上述语句的
t.DCXML改为XMLTYPE(t.DCXML)

Answer 12

首先表示感谢。DCXML       NVARCHAR2(2000)SELECT extractvalue(XMLTYPE(t.DCXML), '/BOOK/TITLE'), extractvalue(XMLTYPE(t.DCXML), '/BOOK/ISBN')
FROM view_holding t
where extractvalue(XMLTYPE(t.DCXML), '/BOOK/TITLE') like '%计算机%' ;
报错：
ora-31011:xml parsing failed
ora-19202:Error occured in xml processing
lpx-00245:extra data after end of document
error at line 1
ora-06512:at"sys.xmltype",line 301
ora-06512:at line 1
如何处理？

Answer 13

omg.
我开始怀疑我的表达能力了..
parsing失败是因为你的xml格式不标准.
xml是要有唯一根节点的.

Answer 14

XML 文档形成一种树结构
XML 文档必须包含根元素。该元素是所有其他元素的父元素。XML 文档中的元素形成了一棵文档树。这棵树从根部开始，并扩展到树的最底端。所有元素均可拥有子元素：这也就是我要为你的xml增加book根节点的原因

Answer 15

报哪个错是因为Oracle试图把varchar2转为xmltype失败，说明你DCXML的内容不是合法的XML数据格式，你可以检查一下。假设你认为第一条数据的XML格式没问题，可以执行SELECT extractvalue(XMLTYPE(t.DCXML), '/BOOK/TITLE'), extractvalue(XMLTYPE(t.DCXML), '/BOOK/ISBN')
FROM view_holding t
where extractvalue(XMLTYPE(t.DCXML), '/BOOK/TITLE') like '%计算机%' 
and   rownum<2;来看是否报错。另外，如果你XML的内容没有根节点，那么也会报错，这时可以把SQL改为
SELECT extractvalue(XMLTYPE('<ROOT>'||t.DCXML||'</ROOT>'), '//BOOK/TITLE'), extractvalue(XMLTYPE('<ROOT>'||t.DCXML||'</ROOT>'), '//BOOK/ISBN')
FROM view_holding t
where extractvalue(XMLTYPE('<ROOT>'||t.DCXML||'</ROOT>'), '//BOOK/TITLE') like '%计算机%' 

Answer 16

谢谢！
DCXML NVARCHAR2(2000)DCXML 的数据：<SUMMARY>本书内容涵盖国际间的人才争夺战等。</SUMMARY><AUTHOR>王辉耀著</AUTHOR><PRICE>CNY36.00</PRICE><LANGUAGE>chi</LANGUAGE><PAGE>312页</PAGE><ISBN>978-7-80234-102-9</ISBN><ADDRESS>北京</ADDRESS><PUBLISHER>中国发展出版社</PUBLISHER><BOOKSIZE>24cm</BOOKSIZE><PUBDATE>2007</PUBDATE><CLASSNO>K820.7</CLASSNO><TITLE>当代中国海归</TITLE><CALLNO>K820.7/0078</CALLNO><AUTHOR>陈望道著</AUTHOR><PRICE>CNY25.00</PRICE><LANGUAGE>chi</LANGUAGE><PAGE>238页</PAGE><ISBN>978-7-309-05615-0</ISBN><ADDRESS>上海</ADDRESS><PUBLISHER>复旦大学出版社</PUBLISHER><BOOKSIZE>23cm</BOOKSIZE><PUBDATE>2008.1</PUBDATE><CLASSNO>H15</CLASSNO><TITLE>修辞学发凡</TITLE><CALLNO>H15/0159</CALLNO>有10万条这样格式的数据。
怎么写呢？

Answer 17

首先谢谢你！
但是我对xml理解不够。
请赐教。

Answer 18

因为你给的数据本质上说不是xml完整数据,只能说是片段.所以没法直接用xml解析.
hyee已经给出了很好的解决方案,先构造xml,然后利用extractvalue解析值进行比较SQL> create table reader
  2  (id  number,
  3  name varchar2(10),
  4  memo varchar2(10),
  5  DCXML nvarchar2(2000));
 
Table created
 
SQL> insert into reader
  2  values(1,'john','d',
  3  '<SUMMARY>本书内容涵盖国际间的人才争夺战等。</SUMMARY>
  4  <AUTHOR>王辉耀著</AUTHOR>
  5  <PRICE>CNY36.00</PRICE>
  6  <LANGUAGE>chi</LANGUAGE>
  7  <PAGE>312页</PAGE>
  8  <ISBN>978-7-80234-102-9</ISBN>
  9  <ADDRESS>北京</ADDRESS>
 10  <PUBLISHER>中国发展出版社</PUBLISHER>
 11  <BOOKSIZE>24cm</BOOKSIZE>
 12  <PUBDATE>2007</PUBDATE>
 13  <CLASSNO>K820.7</CLASSNO>
 14  <TITLE>当代中国海归</TITLE>
 15  <CALLNO>K820.7/0078</CALLNO>');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> 
SQL> insert into reader
  2  values(2,'tom','e',
  3  '<AUTHOR>陈望道著</AUTHOR>
  4  <PRICE>CNY25.00</PRICE>
  5  <LANGUAGE>chi</LANGUAGE>
  6  <PAGE>238页</PAGE>
  7  <ISBN>978-7-309-05615-0</ISBN>
  8  <ADDRESS>上海</ADDRESS>
  9  <PUBLISHER>复旦大学出版社</PUBLISHER>
 10  <BOOKSIZE>23cm</BOOKSIZE>
 11  <PUBDATE>2008.1</PUBDATE>
 12  <CLASSNO>H15</CLASSNO>
 13  <TITLE>修辞学发凡</TITLE>
 14  <CALLNO>H15/0159</CALLNO>');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> 
SQL> SELECT *
  2    FROM reader t
  3   WHERE extractvalue(xmltype('<BOOK>' || t.dcxml || '</BOOK>'),
  4                      '/BOOK/TITLE/text()') = '修辞学发凡';
 
        ID NAME       MEMO       DCXML
---------- ---------- ---------- --------------------------------------------------------------------------------
         2 tom        e          <AUTHOR>陈望道著</AUTHOR>
                                 <PRICE>CNY25.00</PRICE>
                                 <LANGUAGE>chi</LANGUAGE>
                                 <PAGE>238页</PAGE>
                                 <ISBN>978-7-309-05615-0</ISBN>
                                 <ADDRESS>上海</ADDRESS>
                                 <PUBLISHER>复旦大学出版社</PUBLISHER>
                                 <BOOKSIZE>23cm</BOOKSIZE>
                                 <PUBDATE>2008.1</PUBDATE>
                                 <CLASSNO>H15</CLASSNO>
                                 <TITLE>修辞学发凡</TITLE>
                                 <CALLNO>H15/0159</CALLNO>
 
SQL>