Oracle XML Query with multiple child nodes-CodePudding

I would like to achieve a query on XML Element but could not find a proper way to achieve this.

Let's consider this table:

CREATE TABLE SUBSCRIPTIONS(
SUBSCRIPTION_ID NUMBER,
PARAMETERS_XML  CLOB
);

My XML looks like:

<Model>
  <Parameters>
    <Parameter>
      <userParameter>outlets</userParameter>
       <Values>
         <Value>45</Value>
         <Value>676</Value>
         <Value>1502</Value>
         ...
       </Values>
    </Parameter>
  </Parameters>
</Model>

I'm trying for each subscription to get from the XML, the parameter name, and the parameters values, using this query:

SELECT
s.SUBSCRIPTION_ID,
s.PARAMETERS_XML,
xt.*
FROM EFNTA.SUBSCRIPTIONS s,
XMLTABLE(
    '/Model/Parameters/Parameter'
    PASSING XMLTYPE(s.PARAMETERS_XML)
    COLUMNS USER_PARAM  VARCHAR2(100)  PATH 'userParameter',
    PARAM_VALUE VARCHAR2(1000) PATH '//*:Values',
    CHECKED_LIST_EXCLUDE VARCHAR2(100) PATH '//*:CheckedListExclude'                        
) xt ORDER BY 1 DESC;

Issue is that when there are multiple values, the result is returned as one big concatenated chain:

Is there any easy way to separate theses values with a comma, semicolon, or whatever ? (I don't know the number of values in advance) I couln't find how to achieve this with XPath.

CodePudding user response：

You could use FLOWR to rewrite the XML to to incude a trailing , in each Value element:

SELECT s.SUBSCRIPTION_ID,
       s.PARAMETERS_XML,
       xt.User_Param,
       RTRIM(xt.Param_Value, ',') AS Param_Value,
       xt.Checked_List_Exclude
FROM   EFNTA.SUBSCRIPTIONS s
       CROSS JOIN
       XMLTABLE(
         'copy $e := .
         modify (
           for $i in $e/Model/Parameters/Parameter/Values/Value
           return replace node $i with <Value>{$i},</Value>
         )
         return $e/Model/Parameters/Parameter'
         PASSING XMLTYPE(s.PARAMETERS_XML)
         COLUMNS
           USER_PARAM  VARCHAR2(100)  PATH 'userParameter',
           PARAM_VALUE VARCHAR2(1000) PATH '//*:Values',
           CHECKED_LIST_EXCLUDE VARCHAR2(100) PATH '//*:CheckedListExclude'
        ) xt
ORDER BY 1 DESC;

Or, as suggested by

Thanks for your suggestions, I'll keep all this under my sleeve, in case of :-)