developers

    Forum Stats

  • 3,873,852 Users
  • 2,266,621 Discussions
  • 7,911,645 Comments

Discussions

XMLTABLE returns only first row

TrojanSpirit
TrojanSpirit Member Posts: 27
edited Oct 30, 2012 5:56PM in XQuery
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

Best Answer

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    Answer ✓
    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

Answers

  • AlexAnd
    AlexAnd Member Posts: 2,554 Gold Trophy
    >
    <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> 
    AlexAnd
  • 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 !
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    Answer ✓
    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
  • 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.
This discussion has been closed.
developers