SQL Server XML Nested Values Query Form 990

Question

My XML file is as listed below. The XML document is in a table called form990

My query is supposed to return the value for the tag OfficerInd. There are several different versions of this tag (nested values) so I attempt to return all the values. Here is the query that has worked most other tags:

WITH XMLNAMESPACES (DEFAULT 'http://www.irs.gov/efile')
    SELECT ID 
    
    
    , Form990PartVIISectionAGrpOfficerInd = c2.value('(//OfficerInd/text())[0]','varchar(MAX)')
    
    , Form990PartVIISectionAGrpOfficerInd1 = c2.value('(//OfficerInd/text())[1]','varchar(MAX)')
    
    , Form990PartVIISectionAGrpOfficerInd2 = c2.value('(//OfficerInd/text())[2]','varchar(MAX)')
    
    , Form990PartVIISectionAGrpOfficerInd3 = c2.value('(//OfficerInd/text())[3]','varchar(MAX)')
    
    , Form990PartVIISectionAGrpOfficerInd4 = c2.value('(//OfficerInd/text())[4]','varchar(MAX)')
    
    , Form990PartVIISectionAGrpOfficerInd5 = c2.value('(//OfficerInd/text())[5]','varchar(MAX)')
    
    FROM Form990
        CROSS APPLY XMLData.nodes('/Return') AS t(c) 
        CROSS APPLY XMLData.nodes('/Return/ReturnData/IRS990') AS t2(c2)

My query returns

105  NULL     X    X    X    NULL NULL NULL NULL

but is supposed to return

105  NULL  NULL NULL NULL X X X X

XML:

