Concatenate XML nodes using XPath and XQuery in Oracle

Question

I have a XML data stored in CLOB column as follows:

<row id='123456' xml:space='preserve'>
<name>Martin H</name>
<phone>1111</phone>
<phone m='2'>2222</phone>
<phone m='3'></phone>
<sms m='2'>1212</sms>
<sms m='3'>2323</sms>
<email>[email protected]</email>
<email m='3'>[email protected]</email></row>

How can I use Xpath and XQuery in Oracle DB to have the following expected output in only one row:

Name	Phone	Sms	Email
Martin H	1111#2222#	#1212#2323	[email protected]##[email protected]

Basically, a '#' is used to separate values and any missing tag of any group from XML above is returned as a null value.

Any help will be much appreciated. Thanks!

Why does #1212#2323 in your result for Sms have a leading #? There is no sms element with a null value? — EJ Egyed
– EJ Egyed, Commented Jul 14, 2022 at 18:40
@EJEgyed OP wants to treat missing tags the same as empty tags - in this case, pretend there's a <sms></sms> with no @m value — kfinity
– kfinity, Commented Jul 14, 2022 at 18:46
@kfinity that's correct. Any missing tag should be treated as empty one. It should be assign NULL value instead of being ignored. However, if the xml is not fixed but dynamic (one more and many more phone/sms/email is added later), do you have any suggestion? Many thanks! — Martin H
– Martin H, Commented Jul 15, 2022 at 2:01

EJ Egyed · Accepted Answer · 2022-07-14 19:14:27Z

Using XMLTABLE to parse your XML into columns, you can then use the string-join operator when defining the path to perform a similar functionality to LISTAGG.

