SQL Server 2019

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

DECLARE @xml xml ='<?xml version="1.0" encoding="utf-8"?>
<Root>
  <Applicants BuildingCode="7578">
    <SubCode>2000</SubCode>
      <Applicant>
        <CourseName>Data Analytics 101 - Beginner"</CourseName>
        <CourseCode>DA101</CourseCode>
        <StartDate>2022-08-01 08:00:00</StartDate>
        <FirstName>Joe</FirstName>
        <LastName>Stewart</LastName>
        <StudentID>301</StudentID>
        <Membership>Gold</Membership>
      </Applicant>
      <Applicant>
        <CourseName>Data Analytics 101 - Beginner"</CourseName>
        <CourseCode>DA101</CourseCode>
        <StartDate>2022-08-01 08:00:00</StartDate>
        <FirstName>Sandy</FirstName>
        <LastName>Gomez</LastName>
        <StudentID>302</StudentID>
        <Membership>Bronze</Membership>
      </Applicant>
  </Applicants>
</Root>
';

SELECT
   x1.Applicants.value('@BuildingCode', 'VARCHAR(15)'),
   x1.Applicants.value('(SubCode/text())[1]', 'VARCHAR(15)') as SubCode,
   x2.Applicant.value('(CourseName/text())[1]', 'VARCHAR(50)') as CourseName,
   x2.Applicant.value('(CourseCode/text())[1]', 'VARCHAR(20)') as CourseCode,
   x2.Applicant.value('(StartDate/text())[1]', 'datetime') as StartDate,
   x2.Applicant.value('(FirstName/text())[1]', 'VARCHAR(50)') as FirstName,
   x2.Applicant.value('(LastName/text())[1]', 'VARCHAR(50)') as LastName,
   x2.Applicant.value('(StudentID/text())[1]', 'int') as StudentID,
   x2.Applicant.value('(Membership/text())[1]', 'VARCHAR(20)') as Membership

FROM (VALUES (
    @xml
) ) AS T2(XMLFromFile)

CROSS APPLY T2.XMLFromFile.nodes('Root/Applicants') AS x1(Applicants)
CROSS APPLY x1.Applicants.nodes('Applicant') AS x2(Applicant);

(No column name)	SubCode	CourseName	CourseCode	StartDate	FirstName	LastName	StudentID	Membership
7578	2000	Data Analytics 101 - Beginner"	DA101	2022-08-01 08:00:00.000	Joe	Stewart	301	Gold
7578	2000	Data Analytics 101 - Beginner"	DA101	2022-08-01 08:00:00.000	Sandy	Gomez	302	Bronze