Home » Developer & Programmer » JDeveloper, Java & XML » XMLSequence String match how to make it work? (Oracle XML DB)
icon8.gif  XMLSequence String match how to make it work? [message #405180] Tue, 26 May 2009 14:26 Go to next message
nidhichutani
Messages: 1
Registered: April 2009
Junior Member
XMLSequence String matching is not working, can anyone please help point out how to fix the below query?

I am trying to select all employees who have a Asterix(*) in the Salary.

------------------------------------------------------

DROP INDEX emp_idx;
DROP TABLE emp;
CREATE TABLE emp OF xmltype;
INSERT INTO emp VALUES (
xmltype('<emps>
<title>Manager</title>
<info name="A" sal="1000*"/>
<info name="B" sal="*"/>
<info name="C" sal="500*"/>
<info name="D" sal="$"/>
</emps>'));

CREATE INDEX emp_idx ON emp(OBJECT_VALUE) INDEXTYPE is CTXSYS.CONTEXT;

SELECT extractValue(value(t),'//info/@name')EMP_NAME,
extractValue(value(t),'//info/@sal')SALARY
FROM emp r,
TABLE(xmlsequence(extract(object_value,'//info[@sal="*"]'))) t;

--------------------------------------------------

[Updated on: Tue, 26 May 2009 14:29]

Report message to a moderator

Re: XMLSequence String match how to make it work? [message #405678 is a reply to message #405180] Fri, 29 May 2009 00:29 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
It works for me, as demonstrated below. Can you post a copy and paste of the same run with different results? I used emp_xml in place of emp, so I would not disrupt the existing emp demo table.

SCOTT@orcl_11g> DROP INDEX emp_xml_idx;

Index dropped.

SCOTT@orcl_11g> DROP TABLE emp_xml;

Table dropped.

SCOTT@orcl_11g> CREATE TABLE emp_xml OF xmltype;

Table created.

SCOTT@orcl_11g> INSERT INTO emp_xml VALUES (
  2  xmltype('<emps>
  3  <title>Manager</title>
  4  <info name="A" sal="1000*"/>
  5  <info name="B" sal="*"/>
  6  <info name="C" sal="500*"/>
  7  <info name="D" sal="$"/>
  8  </emps>'));

1 row created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE INDEX emp_xml_idx ON emp_xml(OBJECT_VALUE) INDEXTYPE is CTXSYS.CONTEXT;

Index created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> SELECT extractValue(value(t),'//info/@name')EMP_NAME,
  2  extractValue(value(t),'//info/@sal')SALARY
  3  FROM emp_xml r,
  4  TABLE(xmlsequence(extract(object_value,'//info[@sal="*"]'))) t;

EMP_NAME
--------------------------------------------------------------------------------
SALARY
--------------------------------------------------------------------------------
B
*


SCOTT@orcl_11g>

Previous Topic: how to call java class in plsql or how to use plsql to invoke shell (2 threads merged by bb)
Next Topic: what is OA Framework ???
Goto Forum:
  


Current Time: Sat Dec 03 20:03:33 CST 2016

Total time taken to generate the page: 0.13244 seconds