XMLTABLE solution without getting right parenthesis missing error

oracleoracle-12cxml

I am trying to select from an XML type and produce multiple rows. Have searched for my specific scenario/issue with not much success. Environment: Oracle 12c. I have commented out the offending code.

WITH XML_DATA AS  
    (SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
    <response>
        <phone>
            <number>859111</number>
            <contactModes>
                <mode>AUTO_DIAL</mode>
                <mode>AUTO_TEXT</mode>
                <mode>MANUAL_DIAL</mode>
            </contactModes>
            <contactableTimeRange>
                <day1>
                    <startTime>08:00:00</startTime>
                  <endTime>20:00:00</endTime>
                </day1>
                <day2>
                    <startTime>08:00:00</startTime>
                    <endTime>20:00:00</endTime>
                </day2>
            </contactableTimeRange>
        </phone>
    </response>') doc
    FROM DUAL)
SELECT X.phone
    ,x3.d1starttime
    ,x3.d1endtime
    /*,x4.* */
    FROM XML_DATA XD,  
    XMLTABLE('/response/phone' PASSING XD.DOC  
         COLUMNS phone  VARCHAR2(36) PATH 'number',  
                CONTACT_MODES XMLTYPE PATH 'contactModes',  
                CONTACT_TIMES XMLTYPE PATH 'contactableTimeRange')  X  
      /*  , XMLTABLE('/contactModes' 
       passing x.contact_modes
       COLUMNS  modes VARCHAR2(10) 'node' ) AS  x2*/
       ,XMLTABLE('/contactableTimeRange/day1' PASSING X.CONTACT_TIMES  
                COLUMNS D1STARTTIME VARCHAR2(10) PATH 'startTime',  
                        D1ENDTIME VARCHAR2(10) PATH 'endTime') AS X3  
       ,XMLTABLE('/contactableTimeRange/day2' PASSING X.CONTACT_TIMES  
                COLUMNS D2STARTTIME VARCHAR2(10) PATH 'startTime',  
                D2ENDTIME VARCHAR2(10) PATH 'endTime') X4;

I would like the query the return the following records. The mode values are not static, could be more or less. And same thing for the days. Could be day1 ..dayn

Phone      MODE      DAY    STARTTIME     ENDTIME  
859111    AUTO_DIAL   1    08:00:00  20:00:00  
859111    AUTO_DIAL   2    08:00:00  20:00:00  
859111    AUTO_TEXT   1    08:00:00  20:00:00  
859111    AUTO_TEXT   2    08:00:00  20:00:00  
859111    MANUAL_DIAL 1    08:00:00  20:00:00  
859111    MANUAL_DIAL 2    08:00:00  20:00:00

Best Answer

You might have wanted this:

WITH XML_DATA AS
(SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
<response>
    <phone>
        <number>859111</number>
        <contactModes>
            <mode>AUTO_DIAL</mode>
            <mode>AUTO_TEXT</mode>
            <mode>MANUAL_DIAL</mode>
        </contactModes>
        <contactableTimeRange>
            <day1>
                <startTime>08:00:00</startTime>
              <endTime>20:00:00</endTime>
            </day1>
            <day2>
                <startTime>08:00:00</startTime>
                <endTime>20:00:00</endTime>
            </day2>
        </contactableTimeRange>
    </phone>
</response>') doc
FROM DUAL)
SELECT X.phone
,x2."MODE"
,To_Number (SubStr (x3.Day, 4)) Day
,x3.starttime
,x3.endtime
/*,x4.* */
FROM XML_DATA XD,
XMLTABLE('/response/phone' PASSING XD.DOC
     COLUMNS phone  VARCHAR2(36) PATH 'number',
            CONTACT_MODES XMLTYPE PATH 'contactModes',
            CONTACT_TIMES XMLTYPE PATH 'contactableTimeRange')  X
   , XMLTABLE('/contactModes/mode'
   passing x.contact_modes
   COLUMNS "MODE" VARCHAR2(10) Path '.' ) AS  x2
   ,XMLTABLE('/contactableTimeRange/*' PASSING X.CONTACT_TIMES
            COLUMNS Day VARCHAR2(10) PATH 'name()',
                    STARTTIME VARCHAR2(10) PATH 'startTime',
                    ENDTIME VARCHAR2(10) PATH 'endTime') AS X3

Which produces:

    PHONE                                MODE              DAY STARTTIME  ENDTIME
    ------------------------------------ ---------- ---------- ---------- --------
    859111                               AUTO_DIAL           1 08:00:00   20:00:00
    859111                               AUTO_DIAL           2 08:00:00   20:00:00
    859111                               AUTO_TEXT           1 08:00:00   20:00:00
    859111                               AUTO_TEXT           2 08:00:00   20:00:00
    859111                               MANUAL_DIA          1 08:00:00   20:00:00
    859111                               MANUAL_DIA          2 08:00:00   20:00:00
Related Question