Oracle XMLType

[tbandyop]

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

[TimHall]

Remember, the resulting datatype of an extract is still XMLTYPE. When you use getStringVal() it will do it's best to give you a meaningful value. If more than one tag is present that matches your extract specification you will get a "multi-rowed" XML document in the resulting XMLTYPE which getStringVal() will display as your results glued together.

You really need to access the data using indexed XPATH like:

SELECT a.col1.extract('/ROWS/MY_TAG[1]/text()')
FROM tab1 a;

Where [1] specifies the first matching tag in the XMLTYPE.

If you want to return a recordset with the elements split up I think your going to have to use a stored procedure to split out the data into a TABLE type then use the TABLE and CAST functions to create a REF CURSOR you can pass out. That may work but I've not tried it. All the individual bits of technology are covered in these articles, but I'm not going to write it for you and I'm not promising it's going to work.

http://www.oracle-base.com/Articles/...peDatatype.asp

http://www.oracle-base.com/Articles/...namicViews.asp

http://www.oracle-base.com/Articles/...Recordsets.asp

Cheers