WITH
    example_table (xml_clob)
    AS
        (SELECT EMPTY_CLOB () || '<row id=''123456'' xml:space=''preserve''>
    <name>Martin H</name>
    <phone>1111</phone>
    <phone m=''2''>2222</phone>
    <phone m=''3''></phone>
    <sms m=''2''>1212</sms>
    <sms m=''3''>2323</sms>
    <email>[email protected]</email>
    <email m=''3''>[email protected]</email>
</row>' FROM DUAL)
SELECT xt.*
  FROM example_table  et
       CROSS JOIN
       XMLTABLE (
           'row'
           PASSING xmltype (et.xml_clob)
           COLUMNS name VARCHAR2 (100)
                       PATH 'name',
                   phone VARCHAR2 (100)
                       PATH 'string-join(phone,"#")',
                   sms VARCHAR2 (100)
                       PATH 'string-join(sms,"#")',
                   email VARCHAR2 (100)
                       PATH 'string-join(email,"#")') xt;

NAME        PHONE         SMS          EMAIL
___________ _____________ ____________ ________________________________
Martin H    1111#2222#    1212#2323    [email protected]#[email protected]

Update

After reading kfinity's comment and understanding the problem a bit clearer, the problem can still be solved using XMLTABLE, then CASE statements to contatenate the values together for the desired result.

WITH
    example_table (xml_clob)
    AS
        (SELECT EMPTY_CLOB () || '<row id=''123456'' xml:space=''preserve''>
    <name>Martin H</name>
    <phone>1111</phone>
    <phone m=''2''>2222</phone>
    <phone m=''3''></phone>
    <sms m=''2''>1212</sms>
    <sms m=''3''>2323</sms>
    <email>[email protected]</email>
    <email m=''3''>[email protected]</email>
</row>' FROM DUAL)
SELECT xt.name,
          CASE
              WHEN phone1_mtag IS NULL AND phone1 IS NOT NULL THEN phone1
              WHEN phone2_mtag IS NULL AND phone2 IS NOT NULL THEN phone2
              WHEN phone3_mtag IS NULL AND phone3 IS NOT NULL THEN phone3
          END
       || '#'
       || CASE
              WHEN phone1_mtag = '2' AND phone1 IS NOT NULL THEN phone1
              WHEN phone2_mtag = '2' AND phone2 IS NOT NULL THEN phone2
              WHEN phone3_mtag = '2' AND phone3 IS NOT NULL THEN phone3
          END
       || '#'
       || CASE
              WHEN phone1_mtag = '3' AND phone1 IS NOT NULL THEN phone1
              WHEN phone2_mtag = '3' AND phone2 IS NOT NULL THEN phone2
              WHEN phone3_mtag = '3' AND phone3 IS NOT NULL THEN phone3
          END    AS phone,
          CASE
              WHEN sms1_mtag IS NULL AND sms1 IS NOT NULL THEN sms1
              WHEN sms2_mtag IS NULL AND sms2 IS NOT NULL THEN sms2
              WHEN sms3_mtag IS NULL AND sms3 IS NOT NULL THEN sms3
          END
       || '#'
       || CASE
              WHEN sms1_mtag = '2' AND sms1 IS NOT NULL THEN sms1
              WHEN sms2_mtag = '2' AND sms2 IS NOT NULL THEN sms2
              WHEN sms3_mtag = '2' AND sms3 IS NOT NULL THEN sms3
          END
       || '#'
       || CASE
              WHEN sms1_mtag = '3' AND sms1 IS NOT NULL THEN sms1
              WHEN sms2_mtag = '3' AND sms2 IS NOT NULL THEN sms2
              WHEN sms3_mtag = '3' AND sms3 IS NOT NULL THEN sms3
          END    AS sms,
          CASE
              WHEN email1_mtag IS NULL AND email1 IS NOT NULL THEN email1
              WHEN email2_mtag IS NULL AND email2 IS NOT NULL THEN email2
              WHEN email3_mtag IS NULL AND email3 IS NOT NULL THEN email3
          END
       || '#'
       || CASE
              WHEN email1_mtag = '2' AND email1 IS NOT NULL THEN email1
              WHEN email2_mtag = '2' AND email2 IS NOT NULL THEN email2
              WHEN email3_mtag = '2' AND email3 IS NOT NULL THEN email3
          END
       || '#'
       || CASE
              WHEN email1_mtag = '3' AND email1 IS NOT NULL THEN email1
              WHEN email2_mtag = '3' AND email2 IS NOT NULL THEN email2
              WHEN email3_mtag = '3' AND email3 IS NOT NULL THEN email3
          END    AS email
  FROM example_table  et
       CROSS JOIN XMLTABLE ('row'
                            PASSING xmltype (et.xml_clob)
                            COLUMNS name VARCHAR2 (100) PATH 'name',
                                    phone1 VARCHAR2 (100) PATH 'phone[1]',
                                    phone1_mtag VARCHAR2 (100) PATH 'phone[1]/@m',
                                    phone2 VARCHAR2 (100) PATH 'phone[2]',
                                    phone2_mtag VARCHAR2 (100) PATH 'phone[2]/@m',
                                    phone3 VARCHAR2 (100) PATH 'phone[3]',
                                    phone3_mtag VARCHAR2 (100) PATH 'phone[3]/@m',
                                    sms1 VARCHAR2 (100) PATH 'sms[1]',
                                    sms1_mtag VARCHAR2 (100) PATH 'sms[1]/@m',
                                    sms2 VARCHAR2 (100) PATH 'sms[2]',
                                    sms2_mtag VARCHAR2 (100) PATH 'sms[2]/@m',
                                    sms3 VARCHAR2 (100) PATH 'sms[3]',
                                    sms3_mtag VARCHAR2 (100) PATH 'sms[3]/@m',
                                    email1 VARCHAR2 (100) PATH 'email[1]',
                                    email1_mtag VARCHAR2 (100) PATH 'email[1]/@m',
                                    email2 VARCHAR2 (100) PATH 'email[2]',
                                    email2_mtag VARCHAR2 (100) PATH 'email[2]/@m',
                                    email3 VARCHAR2 (100) PATH 'email[3]',
                                    email3_mtag VARCHAR2 (100) PATH 'email[3]/@m') xt;

NAME        PHONE         SMS           EMAIL
___________ _____________ _____________ _________________________________
Martin H    1111#2222#    #1212#2323    [email protected]##[email protected]

Thanks @EJ Egyed for your prompt comment. Appreciate that. Would you suggest in case the group of tags is dynamic (i.e. one more phone/sms/email is added)?

Collectives™ on Stack Overflow

Concatenate XML nodes using XPath and XQuery in Oracle

1 Answer 1

Update

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related