Oracle XMLtable giving cross joined data

Question

I have written a Oracle XML sql and it is giving output like below. I have given the whole code below from creating table to sql below for your quick help. output: (WRONG)

ID, NAME 
1   name1
1   name2
2   name1
2   name2 

Want to make output like below (Required Output):

ID, NAME
1   name1
2   name2

Code:

CREATE TABLE XML_TBL
   (    "INSTANCE_DETAIL_XML" "SYS"."XMLTYPE" 
   );

SET DEFINE OFF;
Insert into XML_TBL (INSTANCE_DETAIL_XML) values ('<?xml version="1.0" encoding="UTF-8" standalone=''yes''?>
<driXML>
  <sDet>
    <cols>
      <col>
        <id>1</id>
        <name>name1</name>
      </col>
      <col>
        <id>2</id>
        <name>name2</name>
      </col>
    </cols>
  </sDet>
</driXML>
');

I tried the below sql, you may modify it or create a new one using it:

Select XT_ID.id
     , XT_NAME.name
  FROM xml_tbl XT
       join XMLTABLE
               ('/driXML' PASSING XT.INSTANCE_DETAIL_XML COLUMNS
                    id_XML XMLType    PATH 'sDet/cols/col/id'
                  , name_XML XMLType  PATH 'sDet/cols/col/name'
               ) RI_XML on 1=1
       join XMLTABLE('/id'   PASSING RI_XML.id_XML   COLUMNS ID   number        PATH '.') XT_ID on 1=1
       join XMLTABLE('/name' PASSING RI_XML.name_XML COLUMNS NAME varchar2(50)  PATH '.') XT_NAME on 1=1
;             

Answer 1

You can just directly extract the id and name in the first XMLTABLE and if you descend through the hierarchy so that the path is '/driXML/sDet/cols/col' then each row from the XMLTYPE will be one col element and the id and name will correlate to that.

Oracle Setup:

CREATE TABLE XML_TBL( INSTANCE_DETAIL_XML ) AS
SELECT XMLTYPE( '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<driXML>
  <sDet>
    <cols>
      <col>
        <id>1</id>
        <name>name1</name>
      </col>
      <col>
        <id>2</id>
        <name>name2</name>
      </col>
    </cols>
  </sDet>
</driXML>' ) FROM DUAL;

Query:

SELECT id,
       name
FROM   xml_tbl XT
       CROSS JOIN XMLTABLE(
         '/driXML/sDet/cols/col'
         PASSING XT.INSTANCE_DETAIL_XML
         COLUMNS
           id   NUMBER       PATH 'id',
           name VARCHAR2(50) PATH 'name'
       );

Output:

ID | NAME 
-: | :----
 1 | name1
 2 | name2

db<>fiddle here