1

Topic: Help with parsing XML

Kind time of the days, respected ! Wrote an unpretentious script which creates object of type XMLType and then deduces on the console value. A problem that value book_id is deduced empty, and it should be deduced 269. Where the problem can disappear?

2

Re: Help with parsing XML

declare
iAppend XMLType;
oAppend schematest.books%ROWTYPE;
begin
select XMLType (
' <books>
<book_id> 269 </book_id>
<title> Oracle SQL*Loader </title>
<author> Jonathan Gennick </author>
</books> ') into iAppend
from dual;
SELECT BOOK_ID
, TITLE
, AUTHOR
INTO oAppend. BOOK_ID
, oAppend. TITLE
, oAppend. AUTHOR
FROM XMLTABLE (/books ' - ' *'
PASSING iAppend
COLUMNS BOOK_ID number PATH ' book_id / value'
, TITLE VARCHAR2 (4000 CHAR) PATH ' title / value'
, AUTHOR VARCHAR2 (4000 CHAR) PATH ' author / value ');
DBMS_OUTPUT.PUT_LINE (oAppend. BOOK_ID);
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (' NO_DATA_FOUND ');
WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (' TOO_MANY_ROWS');
end;

3

Re: Help with parsing XML

Accordingly the table books is defined so:

4

Re: Help with parsing XML

CREATE TABLE SCHEMATEST.books
(
BOOK_ID NUMBER;
TITLE VARCHAR2 (4000 CHAR);
AUTHOR VARCHAR2 (4000 CHAR)
)

5

Re: Help with parsing XML

ultrasonic7;

COLUMNS BOOK_ID number PATH ' book_id'
, TITLE VARCHAR2 (4000 CHAR) PATH ' title'
, AUTHOR VARCHAR2 (4000 CHAR) PATH ' author ');

6

Re: Help with parsing XML

ultrasonic7 wrote:

/ @value

This superfluous.

7

Re: Help with parsing XML

env , AmKad , thanks! Removed this sequence - values began to be derived and be deduced. I for a sample took a piece of the indirect code. And what for generally this @value can be necessary?

8

Re: Help with parsing XML

ultrasonic7;
This obtaining of attribute of a xml-element, . @author, for example it <book author = "T.Kyte"> </book>

9

Re: Help with parsing XML

And in what book is better parsing XML by Oracle means is described? With record in tables and so on. Except the official documentation of Orakla, it is finite) I Want to esteem something in Russian on this subject.

10

Re: Help with parsing XML

There is a question , I understood. As I in xml-ke do not have attributes, only elements and @value it is not necessary.