SQL Server XML 嵌套值查询表单 990-解网

问：

我的XML文件如下所列。XML 文档位于名为 form990 的表中

我的查询应该返回标签 OfficerInd 的值。此标记有几个不同的版本（嵌套值），因此我尝试返回所有值。以下是对大多数其他标签有效的查询：

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)

我的查询返回

105  NULL     X    X    X    NULL NULL NULL NULL

但应该回来

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>

sql-server xml xml-parsing xquery-sql

答：

1赞 Charlieface 4/27/2022 #1

存在许多问题：

您应该指定一个确切的路径，而不是因为它更快。//
XQuery 的逻辑是“返回 1st/2nd/3rd... node“，当您想要的是”返回包含在 1st/2nd/3rd 中的第一个...... 节点”。如果你指定了确切的誓言，你就会注意到这一点。OfficerIndOfficerIndForm990PartVIISectionAGrp
第一个是不必要的，因为您没有使用该行集。CROSS APPLY.nodes
即使你首先需要它，你也应该把它喂入第二个，而不是再次回到根部。CROSS APPLY
这里是明智的数据类型吗？也许它应该是，甚至varchar(max)varchar(30)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);

请注意，您还可以将所有这些节点拆分为单独的行，而不是单独的列。您可以使用计算所有早期节点的黑客来获得位置。

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

上一个：在没有循环的情况下将 Xml 数据转换为 sql server 中的表？

下一个：无法解析 XML

SQL Server XML 嵌套值查询表单 990

SQL Server XML Nested Values Query Form 990

评论