How to return similar xml elements as rows in oracle

user_15836000 0
Viewed 50

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

xml-parsing oracle11g oracle xml sql
1 Answers

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

user_58413060
Related