CODEWITHSUNDEEP
sqlxmloracle-database
kubrdom
kubrdom

Reputation: 35

ORACLE SQL select values from XML

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

Upvotes: 1

Views: 319

Answers (1)

sticky bit
sticky bit

Reputation: 37472

I see two options:

  1. 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.

  2. 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.

Upvotes: 1

Related Questions