Oracle XML Query with multiple child nodes

Question

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:

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.

user_1509264 · Answer

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 {$i},
         )
         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 Pavel Koryakin, use string-join():

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

Which both output:

SUBSCRIPTION_ID	PARAMETERS_XML	USER_PARAM	PARAM_VALUE	CHECKED_LIST_EXCLUDE
1	outlets 45 676 1502	outlets	45,676,1502	null

db<>fiddle here

user_266304 · Answer

You could split the Values handling out to a second XMLTable:

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

SUBSCRIPTION_ID	USER_PARAM	PARAM_VALUE	CHECKED_LIST_EXCLUDE
553219	outlets	45	null
553219	outlets	1502	null
553219	outlets	676	null

And you can then aggregate the individual values:

SELECT
    s.SUBSCRIPTION_ID,
    xt1.USER_PARAM,
    LISTAGG(xt2.PARAM_VALUE, ',') WITHIN GROUP (ORDER BY xt2.PARAM_VALUE) as PARAM_VALUES,
    xt1.CHECKED_LIST_EXCLUDE
FROM SUBSCRIPTIONS s
CROSS APPLY XMLTABLE(
    '/Model/Parameters/Parameter'
    PASSING XMLTYPE(s.PARAMETERS_XML)
    COLUMNS USER_PARAM  VARCHAR2(100)  PATH 'userParameter',
    PARAM_VALUES XMLTYPE PATH '//*:Values',
    CHECKED_LIST_EXCLUDE VARCHAR2(100) PATH '//*:CheckedListExclude'
) xt1
OUTER APPLY XMLTABLE(
    '/*:Values/Value'
    PASSING xt1.PARAM_VALUES
    COLUMNS PARAM_VALUE VARCHAR2(1000) PATH '.'
) xt2
GROUP BY
    s.SUBSCRIPTION_ID,
    xt1.USER_PARAM,
    xt1.CHECKED_LIST_EXCLUDE
ORDER BY 1 DESC;

SUBSCRIPTION_ID	USER_PARAM	PARAM_VALUES	CHECKED_LIST_EXCLUDE
553219	outlets	1502,45,676	null

db<>fiddle

Your example has no namespaces so the wildcarding looks unnecessary (and I'd generally avoid wildcards anyway if possible), but I've left them in case you've simplified it.

I changed the first join to cross apply which means you'll still only see rows where parameters_xml is present, as in your original; but made the second outer apply so you will still see the parameter name even if there are no values.

I've also kind of ignored CHECKED_LIST_EXCLUDE as the example doesn't have that; if that can also have multiple values then you might need to do a similar thing for that.

user_2635942 · Answer

Try using string-join function: 'string-join(Values//Value, ", ")'.

user_5435353 · Answer

I finally handled it that way:

SELECT
s.SUBSCRIPTION_ID,
s.PARAMETERS_XML,
xt.USER_PARAM,
xt.CHECKED_LIST_EXCLUDE,
NVL(xt.PARAM_VALUE, xt.PARAM_VALUES) AS PARAM_VALUES
FROM EFNTA.SUBSCRIPTIONS s,
XMLTABLE(
    '/Model/Parameters/Parameter'
     PASSING XMLTYPE(s.PARAMETERS_XML)
     COLUMNS USER_PARAM  VARCHAR2(100)  PATH 'userParameter',
             CHECKED_LIST_EXCLUDE VARCHAR2(100) PATH '//*:CheckedListExclude',
             PARAM_VALUE VARCHAR2(4000) PATH 'Value',
             PARAM_VALUES VARCHAR2(4000) PATH 'string-join(Values//Value,";")'
 ) xt ORDER BY 1 DESC;

I had to put another column, and a NVL, cause I noticed that sometimes I had a node directly under the node, and had the desired output:

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

Oracle XML Query with multiple child nodes

4 Answers