How to return similar xml elements as rows in oracle

Question

I am trying to parse xml in my oracle DB to return individual rows. The xml is a field called msg in my table Sample xml is

<application  xmlns="http://www.abcxyz.com/Schema/FCX/1" 
    xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">
     <client>
        <clientSource>
          <amount>25000.0</amount>
          <clientSourceTypeDd>1</clientSourceTypeDd>
          <description>aasdadsf</description>
        </clientSource>
        <clientSource>
          <amount>25000.0</amount>
          <clientSourceType>6</clientSourceTypeDd>
          <description>wrewertwerewrt</description>
        </clientSource>
        <clientSource>
          <amount>50000.0</amount>
          <clientSourceType>10</clientSourceTypeDd>
          <description>second and thirs</description>
        </clientSource>
    </client>
</application>

I have tried the below query but not working as expected

SELECT EXTRACT(t.msg, '//application/client/clientSource[*]/clientSourceType/text()')
       .getStringVal() clientSourceType,
       EXTRACT(t.msg, '/pplication/client/clientSource/amount')
       .getStringVal() clientSourceAmount
  FROM table t

The expected result I want to achieve is

clientSourceType	clientSourceAmount
1	25000
6	25000
10	50000

Please help resolve as I am new to parse xml and oracle. Thanks

Answer 1

You can use XMLTABLE, which is recommended by Oracle rather than deprecated function EXTRACTVALUE, after fixing tag names by converting clientSourceTypeDd to clientSourceType in order to make opening and closing tag names match such as

WITH t( xml ) AS
(
 SELECT XMLType('<application xmlns="http://www.abcxyz.com/Schema/FCX/1" 
                     xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">
                     <client>
                        <clientSource>
                          <amount>25000.0</amount>
                          <clientSourceType>1</clientSourceType>
                          <description>aasdadsf</description>
                        </clientSource>
                        <clientSource>
                          <amount>25000.0</amount>
                          <clientSourceType>6</clientSourceType>
                          <description>wrewertwerewrt</description>
                        </clientSource>
                        <clientSource>
                          <amount>50000.0</amount>
                          <clientSourceType>10</clientSourceType>
                          <description>second and thirs</description>
                        </clientSource>
                    </client>
                 </application>')
   FROM dual
)
SELECT "clientSourceType", "clientSourceAmount"
  FROM t,
  XMLTABLE( XMLNAMESPACES( DEFAULT 'http://www.abcxyz.com/Schema/FCX/1' ),
            '/application/client/clientSource'
      PASSING xml
      COLUMNS 
             "clientSourceType"   INT PATH 'clientSourceType',
             "clientSourceAmount" INT PATH 'amount'
         )

clientSourceType	clientSourceAmount
1	25000
6	25000
10	50000

Demo