4 Replies Latest reply: Oct 30, 2012 4:56 PM by TrojanSpirit RSS

    XMLTABLE returns only first row

    TrojanSpirit
      I have follwoing XML inserted into the column named as TEXT in the table MASTERTB.

      *<?xml version="1.0" encoding="utf-8" ?>*
      *<Rowsets DateCreated="2010-11-30T11:12:10" EndDate="2010-06-05T16:52:23" StartDate="2010-06-05T16:52:23" Version="12.0.10 Build(18)">*
      *<Rowset>*
      *<Columns>*
      *<Column Description="Material Number" MaxRange="1" MinRange="0" Name="MATERIAL" SQLDataType="1" SourceColumn="MATERIAL"/>*
      *<Column Description="" MaxRange="1" MinRange="0" Name="TANK" SQLDataType="1" SourceColumn="TANK"/>*
      *</Columns>*
      *<Row>*
      *<MATERIAL>1000000144</MATERIAL>*
      *<TANK>T1000</TANK>*
      *</Row>*
      *<Row>*
      *<MATERIAL>2000000008</MATERIAL>*
      *<TANK>T1000</TANK>*
      *</Row>*
      *<Row>*
      *<MATERIAL>2000000009</MATERIAL>*
      *<TANK>T1000</TANK>*
      *</Row>*
      *<Row>*
      *<MATERIAL>2000000016</MATERIAL>*
      *<TANK>T1000</TANK>*
      *</Row>*
      *<Row>*
      *<MATERIAL>3000000036</MATERIAL>*
      *<TANK>T1000</TANK>*
      *</Row>*
      *</Rowset>*
      *</Rowsets>*

      Now, when my requirement is to get all the Material values so when I run follwoing query in Oracle,

      SELECT RW.MATERIAL
      FROM MASTERTB TM,
      XMLTable('//Row' PASSING TM.TEXT
      COLUMNS  "MATERIAL"    CHAR(30) PATH 'MATERIAL') AS RW

      it return only 1000000144 (First MATERIAL). How can I read all the MATERIAL? Also, how can I read the entire XML in Text format if I want to check what has been inserted?

      ANy help will be appreciated !

      Edited by: 967327 on Oct 27, 2012 12:28 AM
        • 1. Re: XMLTABLE returns only first row
          AlexAnd
          >
          <Rowsets DateCreated="2010-11-30T11:12:10" EndDate="2010-06-05T16:52:23" StartDate="2010-06-05T16:52:23" Version="12.0.10 Build(18)">
          <Rowset>
          ...
          </Rowset>
          >
          so where is </Rowsets> ?

          your query works for me
          SQL> select * from v$version where rownum=1;
           
          BANNER
          ----------------------------------------------------------------
          Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
           
          SQL> 
          SQL> with MASTERTB as
            2  (select
            3  xmltype('<?xml version="1.0" encoding="utf-8" ?>
            4  <Rowsets DateCreated="2010-11-30T11:12:10" EndDate="2010-06-05T16:52:23" StartDate="2010-06-05T16:52:23" Version="12.0.10 Build(18)">
            5  <Rowset>
            6  <Columns>
            7  <Column Description="Material Number" MaxRange="1" MinRange="0" Name="MATERIAL" SQLDataType="1" SourceColumn="MATERIAL"/>
            8  <Column Description="" MaxRange="1" MinRange="0" Name="TANK" SQLDataType="1" SourceColumn="TANK"/>
            9  </Columns>
           10  <Row>
           11  <MATERIAL>1000000144</MATERIAL>
           12  <TANK>T1000</TANK>
           13  </Row>
           14  <Row>
           15  <MATERIAL>2000000008</MATERIAL>
           16  <TANK>T1000</TANK>
           17  </Row>
           18  <Row>
           19  <MATERIAL>2000000009</MATERIAL>
           20  <TANK>T1000</TANK>
           21  </Row>
           22  <Row>
           23  <MATERIAL>2000000016</MATERIAL>
           24  <TANK>T1000</TANK>
           25  </Row>
           26  <Row>
           27  <MATERIAL>3000000036</MATERIAL>
           28  <TANK>T1000</TANK>
           29  </Row>
           30  </Rowset></Rowsets>') TEXT from dual)
           31  SELECT RW.MATERIAL
           32  FROM MASTERTB TM,
           33  XMLTable('//Row' PASSING TM.TEXT
           34  COLUMNS "MATERIAL" CHAR(30) PATH 'MATERIAL') AS RW
           35  /
           
          MATERIAL
          ------------------------------
          1000000144
          2000000008
          2000000009
          2000000016
          3000000036
           
          SQL> 
          SQL> select * from v$version where rownum=1;
           
          BANNER
          --------------------------------------------------------------------------------
          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
           
          SQL> 
          SQL> with MASTERTB as
            2  (select
            3  xmltype('<?xml version="1.0" encoding="utf-8" ?>
            4  <Rowsets DateCreated="2010-11-30T11:12:10" EndDate="2010-06-05T16:52:23" StartDate="2010-06-05T16:52:23" Version="12.0.10 Build(18)">
            5  <Rowset>
            6  <Columns>
            7  <Column Description="Material Number" MaxRange="1" MinRange="0" Name="MATERIAL" SQLDataType="1" SourceColumn="MATERIAL"/>
            8  <Column Description="" MaxRange="1" MinRange="0" Name="TANK" SQLDataType="1" SourceColumn="TANK"/>
            9  </Columns>
           10  <Row>
           11  <MATERIAL>1000000144</MATERIAL>
           12  <TANK>T1000</TANK>
           13  </Row>
           14  <Row>
           15  <MATERIAL>2000000008</MATERIAL>
           16  <TANK>T1000</TANK>
           17  </Row>
           18  <Row>
           19  <MATERIAL>2000000009</MATERIAL>
           20  <TANK>T1000</TANK>
           21  </Row>
           22  <Row>
           23  <MATERIAL>2000000016</MATERIAL>
           24  <TANK>T1000</TANK>
           25  </Row>
           26  <Row>
           27  <MATERIAL>3000000036</MATERIAL>
           28  <TANK>T1000</TANK>
           29  </Row>
           30  </Rowset></Rowsets>') TEXT from dual)
           31  SELECT RW.MATERIAL
           32  FROM MASTERTB TM,
           33  XMLTable('//Row' PASSING TM.TEXT
           34  COLUMNS "MATERIAL" CHAR(30) PATH 'MATERIAL') AS RW
           35  /
           
          MATERIAL
          ------------------------------
          1000000144
          2000000008
          2000000009
          2000000016
          3000000036
           
          SQL> 
          try
           31  SELECT RW.MATERIAL
           32  FROM MASTERTB TM,
           33  XMLTable('Rowsets/Rowset/Row' PASSING TM.TEXT
           34  COLUMNS "MATERIAL" varchar2(30) PATH 'MATERIAL') AS RW
           35  /
           
          MATERIAL
          ------------------------------
          1000000144
          2000000008
          2000000009
          2000000016
          3000000036
           
          SQL> 
          • 2. Re: XMLTABLE returns only first row
            TrojanSpirit
            Sorry I forgot to add <Rowsets> in the XML. My apologies. I will correct that.

            Actually, I am running the query in SAP MII Query Editor and somehow it is giving me only first MATERIAL.

            Do you know how can I see what XML is stored in the MASTERTB's TEXT column because when I d Select *from MASTERTB, it just gives me "---" in TEXT column. SOmehow select statement is not able to convert XMLTYPE into String. So can you help me extract the whole XML stored in TEXT column.

            Thanks for the help !
            • 3. Re: XMLTABLE returns only first row
              odie_63
              Do you know how can I see what XML is stored in the MASTERTB's TEXT column because when I d Select *from MASTERTB, it just gives me "---" in TEXT column. SOmehow select statement is not able to convert XMLTYPE into String. So can you help me extract the whole XML stored in TEXT column.
              Do you have access to another Oracle client tool, such as SQL*Plus or SQL Developer?
              It would be useful to test your queries on either of those to first rule out any issues related to the database.

              Regarding your question about extracting the whole XML content, the answer depends on the database version, which you didn't mention.

              Prior to 11g, use getClobVal (or getStringVal) method :
              SELECT tm.text.getClobVal()
              FROM MASTERTB tm
              The above is deprecated in 11.2, so if you're on 11g :
              SELECT XMLSerialize(document tm.text)
              FROM MASTERTB tm
              http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb13gen.htm#ADXDB5033
              • 4. Re: XMLTABLE returns only first row
                TrojanSpirit
                Hi,

                Using your query to extract the whole XML,

                SELECT tm.text.getClobVal()
                FROM MASTERTB tm

                I found that I inserted only one MATERIAL tag in the XML and hence it was returning on,y one MATERIAL.

                I inserted the full XML and tried the query again and I was able to get all the MATERIAL.

                Thanks a lot for the hellp.