Как выполнить внутреннее соединение трех таблиц с помощью XMLTABLE в Oracle

Сделайте выбор (или создайте представления) следующим образом:

CREATE VIEW ptcar AS
SELECT Id, LongNameFrench, LongNameDutch,
    TO_DATE(x.ValidFromDate, 'YYYY-MM-DD') as ValidFromDate,
    ...
FROM XML_Infrastructure,
    XMLTABLE(
        '$d/*:ptcars/*:ptcar'
        PASSING XMLTYPE(XML_Infrastructure.XML_RAW) as "d"
        COLUMNS
            Id              VARCHAR2(10)    PATH    '@*:id',
            LongNameFrench  VARCHAR2(60)    PATH    '@*:longNameFrench',
            LongNameDutch   VARCHAR2(60)    PATH    '@*:longNameDutch',
            ValidFromDate   VARCHAR2(10)    PATH    '@*:validFromDate',
            ValidToDate     VARCHAR2(10)    PATH    '@*:validToDate'
    ) as x;

CREATE VIEW ptrefColumn AS
SELECT Id,
    TO_DATE(x.ValidFromDate, 'YYYY-MM-DD') as ValidFromDate,
    ...
FROM XML_Infrastructure,
XMLTABLE(
    '$d/*:ptrefColumns/*:ptrefColumn'
    PASSING XMLTYPE(XML_Infrastructure.XML_RAW) as "d"
    COLUMNS
        Id              VARCHAR2(10)    PATH    '@*:id',
        ValidFromDate   VARCHAR2(10)    PATH    '@*:validFromDate',
        ValidToDate     VARCHAR2(10)    PATH    '@*:validToDate'
) as x

После этого вы можете присоединиться к ним напрямую, как указано в вашем вопросе.

NB, почему вы храните XML_RAW как CLOB, а не XMLTYPE?

Вы создаете три результата XMLTable X, Y и Z из одного и того же XML-документа, и в нем есть только узел ptcar, поэтому Y и Z не находят никаких данных (поскольку нет узлов, соответствующих этим XPath).

Учитывая сходство между тремя XML-документами и предполагая, что показанные вами узлы идентификаторов должны иметь одно и то же значение (что не так в вашем примере данных), вы можете использовать одну XMLTable для извлечения всех соответствующие данные из всех документов:

SELECT X.Name, X.Id, X.ValidFromDate, X.ValidToDate, X.LongNameFrench, X.LongNameDutch
FROM XML_Infrastructure
CROSS JOIN XMLTABLE(
        '$d/*/*'
        PASSING XMLTYPE(XML_Infrastructure.XML_RAW) as "d"
        COLUMNS
            Name            VARCHAR2(10)    PATH    './local-name()',
            Id              NUMBER          PATH    '@*:id',
            LongNameFrench  VARCHAR2(60)    PATH    '@*:longNameFrench',
            LongNameDutch   VARCHAR2(60)    PATH    '@*:longNameDutch',
            ValidFromDate   DATE            PATH    '@*:validFromDate',
            ValidToDate     DATE            PATH    '@*:validToDate'
    ) X;

Это использует подстановочные знаки для получения любого дочернего узла, но вы можете отфильтровать это, если у вас есть другие типы, которые вы не показали. Он также добавляет столбец name, чтобы вы могли указать, из какого документа взята каждая строка (или вы можете включить имя файла, если хотите). Это даст нулевые значения для атрибутов, которых нет во всех трех.

А затем используйте это в CTE и дважды соедините это с самим собой:

WITH cte AS (
  SELECT X.Name, X.Id, X.ValidFromDate, X.ValidToDate, X.LongNameFrench, X.LongNameDutch
  FROM XML_Infrastructure
  CROSS JOIN XMLTABLE(
          '$d/*/*'
          PASSING XMLTYPE(XML_Infrastructure.XML_RAW) as "d"
          COLUMNS
              Name            VARCHAR2(30)    PATH    './local-name()',
              Id              NUMBER          PATH    '@*:id',
              LongNameFrench  VARCHAR2(60)    PATH    '@*:longNameFrench',
              LongNameDutch   VARCHAR2(60)    PATH    '@*:longNameDutch',
              ValidFromDate   DATE            PATH    '@*:validFromDate',
              ValidToDate     DATE            PATH    '@*:validToDate'
      ) AS X
)
select a.longNameFrench, a.longNameDutch
from cte a
join cte b on b.id = a.id
join cte c on c.id = b.id
where c.name = 'ptref'
and b.name = 'ptrefColumn'
and a.name = 'ptcar'
and c.id = ptrefId
and a.validFromDate <= givenDate
and a.validToDate >= givenDate
and b.validFromDate <= givenDate
and b.validToDate >= givenDate
and c.validFromDate <= givenDate
and c.validToDate >= givenDate;

