Hi All,

We are trying to use XMLType feature of Oracle9i.

I have created a table with a XMLType column.

Inserted a row as follows.

insert into tblM1Data(
PK_COL,
XMLDATA)
values
(
1,
sys.XMLTYPE.createXML('

GLA1111
GL Account 1111
110
100
10


GLA2222
GL Account 2222
220
195
25

')
);

Within the XMLType data, there are two records.

I have written the following query that returned me a single recordset. The query is as follows

select PK_COL,
substr(sys.xmlType.getStringVal(sys.xmlType.extract(t2.xmldata,'/ACCR_VAC/ROW/GL_ACC/text()')),1,10) "Account code",
substr(sys.xmlType.getStringVal(sys.xmlType.extract(t2.xmldata,'/ACCR_VAC/ROW/DESCRIPTION/text()')),1,20) "DESCRIPTION",
TO_NUMBER(sys.xmlType.getStringVal(sys.xmlType.extract(t2.xmldata,'/ACCR_VAC/ROW/PRIOR_YR_PAY/text()'))) "Prior YearPayment",
TO_NUMBER(sys.xmlType.getStringVal(sys.xmlType.extract(t2.xmldata,'/ACCR_VAC/ROW/CURR_YR_PAY/text()'))) "Current Year Payment"
from tblm1data t2;

I want to make a qury to select multiple record(in this case 2) from this data. Can anybody help?

Regards,

Tapas