Oracle XMLType
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Oracle XMLType

  1. #1
    Join Date
    Feb 2002
    Posts
    1
    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


  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width