ORACLE SQL select values from XML

Question

How can I select values from XML in ORACLE SQL?

Let's say I have two XMLs in table "items":

first row and column "intreevalue" data

<change xmlns="http://do.it.com/ch/PLP">
  <header>
    <op>XDD</op>
  </header>
  <id>10565</id>
</change>

and second row and column "intreevalue" data

<deactivate xmlns="http://mrlo.com/nnu/XCBouIK">
  <header>
    <op>XDG</id>
  </header>
  <id>67544</id>
</deactivate>

I know "EXTRACTVALUE" is depricated. I need values "op" and "id" in one select. I guess if there is no namespace like 'xmlns="http://do.it.com/ch/PLP"' it would be easy. But can I select data like this (example - I know it can't work, just trying to say what i mean)?

select "/*/header/op" op, "/*/id" id from .....

Thanks

sticky bit sticky bit · Accepted Answer · 2018-05-21T16:30:00

I see two options:

Use REGEXP_REPLACE() to remove all xmlns attributes from all tags.

SELECT EXTRACT(XMLTYPE(REGEXP_REPLACE(items.intreevalue,
                                      '(<[^>]+[[:space:]]+)xmlns(?::[^[:space:]=]+)?[:space:]*=[:space:]*"[^"]*"',
                                      '\1')),
               '/*/header/op/text()')
       .GETSTRINGVAL() op,
       EXTRACT(XMLTYPE(REGEXP_REPLACE(items.intreevalue,
                                      '(<[^>]+[[:space:]]+)xmlns(?::[^[:space:]=]+)?[:space:]*=[:space:]*"[^"]*"',
                                      '\1')),
               '/*/id/text()')
       .GETNUMBERVAL() id
       FROM items;

But it might be a problem if you actually need to treat some elements from different name spaces differently. Maybe you can then tweak the regular expression to remove only "problematic" name space attributes but keeping others.

Use a large CASE ... END to check each possible namespace and take the first one you get a (not NULL) result for.

SELECT CASE
         WHEN EXTRACT(items.intreevalue,
                      '/*/header/op/text()',
                      'xmlns="http://do.it.com/ch/PLP"')
              .GETSTRINGVAL() IS NOT NULL
           THEN EXTRACT(items.intreevalue,
                        '/*/header/op/text()',
                        'xmlns="http://do.it.com/ch/PLP"')
                .GETSTRINGVAL()
         WHEN EXTRACT(items.intreevalue,
                      '/*/header/op/text()',
                      'xmlns="http://mrlo.com/nnu/XCBouIK"')
              .GETSTRINGVAL() IS NOT NULL
           THEN EXTRACT(items.intreevalue,
                        '/*/header/op/text()',
                        'xmlns="http://mrlo.com/nnu/XCBouIK"')
                .GETSTRINGVAL()
       END op,
       CASE
         WHEN EXTRACT(items.intreevalue,
                      '/*/id/text()',
                      'xmlns="http://do.it.com/ch/PLP"')
              .GETNUMBERVAL() IS NOT NULL
           THEN EXTRACT(items.intreevalue,
                        '/*/id/text()',
                        'xmlns="http://do.it.com/ch/PLP"')
                .GETNUMBERVAL()
         WHEN EXTRACT(items.intreevalue,
                      '/*/id/text()',
                      'xmlns="http://mrlo.com/nnu/XCBouIK"')
              .GETNUMBERVAL() IS NOT NULL
           THEN EXTRACT(items.intreevalue,
                        '/*/id/text()',
                        'xmlns="http://mrlo.com/nnu/XCBouIK"')
                .GETNUMBERVAL()
       END id
       FROM items;

If there is a namespace not included in the case you'd still get NULLs for the respective XML.

ORACLE SQL select values from XML

1 Answers