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

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

CodePudding user response：

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'
         )

Demo