DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

XMLTABLE returns only first row

TrojanSpiritOct 25 2012 — edited Oct 30 2012

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

This post has been answered by odie_63 on Oct 27 2012

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 27 2012

Added on Oct 25 2012

#xml, #xquery

4 comments

3,669 views