How do I search for a specific XML value in Oracle SQL?

user_16956822 0
Viewed 171

I'm trying to search for a given value in a XML binary type column in a SQL table (BLOB).

The table has +3000 records. I need to look for all the values that corresponds to a specific IP in /properties/ip/ from an XML that looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<properties>
   <port>8000</port>
   <ip>12.345.67.80</ip>
</properties>

So far, I've been able to get some output but not what I needed (I could only run the query with the last WHERE claus, not without):

SELECT
    d.*,
    extractvalue(xmltype(d.CONFIGURATION,nls_charset_id('AL32UTF8')),'properties/ip') as ip_lookup
FROM
    AZ.DEVICES d
WHERE
    NAME = 'Door-Entrance';

I am interesting in looking for all records with specific IP's, so something like this:

SELECT
    d.*,
    extractvalue(xmltype(d.CONFIGURATION,nls_charset_id('AL32UTF8')),'properties/ip') as ip_lookup
FROM
    AZ.DEVICES d
WHERE
    ip_lookup = '12.345.67.80';

Any ideas?

blob oracle xml sql
2 Answers

You can use XMLTABLE which is recommended by Oracle rather than deprecated functions XMLSEQUENCE or EXTRACTVALUE such as

WITH devices(configuration,name) AS
 (SELECT XMLType('<?xml version="1.0" encoding="UTF-8"?>
        <properties>
           <port>8000</port>
           <ip>12.345.67.80</ip>
        </properties>'),
         'Door-Entrance'
    FROM dual)
SELECT ip
  FROM devices,
       XMLTABLE('/properties' PASSING devices.configuration COLUMNS "IP"
                VARCHAR2(50) PATH 'ip')
 WHERE name = 'Door-Entrance'

Demo

user_58413060

You can use XMLTABLE and either filter in the WHERE clause:

SELECT *
FROM   AZ.devices d
       CROSS APPLY XMLTABLE(
         '/properties'
         PASSING XMLTYPE(d.configuration,nls_charset_id('UTF8'))
         COLUMNS
           port NUMBER(5,0)  PATH './port',
           ip   VARCHAR2(15) PATH './ip'
       ) x
WHERE  x.ip = '12.345.67.80';

Or in the XPath:

SELECT *
FROM   AZ.devices d
       CROSS APPLY XMLTABLE(
         '/properties[ip="12.345.67.80"]'
         PASSING XMLTYPE(d.configuration,nls_charset_id('UTF8'))
         COLUMNS
           port NUMBER(5,0)  PATH './port',
           ip   VARCHAR2(15) PATH './ip'
       ) x

Which, for the sample data:

CREATE TABLE AZ.devices (
  name          VARCHAR2(30),
  configuration BLOB
);

INSERT INTO AZ.devices (name, configuration)
VALUES (
  'Door-Entrance',
  UTL_RAW.CAST_TO_RAW('<?xml version="1.0" encoding="UTF-8"?>
<properties>
   <port>8000</port>
   <ip>12.345.67.80</ip>
</properties>')
);

Both output:

NAME CONFIGURATION PORT IP_LOOKUP
Door-Entrance <?xml version="1.0" encoding="UTF-8"?>
<properties>
   <port>8000</port>
   <ip>12.345.67.80</ip>
</properties>
 8000 12.345.67.80

db<>fiddle here

user_15092640
Related