Oracle XMLTABLE syntax error
I've created a simple example of a table with a xmltype column
--set up the able
CREATE TABLE dept (name varchar2(20), employees XMLTYPE);
--insert a test row
INSERT INTO dept VALUES (
'Sales',
XMLTYPE.createxml(
'<?xml version="1.0" encoding="UTF-8"?>
<People>
<Person>
<Name>TED</Name>
<Age>35</Age>
</Person>
<Person>
开发者_JAVA技巧 <Name>BEN</Name>
<Age>36</Age>
</Person>
<Person>
<Name>EDI</Name>
<Age>37</Age>
</Person>
</People>'
)
);
I just want to flatten the results (to use in another query) by using xml table so that I get
Sales | Ted | 35
Sales | BEN | 36
Sales | EDI | 37
So I wrote this query using xtable
SELECT dept name, people.Name, people.Age
FROM dept,
XMLTABLE('/people'
PASSING dept.employees
COLUMNS
"Name" varchar2(6) PATH '/People/Person/Name',
"Age" varchar2(6) PATH '/People/Person/Age'
people;
However I get the following error
"ORA-01780: string literal required'
and the ide highlights the following line
"Name" varchar2(6) PATH '/People/Person/Name',
Can anyone tell me what I am missing/doing wrong
EDIT - Run this - alter session set cursor_sharing = exact
or set cursor_sharing=exact in the parameter file.
and then you are probably missing a closing bracket too after COLUMNS (/Age) -
SELECT dept name, people.Name, people.Age
FROM dept,
XMLTABLE('/people'
PASSING dept.employees
COLUMNS
"Name" varchar2(6) PATH '/People/Person/Name',
"Age" varchar2(6) PATH '/People/Person/Age' )
people;
精彩评论