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

Look here, this may help: asktom.oracle.com/pls/apex/…

Art
– Art

2018-05-21 16:34:51 +00:00
Commented May 21, 2018 at 16:34 — Art
– Art, Commented May 21, 2018 at 16:34

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

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.

I need to process them in the same way. So the first answer is really perfect for me and works flawlessly. But the second one will be good in other cases. Thank you very much!!

Collectives™ on Stack Overflow

ORACLE SQL select values from XML

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related