<?xml version="1.0" encoding="utf-8"?>
<Return xmlns="http://www.irs.gov/efile" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.irs.gov/efile" returnVersion="2019v5.2">
  <ReturnHeader binaryAttachmentCnt="0">
    <ReturnTs>2021-02-11T17:37:09-06:00</ReturnTs>
    <TaxPeriodEndDt>2020-03-31</TaxPeriodEndDt>
    <PreparerFirmGrp>
      <PreparerFirmEIN>752570395</PreparerFirmEIN>
      <PreparerFirmName>
        <BusinessNameLine1Txt>DURBIN &amp; COMPANY LLP</BusinessNameLine1Txt>
      </PreparerFirmName>
      <PreparerUSAddress>
        <AddressLine1Txt>2950 50TH STREET</AddressLine1Txt>
        <CityNm>LUBBOCK</CityNm>
        <StateAbbreviationCd>TX</StateAbbreviationCd>
        <ZIPCd>79413</ZIPCd>
      </PreparerUSAddress>
    </PreparerFirmGrp>
    <ReturnTypeCd>990</ReturnTypeCd>
    <TaxPeriodBeginDt>2019-04-01</TaxPeriodBeginDt>
    <Filer>
      <EIN>320326595</EIN>
      <BusinessName>
        <BusinessNameLine1Txt>HEART OF TEXAS HEALTHCARE SYSTEM</BusinessNameLine1Txt>
      </BusinessName>
      <BusinessNameControlTxt>HEAR</BusinessNameControlTxt>
      <PhoneNum>3255972901</PhoneNum>
      <USAddress>
        <AddressLine1Txt>2008 NINE RD</AddressLine1Txt>
        <CityNm>BRADY</CityNm>
        <StateAbbreviationCd>TX</StateAbbreviationCd>
        <ZIPCd>76825</ZIPCd>
      </USAddress>
    </Filer>
    <BusinessOfficerGrp>
      <PersonNm>RENAE THOMAS</PersonNm>
      <PersonTitleTxt>CFO</PersonTitleTxt>
      <PhoneNum>3255972901</PhoneNum>
      <SignatureDt>2021-02-11</SignatureDt>
    </BusinessOfficerGrp>
    <PreparerPersonGrp>
      <PreparerPersonNm>GAYLE DE HAAS</PreparerPersonNm>
      <PTIN>P01774581</PTIN>
      <PhoneNum>8067911591</PhoneNum>
    </PreparerPersonGrp>
    <FilingSecurityInformation>
      <IPAddress>
        <IPv4AddressTxt>12.86.242.26</IPv4AddressTxt>
      </IPAddress>
      <IPDt>2021-02-11</IPDt>
      <IPTm>17:37:04</IPTm>
      <IPTimezoneCd>CS</IPTimezoneCd>
      <FilingLicenseTypeCd>P</FilingLicenseTypeCd>
      <AtSubmissionCreationDeviceId>DA39A3EE5E6B4B0D3255BFEF95601890AFD80709</AtSubmissionCreationDeviceId>
      <AtSubmissionFilingDeviceId>0233A6F5F381DB5D6536E14B0823E12B194F9A03</AtSubmissionFilingDeviceId>
    </FilingSecurityInformation>
    <TaxYr>2019</TaxYr>
    <BuildTS>2021-01-29 14:40:06Z</BuildTS>
  </ReturnHeader>
  <ReturnData documentCnt="7">
    <IRS990 documentId="RetDoc1038000001" referenceDocumentId="RetDoc1044400001">
      <PrincipalOfficerNm>TIM JONES</PrincipalOfficerNm>
      <USAddress>
        <AddressLine1Txt>2008 NINE ROAD</AddressLine1Txt>
        <CityNm>BRADY</CityNm>
        <StateAbbreviationCd>TX</StateAbbreviationCd>
        <ZIPCd>76825</ZIPCd>
      </USAddress>
      <GrossReceiptsAmt>27779767</GrossReceiptsAmt>
      <GroupReturnForAffiliatesInd>0</GroupReturnForAffiliatesInd>
      <Organization501c3Ind>X</Organization501c3Ind>
      <WebsiteAddressTxt>WWW.BRADYHOSPITAL.COM</WebsiteAddressTxt>
      <TypeOfOrganizationCorpInd>X</TypeOfOrganizationCorpInd>
      <FormationYr>2011</FormationYr>
      <LegalDomicileStateCd>TX</LegalDomicileStateCd>
      <ActivityOrMissionDesc>TO PROMOTE THE HEALTH OF THE RESIDENTS OF MCCULLOCH COUNTY, TEXAS AND SURROUNDING COMMUNITIES, IN ANY APPROPRIATE MANNER AND IN COMPLIANCE WITH APPLICABLE FEDERAL AND STATE LAWS.</ActivityOrMissionDesc>
      <VotingMembersGoverningBodyCnt>5</VotingMembersGoverningBodyCnt>
      <VotingMembersIndependentCnt>4</VotingMembersIndependentCnt>
      <TotalEmployeeCnt>135</TotalEmployeeCnt>
      <TotalVolunteersCnt>0</TotalVolunteersCnt>
      <TotalGrossUBIAmt>0</TotalGrossUBIAmt>
      <NetUnrelatedBusTxblIncmAmt>0</NetUnrelatedBusTxblIncmAmt>
      <PYContributionsGrantsAmt>33845</PYContributionsGrantsAmt>
      <CYContributionsGrantsAmt>19787</CYContributionsGrantsAmt>
      <PYProgramServiceRevenueAmt>15243987</PYProgramServiceRevenueAmt>
      <CYProgramServiceRevenueAmt>18494816</CYProgramServiceRevenueAmt>
      <PYInvestmentIncomeAmt>171058</PYInvestmentIncomeAmt>
      <CYInvestmentIncomeAmt>454803</CYInvestmentIncomeAmt>
      <PYOtherRevenueAmt>9100823</PYOtherRevenueAmt>
      <CYOtherRevenueAmt>8726245</CYOtherRevenueAmt>
      <PYTotalRevenueAmt>24549713</PYTotalRevenueAmt>
      <CYTotalRevenueAmt>27695651</CYTotalRevenueAmt>
      <PYGrantsAndSimilarPaidAmt>0</PYGrantsAndSimilarPaidAmt>
      <CYGrantsAndSimilarPaidAmt>0</CYGrantsAndSimilarPaidAmt>
      <PYBenefitsPaidToMembersAmt>0</PYBenefitsPaidToMembersAmt>
      <CYBenefitsPaidToMembersAmt>0</CYBenefitsPaidToMembersAmt>
      <PYSalariesCompEmpBnftPaidAmt>5082231</PYSalariesCompEmpBnftPaidAmt>
      <CYSalariesCompEmpBnftPaidAmt>5180633</CYSalariesCompEmpBnftPaidAmt>
      <PYTotalProfFndrsngExpnsAmt>0</PYTotalProfFndrsngExpnsAmt>
      <CYTotalProfFndrsngExpnsAmt>0</CYTotalProfFndrsngExpnsAmt>
      <CYTotalFundraisingExpenseAmt>0</CYTotalFundraisingExpenseAmt>
      <PYOtherExpensesAmt>13226673</PYOtherExpensesAmt>
      <CYOtherExpensesAmt>12878290</CYOtherExpensesAmt>
      <PYTotalExpensesAmt>18308904</PYTotalExpensesAmt>
      <CYTotalExpensesAmt>18058923</CYTotalExpensesAmt>
      <PYRevenuesLessExpensesAmt>6240809</PYRevenuesLessExpensesAmt>
      <CYRevenuesLessExpensesAmt>9636728</CYRevenuesLessExpensesAmt>
      <TotalAssetsBOYAmt>48006718</TotalAssetsBOYAmt>
      <TotalAssetsEOYAmt>57981362</TotalAssetsEOYAmt>
      <TotalLiabilitiesBOYAmt>2005319</TotalLiabilitiesBOYAmt>
      <TotalLiabilitiesEOYAmt>2238391</TotalLiabilitiesEOYAmt>
      <NetAssetsOrFundBalancesBOYAmt>46001399</NetAssetsOrFundBalancesBOYAmt>
      <NetAssetsOrFundBalancesEOYAmt>55742971</NetAssetsOrFundBalancesEOYAmt>
      <InfoInScheduleOPartIIIInd>X</InfoInScheduleOPartIIIInd>
      <MissionDesc>THE SYSTEM'S PURPOSES ARE TO PROMOTE THE HEALTH OF THE RESIDENTS OF MCCULLOCH COUNTY, TEXAS AND SURROUNDING COMMUNITIES, IN ANY APPROPRIATE MANNER AND IN COMPLIANCE WITH APPLICABLE FEDERAL AND STATE LAWS; AND TO OWN, OPERATE, OR MANAGE, OR PARTICIPATE IN THE OWNERSHIP, OPERATION, OR MANAGEMENT OF, ONE OR MORE HOSPITALS OR HEALTH CARE ORGANIZATIONS OR OTHER ENTITIES WHOSE PURPOSE IS THE DELIVERY OF OR ARRANGEMENT FOR HEALTH CARE OR HEALTH-RELATED SERVICES, INCLUDING BUT NOT LIMITED TO HEART OF TEXAS MEMORIAL HOSPITAL ("HOSPITAL").</MissionDesc>
      <SignificantNewProgramSrvcInd>0</SignificantNewProgramSrvcInd>
      <SignificantChangeInd>0</SignificantChangeInd>
      <ExpenseAmt>15872829</ExpenseAmt>
      <RevenueAmt>27136945</RevenueAmt>
      <Desc>HEART OF TEXAS HEALTHCARE SYSTEM IS DEDICATED TO PROVIDING ACCESSIBLE HEALTHCARE SERVICES TO THE CITIZENS OF MCCULLOCH COUNTY, TEXAS AND SURROUNDING AREAS. THE SYSTEM IS A 25-BED CRITICAL ACCESS HOSPITAL OFFERING COMPREHENSIVE HEALTHCARE. THE HOSPITAL HAD 1014 ACUTE DAYS AND 81 SWING BED DAYS IN FISCAL YEAR 2015.</Desc>
      <TotalProgramServiceExpensesAmt>15872829</TotalProgramServiceExpensesAmt>
      <DescribedInSection501c3Ind referenceDocumentId="RetDoc1039100001">1</DescribedInSection501c3Ind>
      <ScheduleBRequiredInd referenceDocumentId="RetDoc1234500001">1</ScheduleBRequiredInd>
      <PoliticalCampaignActyInd>0</PoliticalCampaignActyInd>
      <LobbyingActivitiesInd>0</LobbyingActivitiesInd>
      <SubjectToProxyTaxInd>0</SubjectToProxyTaxInd>
      <DonorAdvisedFundInd referenceDocumentId="RetDoc1040000001">0</DonorAdvisedFundInd>
      <ConservationEasementsInd referenceDocumentId="RetDoc1040000001">0</ConservationEasementsInd>
      <CollectionsOfArtInd referenceDocumentId="RetDoc1040000001">0</CollectionsOfArtInd>
      <CreditCounselingInd referenceDocumentId="RetDoc1040000001">0</CreditCounselingInd>
      <DonorRstrOrQuasiEndowmentsInd>0</DonorRstrOrQuasiEndowmentsInd>
      <ReportLandBuildingEquipmentInd referenceDocumentId="RetDoc1040000001">1</ReportLandBuildingEquipmentInd>
      <ReportInvestmentsOtherSecInd referenceDocumentId="RetDoc1040000001">0</ReportInvestmentsOtherSecInd>
      <ReportProgramRelatedInvstInd referenceDocumentId="RetDoc1040000001">0</ReportProgramRelatedInvstInd>
      <ReportOtherAssetsInd referenceDocumentId="RetDoc1040000001">1</ReportOtherAssetsInd>
      <ReportOtherLiabilitiesInd referenceDocumentId="RetDoc1040000001">1</ReportOtherLiabilitiesInd>
      <IncludeFIN48FootnoteInd referenceDocumentId="RetDoc1040000001">1</IncludeFIN48FootnoteInd>
      <IndependentAuditFinclStmtInd referenceDocumentId="RetDoc1040000001">0</IndependentAuditFinclStmtInd>
      <ConsolidatedAuditFinclStmtInd referenceDocumentId="RetDoc1040000001">1</ConsolidatedAuditFinclStmtInd>
      <SchoolOperatingInd>0</SchoolOperatingInd>
      <ForeignOfficeInd>0</ForeignOfficeInd>
      <ForeignActivitiesInd>0</ForeignActivitiesInd>
      <MoreThan5000KToOrgInd>0</MoreThan5000KToOrgInd>
      <MoreThan5000KToIndividualsInd>0</MoreThan5000KToIndividualsInd>
      <ProfessionalFundraisingInd>0</ProfessionalFundraisingInd>
      <FundraisingActivitiesInd>0</FundraisingActivitiesInd>
      <GamingActivitiesInd>0</GamingActivitiesInd>
      <OperateHospitalInd referenceDocumentId="RetDoc1041500001">1</OperateHospitalInd>
      <AuditedFinancialStmtAttInd referenceDocumentId="RetDoc2317200001">1</AuditedFinancialStmtAttInd>
      <GrantsToOrganizationsInd>0</GrantsToOrganizationsInd>
      <GrantsToIndividualsInd>0</GrantsToIndividualsInd>
      <ScheduleJRequiredInd referenceDocumentId="RetDoc1042400001">1</ScheduleJRequiredInd>
      <TaxExemptBondsInd>0</TaxExemptBondsInd>
      <EngagedInExcessBenefitTransInd>0</EngagedInExcessBenefitTransInd>
      <PYExcessBenefitTransInd>0</PYExcessBenefitTransInd>
      <LoanOutstandingInd>0</LoanOutstandingInd>
      <GrantToRelatedPersonInd>0</GrantToRelatedPersonInd>
      <BusinessRlnWithOrgMemInd>0</BusinessRlnWithOrgMemInd>
      <BusinessRlnWithFamMemInd>0</BusinessRlnWithFamMemInd>
      <BusinessRlnWith35CtrlEntInd>0</BusinessRlnWith35CtrlEntInd>
      <DeductibleNonCashContriInd>0</DeductibleNonCashContriInd>
      <DeductibleArtContributionInd>0</DeductibleArtContributionInd>
      <TerminateOperationsInd>0</TerminateOperationsInd>
      <PartialLiquidationInd>0</PartialLiquidationInd>
      <DisregardedEntityInd>0</DisregardedEntityInd>
      <RelatedEntityInd>0</RelatedEntityInd>
      <RelatedOrganizationCtrlEntInd>0</RelatedOrganizationCtrlEntInd>
      <TrnsfrExmptNonChrtblRltdOrgInd>0</TrnsfrExmptNonChrtblRltdOrgInd>
      <ActivitiesConductedPrtshpInd>0</ActivitiesConductedPrtshpInd>
      <ScheduleORequiredInd>1</ScheduleORequiredInd>
      <IRPDocumentCnt>44</IRPDocumentCnt>
      <IRPDocumentW2GCnt>0</IRPDocumentW2GCnt>
      <BackupWthldComplianceInd>1</BackupWthldComplianceInd>
      <EmployeeCnt>135</EmployeeCnt>
      <EmploymentTaxReturnsFiledInd>1</EmploymentTaxReturnsFiledInd>
      <UnrelatedBusIncmOverLimitInd>0</UnrelatedBusIncmOverLimitInd>
      <ForeignFinancialAccountInd>0</ForeignFinancialAccountInd>
      <ProhibitedTaxShelterTransInd>0</ProhibitedTaxShelterTransInd>
      <TaxablePartyNotificationInd>0</TaxablePartyNotificationInd>
      <NondeductibleContributionsInd>0</NondeductibleContributionsInd>
      <QuidProQuoContributionsInd>0</QuidProQuoContributionsInd>
      <Form8282PropertyDisposedOfInd>0</Form8282PropertyDisposedOfInd>
      <IndoorTanningServicesInd>0</IndoorTanningServicesInd>
      <SubjToTaxRmnrtnExPrchtPymtInd>0</SubjToTaxRmnrtnExPrchtPymtInd>
      <SubjectToExcsTaxNetInvstIncInd>0</SubjectToExcsTaxNetInvstIncInd>
      <InfoInScheduleOPartVIInd>X</InfoInScheduleOPartVIInd>
      <GoverningBodyVotingMembersCnt>5</GoverningBodyVotingMembersCnt>
      <IndependentVotingMemberCnt>4</IndependentVotingMemberCnt>
      <FamilyOrBusinessRlnInd>0</FamilyOrBusinessRlnInd>
      <DelegationOfMgmtDutiesInd>0</DelegationOfMgmtDutiesInd>
      <ChangeToOrgDocumentsInd>0</ChangeToOrgDocumentsInd>
      <MaterialDiversionOrMisuseInd>0</MaterialDiversionOrMisuseInd>
      <MembersOrStockholdersInd>0</MembersOrStockholdersInd>
      <ElectionOfBoardMembersInd>0</ElectionOfBoardMembersInd>
      <DecisionsSubjectToApprovaInd>0</DecisionsSubjectToApprovaInd>
      <MinutesOfGoverningBodyInd>1</MinutesOfGoverningBodyInd>
      <MinutesOfCommitteesInd>1</MinutesOfCommitteesInd>
      <OfficerMailingAddressInd>0</OfficerMailingAddressInd>
      <LocalChaptersInd>0</LocalChaptersInd>
      <Form990ProvidedToGvrnBodyInd>1</Form990ProvidedToGvrnBodyInd>
      <ConflictOfInterestPolicyInd>1</ConflictOfInterestPolicyInd>
      <AnnualDisclosureCoveredPrsnInd>1</AnnualDisclosureCoveredPrsnInd>
      <RegularMonitoringEnfrcInd>1</RegularMonitoringEnfrcInd>
      <WhistleblowerPolicyInd>0</WhistleblowerPolicyInd>
      <DocumentRetentionPolicyInd>1</DocumentRetentionPolicyInd>
      <CompensationProcessCEOInd>1</CompensationProcessCEOInd>
      <CompensationProcessOtherInd>1</CompensationProcessOtherInd>
      <InvestmentInJointVentureInd>0</InvestmentInJointVentureInd>
      <UponRequestInd>X</UponRequestInd>
      <BooksInCareOfDetail>
        <BusinessName>
          <BusinessNameLine1Txt>HEART OF TEXAS HEALTHCARE SYSTM</BusinessNameLine1Txt>
        </BusinessName>
        <PhoneNum>3255972901</PhoneNum>
        <USAddress>
          <AddressLine1Txt>2008 NINE RD</AddressLine1Txt>
          <CityNm>BRADY</CityNm>
          <StateAbbreviationCd>TX</StateAbbreviationCd>
          <ZIPCd>76825</ZIPCd>
        </USAddress>
      </BooksInCareOfDetail>
      <Form990PartVIISectionAGrp>
        <PersonNm>MICHELLE YOUNG-DERRICK</PersonNm>
        <TitleTxt>PRESIDENT</TitleTxt>
        <AverageHoursPerWeekRt>1.00</AverageHoursPerWeekRt>
        <IndividualTrusteeOrDirectorInd>X</IndividualTrusteeOrDirectorInd>
        <ReportableCompFromOrgAmt>0</ReportableCompFromOrgAmt>
        <ReportableCompFromRltdOrgAmt>0</ReportableCompFromRltdOrgAmt>
        <OtherCompensationAmt>0</OtherCompensationAmt>
      </Form990PartVIISectionAGrp>
      <Form990PartVIISectionAGrp>
        <PersonNm>KIRK RODDIE</PersonNm>
        <TitleTxt>VICE PRESIDENT</TitleTxt>
        <AverageHoursPerWeekRt>1.00</AverageHoursPerWeekRt>
        <IndividualTrusteeOrDirectorInd>X</IndividualTrusteeOrDirectorInd>
        <ReportableCompFromOrgAmt>0</ReportableCompFromOrgAmt>
        <ReportableCompFromRltdOrgAmt>0</ReportableCompFromRltdOrgAmt>
        <OtherCompensationAmt>0</OtherCompensationAmt>
      </Form990PartVIISectionAGrp>
      <Form990PartVIISectionAGrp>
        <PersonNm>TERRY KELTZ</PersonNm>
        <TitleTxt>BOARD MEMBER</TitleTxt>
        <AverageHoursPerWeekRt>1.00</AverageHoursPerWeekRt>
        <IndividualTrusteeOrDirectorInd>X</IndividualTrusteeOrDirectorInd>
        <ReportableCompFromOrgAmt>0</ReportableCompFromOrgAmt>
        <ReportableCompFromRltdOrgAmt>0</ReportableCompFromRltdOrgAmt>
        <OtherCompensationAmt>0</OtherCompensationAmt>
      </Form990PartVIISectionAGrp>
      <Form990PartVIISectionAGrp>
        <PersonNm>MIKE SCHAFFNER</PersonNm>
        <TitleTxt>BOARD MEMBER</TitleTxt>
        <AverageHoursPerWeekRt>1.00</AverageHoursPerWeekRt>
        <IndividualTrusteeOrDirectorInd>X</IndividualTrusteeOrDirectorInd>
        <ReportableCompFromOrgAmt>0</ReportableCompFromOrgAmt>
        <ReportableCompFromRltdOrgAmt>0</ReportableCompFromRltdOrgAmt>
        <OtherCompensationAmt>0</OtherCompensationAmt>
      </Form990PartVIISectionAGrp>
      <Form990PartVIISectionAGrp>
        <PersonNm>TIM JONES</PersonNm>
        <TitleTxt>CEO</TitleTxt>
        <AverageHoursPerWeekRt>40.00</AverageHoursPerWeekRt>
        <IndividualTrusteeOrDirectorInd>X</IndividualTrusteeOrDirectorInd>
        <OfficerInd>X</OfficerInd>
        <ReportableCompFromOrgAmt>322255</ReportableCompFromOrgAmt>
        <ReportableCompFromRltdOrgAmt>0</ReportableCompFromRltdOrgAmt>
        <OtherCompensationAmt>0</OtherCompensationAmt>
      </Form990PartVIISectionAGrp>
      <Form990PartVIISectionAGrp>
        <PersonNm>RENAE THOMAS</PersonNm>
        <TitleTxt>CFO</TitleTxt>
        <AverageHoursPerWeekRt>40.00</AverageHoursPerWeekRt>
        <OfficerInd>X</OfficerInd>
        <ReportableCompFromOrgAmt>100330</ReportableCompFromOrgAmt>
        <ReportableCompFromRltdOrgAmt>0</ReportableCompFromRltdOrgAmt>
        <OtherCompensationAmt>0</OtherCompensationAmt>
      </Form990PartVIISectionAGrp>
      <Form990PartVIISectionAGrp>
        <PersonNm>RAMONA SLOAN</PersonNm>
        <TitleTxt>CNO</TitleTxt>
        <AverageHoursPerWeekRt>40.00</AverageHoursPerWeekRt>
        <OfficerInd>X</OfficerInd>
        <ReportableCompFromOrgAmt>107981</ReportableCompFromOrgAmt>
        <ReportableCompFromRltdOrgAmt>0</ReportableCompFromRltdOrgAmt>
        <OtherCompensationAmt>0</OtherCompensationAmt>
      </Form990PartVIISectionAGrp>
    </IRS990>
  </ReturnData>