Это чем-то похоже на создание представлений запросов для каждого типа документа и их последующее объединение, но без необходимости в каких-либо новых постоянных объектах.

С вашими частичными данными выборки в другом CTE и установкой всех идентификаторов на 512 и добавлением отсутствующих имен в ptcar:

with XML_INFRASTRUCTURE (XMLI_ID, FILENAME, LAST_VERSION, XML_RAW) as (
  select cast (1 as number(2,0)), cast('ptcar' as varchar2(255)), date '2018-01-07', to_clob('<?xml version="1.0" encoding="ISO-8859-1"?>
<cern:ptcars creationDate="2018-03-16T19:35:54" xmlns:cern="http://www.website.com/Infrastructure" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.website.com/Infrastructure ../ns/infrastructure.xsd">
 <cern:ptcar id="512" validFromDate="1996-06-02" validToDate="2007-12-08" longNameFrench="Jean Dupont" longNameDutch="Jan Jansen"/>
</cern:ptcars>') from dual
  union all select 2, 'ptrefColumn', date '2018-01-07', to_clob('<?xml version="1.0" encoding="ISO-8859-1"?>
<cern:ptrefColumns creationDate="2018-03-20T11:33:21" xmlns:cern="http://www.website.com/Infrastructure" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.website.com/Infrastructure ../ns/infrastructure.xsd">
 <cern:ptrefColumn id="512" validFromDate="1998-04-01" validToDate="2001-06-11" />
</cern:ptrefColumns>') from dual
  union all select 3, 'ptref', date '2018-01-07', to_clob('<?xml version="1.0" encoding="ISO-8859-1"?>
<cern:ptrefs creationDate="2018-03-20T11:33:05" xmlns:cern="http://www.website.com/Infrastructure" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.website.com//Infrastructure ../ns/infrastructure.xsd">
 <cern:ptref id="512" validFromDate="1998-04-01" validToDate="2001-06-11" />
</cern:ptrefs>') from dual
),
cte AS (
  SELECT X.Name, X.Id, X.ValidFromDate, X.ValidToDate, X.LongNameFrench, X.LongNameDutch
  FROM XML_Infrastructure
  CROSS JOIN XMLTABLE(
          '$d/*/*'
          PASSING XMLTYPE(XML_Infrastructure.XML_RAW) as "d"
          COLUMNS
              Name            VARCHAR2(30)    PATH    './local-name()',
              Id              NUMBER          PATH    '@*:id',
              LongNameFrench  VARCHAR2(60)    PATH    '@*:longNameFrench',
              LongNameDutch   VARCHAR2(60)    PATH    '@*:longNameDutch',
              ValidFromDate   DATE            PATH    '@*:validFromDate',
              ValidToDate     DATE            PATH    '@*:validToDate'
      ) AS X
)
select a.longNameFrench, a.longNameDutch
from cte a
join cte b on b.id = a.id
join cte c on c.id = b.id
where c.name = 'ptref'
and b.name = 'ptrefColumn'
and a.name = 'ptcar'
and c.id = 512
and a.validFromDate <= date '2001-01-01'
and a.validToDate >= date '2001-01-01'
and b.validFromDate <= date '2001-01-01'
and b.validToDate >= date '2001-01-01'
and c.validFromDate <= date '2001-01-01'
and c.validToDate >= date '2001-01-01';

дает

LONGNAMEFRENCH                                               LONGNAMEDUTCH                                               
------------------------------------------------------------ ------------------------------------------------------------
Jean Dupont                                                  Jan Jansen