代码之家 › 专栏 › 技术社区 › TenkMan

SQL Server XML嵌套值查询表单990

xml-parsing xml sql-server

TenkMan · 技术社区 · 2 年前

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

我的查询应该返回标记OfficeFind的值。这个标签有几个不同的版本(嵌套值),所以我尝试返回所有的值。以下是对大多数其他标记有效的查询:

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>

0 回复 | 直到 2 年前

Charlieface 2 年前

有很多问题:

您应该指定确切的路径,而不是 // 因为它更快。
XQuery的逻辑是“返回1/2/3。。。 OfficerInd 节点“when you want is”返回第一个 办公用品 包含在第1/2/3页。。。 Form990PartVIISectionAGrp 如果你指定了确切的誓言,你就会注意到这一点。
第一个 CROSS APPLY 是不必要的,因为你没有使用它 .nodes 罗塞特。
即使你先需要 交叉申请 你应该把它注入第二个,而不是再回到根上。
是 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