</Return>

Answer 1

There are a number of issues:

• You should specify an exact path instead of // as it is faster.
• The logic of your XQuery is "return the 1st/2nd/3rd... OfficerInd node" when what you want is "return the first OfficerInd contained in the 1st/2nd/3rd... Form990PartVIISectionAGrp node". If you had specified the exact oath you would have noticed this.
• The first CROSS APPLY is unnecessary as you are not using that .nodes rowset.
• Even if you needed that first CROSS APPLY you should feed it into the second one, rather than going back to the root again.
• Is varchar(max) a sensible data type here? Perhaps it should be varchar(30) or even char(1)

WITH XMLNAMESPACES (DEFAULT 'http://www.irs.gov/efile')
SELECT 
  Form990PartVIISectionAGrpOfficerInd0 = 
    c2.value('(Form990PartVIISectionAGrp[1]/OfficerInd/text())[1]','varchar(MAX)')
, Form990PartVIISectionAGrpOfficerInd1 =
    c2.value('(Form990PartVIISectionAGrp[2]/OfficerInd/text())[1]','varchar(MAX)')
, Form990PartVIISectionAGrpOfficerInd2 =
    c2.value('(Form990PartVIISectionAGrp[3]/OfficerInd/text())[1]','varchar(MAX)')
, Form990PartVIISectionAGrpOfficerInd3 =
    c2.value('(Form990PartVIISectionAGrp[4]/OfficerInd/text())[1]','varchar(MAX)')
, Form990PartVIISectionAGrpOfficerInd4 =
    c2.value('(Form990PartVIISectionAGrp[5]/OfficerInd/text())[1]','varchar(MAX)')
, Form990PartVIISectionAGrpOfficerInd5 =
    c2.value('(Form990PartVIISectionAGrp[6]/OfficerInd/text())[1]','varchar(MAX)')
FROM Form990
CROSS APPLY XMLData.nodes('/Return/ReturnData/IRS990') AS t2(c2);

Note that you can also shred all of those nodes into separate rows instead of separate columns. You can get the position using a hack counting all earlier nodes.

WITH XMLNAMESPACES (DEFAULT 'http://www.irs.gov/efile')
SELECT 
  Form990PartVIISectionAGrpOfficerInd = c2.value('(OfficerInd/text())[1]','varchar(MAX)')
, Position = c2.value('let $i:= . return count(../Form990PartVIISectionAGrp[. << $i]) + 1','int')
FROM Form990
CROSS APPLY XMLData.nodes('/Return/ReturnData/IRS990/Form990PartVIISectionAGrp') AS t2(c2)

db<>fiddle