SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Q_ChangeMatter
(
  RecordSeriesId nvarchar(10),
  RecordCategoryId nvarchar(10),
  Id nvarchar(10),
  ResponsibleUser nvarchar(10)
);

insert into Q_ChangeMatter (RecordSeriesId, RecordCategoryId, Id, ResponsibleUser) values
('10070', '10070-8945', '0001', 'BFRANKLIN'),
('10070', '10070-8945', '0002', 'BFRANKLIN');

select * from Q_ChangeMatter;


RecordSeriesId
RecordCategoryId
Id
ResponsibleUser


10070
10070-8945
0001
BFRANKLIN

10070
10070-8945
0002
BFRANKLIN


-- ORIGINAL ATTEMPT BY QUESTION ASKER

SELECT
     RecordSeriesId as [RecordSeries/@Id]
   , RecordCategoryId as [RecordSeries/RecordCategory/@Id]

   ,'yes' as [RecordSeries/RecordCategory/Record/@Define]
   ,'0001' as [RecordSeries/RecordCategory/Record/@Id]

  ,'yes'    as [RecordSeries/RecordCategory/Record/Record/@Define]
  ,'0001' as [RecordSeries/RecordCategory/Record/Record/@Id]
  , [ResponsibleUser] as [RecordSeries/RecordCategory/Record/Record/@ResponsibleUser]

  ,'yes' as [RecordSeries/RecordCategory/Record/Record/Record/@Define]
  ,'0002' as [RecordSeries/RecordCategory/Record/Record/Record/@Id]
  , [ResponsibleUser] as [RecordSeries/RecordCategory/Record/Record/Record/@ResponsibleUser]

 FROM Q_ChangeMatter
  WHERE RecordSeriesId = '10070'
 FOR xml path(''), ROOT('RecordsManager')

/*
<RecordsManager>
   <RecordSeries Id="10070">
      <RecordCategory Id="10070-8945">
         <Record Define="yes" Id="0001">
            <Record Define="yes" Id="0001" ResponsibleUser="BFRANKLIN">
               <Record Define="yes" Id="0002" ResponsibleUser="BFRANKLIN" />
            </Record>
         </Record>
      </RecordCategory>
   </RecordSeries>
   <RecordSeries Id="10070">
      <RecordCategory Id="10070-8945">
         <Record Define="yes" Id="0001">
            <Record Define="yes" Id="0001" ResponsibleUser="BFRANKLIN">
               <Record Define="yes" Id="0002" ResponsibleUser="BFRANKLIN" />
            </Record>
         </Record>
      </RecordCategory>
   </RecordSeries>
</RecordsManager>
*/


XML_F52E2B61-18A1-11d1-B105-00805F49916B


<RecordsManager><RecordSeries Id="10070"><RecordCategory Id="10070-8945"><Record Define="yes" Id="0001"><Record Define="yes" Id="0001" ResponsibleUser="BFRANKLIN"><Record Define="yes" Id="0002" ResponsibleUser="BFRANKLIN"/></Record></Record></RecordCategory></RecordSeries><RecordSeries Id="10070"><RecordCategory Id="10070-8945"><Record Define="yes" Id="0001"><Record Define="yes" Id="0001" ResponsibleUser="BFRANKLIN"><Record Define="yes" Id="0002" ResponsibleUser="BFRANKLIN"/></Record></Record></RecordCategory></RecordSeries></RecordsManager>


-- SOLUTION

select RecordSeriesId as [@Id]
      ,(
       select RecordCategoryId as [@Id]
             ,'yes' as [@Define]
             ,(
              select '0001' as [@Id]
                    ,'yes' as [@Define]
                    ,(
                     select Id as [@Id]
                           ,ResponsibleUser as [@ResponsibleUser]
                     from Q_ChangeMatter T4
                     where T4.RecordSeriesId = T1.RecordSeriesId
                       and T4.RecordCategoryId = T2.RecordCategoryId
                     for xml path('Record'), type
                     )
              from Q_ChangeMatter T3
              where T3.RecordSeriesId = T1.RecordSeriesId
                and T3.RecordCategoryId = T2.RecordCategoryId
              group by RecordSeriesId, RecordCategoryId
              for xml path('Record'), type
              )
       from Q_ChangeMatter T2
       where T2.RecordSeriesId = T1.RecordSeriesId
       group by RecordCategoryId
       for xml path('RecordCategory'), type
       )
from Q_ChangeMatter T1
where RecordSeriesId = '10070'
group by RecordSeriesId
for xml path('RecordSeries'), root('RecordsManager');

/*
<RecordsManager>
   <RecordSeries Id="10070">
      <RecordCategory Id="10070-8945" Define="yes">
         <Record Id="0001" Define="yes">
            <Record Id="0001" ResponsibleUser="BFRANKLIN" />
            <Record Id="0002" ResponsibleUser="BFRANKLIN" />
         </Record>
      </RecordCategory>
   </RecordSeries>
</RecordsManager>
*/


XML_F52E2B61-18A1-11d1-B105-00805F49916B


<RecordsManager><RecordSeries Id="10070"><RecordCategory Id="10070-8945" Define="yes"><Record Id="0001" Define="yes"><Record Id="0001" ResponsibleUser="BFRANKLIN"/><Record Id="0002" ResponsibleUser="BFRANKLIN"/></Record></RecordCategory></RecordSeries></RecordsManager>