开发者

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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