Как создать объединенные записи в виде встроенных элементов XML в SELECT... FOR XML?

Вы не группируете по MASTER.MASTER_PK, поэтому вы получаете 1 строку для каждого кортежа (master, вложенный1, вложенный2), сгенерированного вашими соединениями JOIN. Как только вы сгруппируете по MASTER.MASTER_PK, у вас возникнет еще одна проблема: вы получите перекрестное произведение все NESTED1 и NESTED2 связаны с этим MASTER_PK, поэтому решение состоит в том, чтобы сгенерировать список узлов «NESTED1» и «NESTED2» с подзапросами, агрегирующими результаты в узле MASTER.

Попробуйте следующее решение на основе T-SQL и XQuery.

Он следует той же парадигме минимальных воспроизводимых примеров. Вы копируете его в SSMS как есть, запускаете, и он работает как положено, выдавая желаемый результат.

SQL

USE tempdb;
GO

-- DDL and sample data population, start
DROP TABLE IF EXISTS popis;
DROP TABLE IF EXISTS master;
DROP TABLE IF EXISTS performance;

CREATE TABLE master (
   master_pk int identity(1,1) primary key,
   item_id int,
   item_name varchar(50)
);
INSERT INTO master VALUES 
( 158, 'Antenna');

create table popis (
  master_pk int,
  POPIS_PK int identity(1,1) primary key,
  LANG varchar(3),
  TEXT varchar(50)
);
insert into popis values 
(1, 'CZ', 'Antena Yagi 6el'), 
(1, 'EN', 'Antenna Yagi 6 elements');

create table performance ( 
  master_pk int, 
  performance_pk int identity(1,1),
  param varchar(50),
  unit varchar(10),
  value numeric(8,3)
);
insert into performance 
values (1, 'GAIN', 'dBi', 8.5),
(1, 'F/B RATIO', 'dB', 15),
(1, 'LENGTH', 'cm', 120);
-- DDL and sample data population, end

SELECT m.item_id AS [ID], m.item_name AS [NAME]
    , (SELECT po.LANG, po.TEXT
        FROM popis AS po
        WHERE po.master_pk = m.master_pk
        FOR XML PATH('DESCRIPTION'), TYPE)
    , (SELECT pe.param AS [PARAM], pe.unit AS [UNIT], pe.value AS [VALUE]
        FROM performance AS pe
        WHERE pe.master_pk = m.master_pk
        FOR XML PATH('PERFORMANCE'), TYPE)
FROM dbo.master  AS m
FOR XML PATH(''), TYPE, ROOT('MASTER');

Выход

<MASTER>
  <ID>158</ID>
  <NAME>Antenna</NAME>
  <DESCRIPTION>
    <LANG>CZ</LANG>
    <TEXT>Antena Yagi 6el</TEXT>
  </DESCRIPTION>
  <DESCRIPTION>
    <LANG>EN</LANG>
    <TEXT>Antenna Yagi 6 elements</TEXT>
  </DESCRIPTION>
  <PERFORMANCE>
    <PARAM>GAIN</PARAM>
    <UNIT>dBi</UNIT>
    <VALUE>8.500</VALUE>
  </PERFORMANCE>
  <PERFORMANCE>
    <PARAM>F/B RATIO</PARAM>
    <UNIT>dB</UNIT>
    <VALUE>15.000</VALUE>
  </PERFORMANCE>
  <PERFORMANCE>
    <PARAM>LENGTH</PARAM>
    <UNIT>cm</UNIT>
    <VALUE>120.000</VALUE>
  </PERFORMANCE>
</MASTER>