Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Orders ( ID, Name ) AS
  select 1, 'Order 1' from dual union all
  select 2, 'Order 2' from dual;
2 rows affected

CREATE TABLE Products (Id, Name) AS
  select 200, 'apple'    from dual union all
  select 201, 'orange'   from dual union all
  select 202, 'car'      from dual union all
  select 203, 'airplane' from dual;
4 rows affected

CREATE TABLE OrderDetails (Order_Id, Product_Id) as
    select 1, 202 from dual union all
    select 2, 201 from dual union all
    select 2, 202 from dual union all
    select 2, 203 from dual;
4 rows affected

CREATE TYPE product_type AS OBJECT(
  id   NUMBER,
  name VARCHAR2(200)
);
/
CREATE TYPE product_table AS TABLE OF product_type;
/
SELECT id AS Order_Id,
       CAST(
         MULTISET (
           SELECT  product_type( P.id, P.name )
           FROM    OrderDetails OD
           JOIN    Products P
           ON      OD.Product_Id = P.Id
           WHERE   OD.Order_Id   = O.Id
         )
         AS product_table
       ) AS products
FROM   Orders O;
SELECT id AS order_id,
       ( SELECT XMLELEMENT(
                  "Products",
                  XMLAGG(
                    XMLElement(
                      "Product",
                      XMLFOREST(
                        p.id AS "ProductID",
                        p.name AS "ProductName"
                      )
                    )
                  )
                )
         FROM   OrderDetails OD
         JOIN   Products P
         ON     OD.Product_Id = P.Id
         WHERE  OD.Order_Id   = O.Id
       ) AS products
FROM   Orders o


ORDER_ID
PRODUCTS


1
<Products><Product><ProductID>202</ProductID><ProductName>car</ProductName></Product></Products>

2
<Products><Product><ProductID>201</ProductID><ProductName>orange</ProductName></Product><Product><ProductID>202</ProductID><ProductName>car</ProductName></Product><Product><ProductID>203</ProductID><ProductName>airplane</ProductName></Product></Products>


SELECT id AS order_id,
       ( SELECT JSON_ARRAYAGG(
                  JSON_OBJECT(
                    'id' VALUE p.id,
                    'name' VALUE p.name
                  )
                )
         FROM   OrderDetails OD
         JOIN   Products P
         ON     OD.Product_Id = P.Id
         WHERE  OD.Order_Id   = O.Id
       ) AS products
FROM   Orders o


ORDER_ID
PRODUCTS


1
[{"id":202,"name":"car"}]

2
[{"id":201,"name":"orange"},{"id":202,"name":"car"},{"id":203,"name":"airplane"}]

ORDER_ID	PRODUCTS
1	<Products><Product><ProductID>202</ProductID><ProductName>car</ProductName></Product></Products>
2	<Products><Product><ProductID>201</ProductID><ProductName>orange</ProductName></Product><Product><ProductID>202</ProductID><ProductName>car</ProductName></Product><Product><ProductID>203</ProductID><ProductName>airplane</ProductName></Product></Products>

ORDER_ID	PRODUCTS
1	[{"id":202,"name":"car"}]
2	[{"id":201,"name":"orange"},{"id":202,"name":"car"},{"id":203,"name":"airplane"}]