0

I'm using Xmltable to convert a field of comma-delimited email addresses to a table of values.

WITH

data AS
  (
    select  1 ID, 'foo&bar@domain.tld,bar@domain.tld' recipients from dual
  )

select  ID, trim(COLUMN_VALUE) recipient
from    data,xmltable(('"'|| REPLACE( recipients , ',', '","') || '"'))

produces an error:

[72000][19112] ORA-19112: error raised during evaluation: XVM-01003: [XPST0003] Syntax error at '"foo' 1 "foo&bar@domain.tld","bar@domain.tld" - ^

However, when I replace the & with its entity value (&):

WITH

DATA AS
  (
    select  1 ID, 'foo&bar@domain.tld,bar@domain.tld' recipients from dual
  )

select  ID
        -- & --> &
        , replace( trim(COLUMN_VALUE), '&', '&') recipient
from    data
        -- & --> &
        ,xmltable(('"'|| REPLACE( replace( recipients, '&','&')  , ',', '","') || '"'))

the query works:

ID,RECIPIENT
1,foo&bar@domain.tld
1,bar@domain.tld

I'm imaging that there might be other characters that are valid in an email address, but will be problematic for Xmltable.

Is there a better way to do this?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
craig
  • 25,664
  • 27
  • 119
  • 205
  • 1
    Since you asked about better ways, there's a number of other options for splitting CSV to tables. My favorite is MT0's CTE on this question: https://stackoverflow.com/questions/38371989/how-to-convert-comma-separated-values-to-rows-in-oracle If you go with a regexp, be aware of the null problem. https://stackoverflow.com/questions/25648653/regex-to-select-nth-value-from-a-list-allowing-for-nulls – kfinity Mar 28 '19 at 16:59

1 Answers1

2

You could use the built-in dbms_xmlgen.convert() function:

with data (id, recipients) as
(
  select 1, 'foo&bar@domain.tld,bar@domain.tld' from dual
)
select d.id, dbms_xmlgen.convert(x.column_value.getstringval(), 1) as recipient
from data d
cross join
xmltable(('"' || replace(dbms_xmlgen.convert(d.recipients, 0), ',', '","') || '"')) x

        ID RECIPIENT                     
---------- ------------------------------
         1 foo&bar@domain.tld            
         1 bar@domain.tld

The inner call dbms_xmlgen.convert(d.recipients, 0) gives you

foo&bar@domain.tld,bar@domain.tld

After that has been modified to have double quotes around each comma-separated value and been split into multiple rows, you end up with column_value as:

foo&bar@domain.tld
bar@domain.tld

so the outer dbms_xmlgen.convert(x.column_value.getstringval(), 1) converts any encoded entities back to their plain versions.

If you do this in a PL/SQL context then you can use dbms_xmlgen.entity_encode and dbms_xmlgen.entity_decode instead of the fixed 0 and 1, but those aren't available in plain SQL.

(There are only five entities to worry about anyway, but you still might as well allow for them all - whether they are valid in email addresses or not - and using a function call like this is maybe going to be less confusing to future maintainers...)

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • The `dbms_xmlgen.convert(d.recipients, 0)` works correctly. The `dbms_xmlgen.convert(x.column_value.getstringval(), 1) ` generates an error `42000][904] ORA-00904: "X"."COLUMN_VALUE"."GETSTRINGVAL": invalid identifier`. – craig Mar 29 '19 at 00:32
  • @craig - this works exactly as posted in 11.2.0.4 and later (including [in 18c](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=fb9950bb8fa48167fcdc21cc9e0c28e7)); it doesn't work [in 11.2.0.2](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=3120127158401aaaa00a1d1efce30b96) but for a different reason. Unclear why it would matter but which version are you on? (I've just noticed that the `x` alias for the XMLTable expression might have required horizontal scrolling to be visible - I've split that onto a new line to stop that, just in case you'd just missed it...) – Alex Poole Mar 29 '19 at 09:39