Parsing XML in SQL Server with duplicate tags-CodePudding

Here is the DDL and table structure of the XML that I am working on. I'm able to parse a lot of the basic XML now but now I have a tag that repeats several times with this same name and then with the same child tags. I would like to get a separate column for each combination of parent and child tags and somehow have different names for each one of those. Here is the tag that repeats several times along with the four child tags.

  <ProgSrvcAccomActyOtherGrp>
        <ExpenseAmt>338351</ExpenseAmt>
        <GrantAmt>0</GrantAmt>
        <RevenueAmt>0</RevenueAmt>
        <Desc>Equipment</Desc>
      </ProgSrvcAccomActyOtherGrp>

Again, thank you!

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, XMLData XML);
INSERT INTO @tbl (XMLData) VALUES
('<?xml version="1.0" encoding="utf-8"?>
<Return returnVersion="2019v5.0" xmlns="http://www.irs.gov/efile" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ReturnHeader binaryAttachmentCnt="0">
    <ReturnTs>2021-02-11T21:27:00-07:00</ReturnTs>
    <TaxPeriodEndDt>2020-06-30</TaxPeriodEndDt>
    <ReturnTypeCd>990</ReturnTypeCd>
    <TaxPeriodBeginDt>2019-07-01</TaxPeriodBeginDt>
    <Filer>
      <EIN>936022772</EIN>
      <BusinessName>
        <BusinessNameLine1Txt>OREGON STATE UNIVERSITY FOUNDATION</BusinessNameLine1Txt>
      </BusinessName>
      <BusinessNameControlTxt>OREG</BusinessNameControlTxt>
      <PhoneNum>5417374218</PhoneNum>
      <USAddress>
        <AddressLine1Txt>4238 SW Research Way</AddressLine1Txt>
        <CityNm>Corvallis</CityNm>
        <StateAbbreviationCd>OR</StateAbbreviationCd>
        <ZIPCd>973331068</ZIPCd>
      </USAddress>
    </Filer>
    <BusinessOfficerGrp>
      <PersonNm>Steven Schauble</PersonNm>
      <PersonTitleTxt>Vice President &amp; CFO</PersonTitleTxt>
      <PhoneNum>5417377499</PhoneNum>
      <SignatureDt>2021-02-12</SignatureDt>
    </BusinessOfficerGrp>
    <FilingSecurityInformation>
      <IPAddress>
        <IPv4AddressTxt>73.25.170.86</IPv4AddressTxt>
      </IPAddress>
    </FilingSecurityInformation>
    <TaxYr>2019</TaxYr>
    <BuildTS>2021-01-29 14:40:06Z</BuildTS>
  </ReturnHeader>
  <ReturnData documentCnt="11">
    <IRS990 documentId="R000001" referenceDocumentId="R000010" softwareId="19009572" softwareVersionNum="v1.00">
      <PrincipalOfficerNm>Shawn Scoville</PrincipalOfficerNm>
      <USAddress>
        <AddressLine1Txt>4238 SW Research Way</AddressLine1Txt>
        <CityNm>Corvallis</CityNm>
        <StateAbbreviationCd>OR</StateAbbreviationCd>
        <ZIPCd>973331068</ZIPCd>
      </USAddress>
      <GrossReceiptsAmt>336252171</GrossReceiptsAmt>
      <GroupReturnForAffiliatesInd>0</GroupReturnForAffiliatesInd>
      <Organization501c3Ind>X</Organization501c3Ind>
      <WebsiteAddressTxt>www.osufoundation.org</WebsiteAddressTxt>
      <TypeOfOrganizationCorpInd>X</TypeOfOrganizationCorpInd>
      <FormationYr>1947</FormationYr>
      <LegalDomicileStateCd>OR</LegalDomicileStateCd>
      <ActivityOrMissionDesc>The OSU Foundation partners with Oregon State University to engage our community, inspire investment, and steward resources to enhance the university''s excellence and impact.</ActivityOrMissionDesc>
      <VotingMembersGoverningBodyCnt>39</VotingMembersGoverningBodyCnt>
      <VotingMembersIndependentCnt>36</VotingMembersIndependentCnt>
      <TotalEmployeeCnt>183</TotalEmployeeCnt>
      <TotalVolunteersCnt>39</TotalVolunteersCnt>
      <TotalGrossUBIAmt>0</TotalGrossUBIAmt>
      <NetUnrelatedBusTxblIncmAmt>-568422</NetUnrelatedBusTxblIncmAmt>
      <PYContributionsGrantsAmt>74724577</PYContributionsGrantsAmt>
      <CYContributionsGrantsAmt>93028322</CYContributionsGrantsAmt>
      <PYProgramServiceRevenueAmt>510476</PYProgramServiceRevenueAmt>
      <CYProgramServiceRevenueAmt>1338931</CYProgramServiceRevenueAmt>
      <PYInvestmentIncomeAmt>31179603</PYInvestmentIncomeAmt>
      <CYInvestmentIncomeAmt>20095789</CYInvestmentIncomeAmt>
      <PYOtherRevenueAmt>21307467</PYOtherRevenueAmt>
      <CYOtherRevenueAmt>17916447</CYOtherRevenueAmt>
      <PYTotalRevenueAmt>127722123</PYTotalRevenueAmt>
      <CYTotalRevenueAmt>132379489</CYTotalRevenueAmt>
      <PYGrantsAndSimilarPaidAmt>0</PYGrantsAndSimilarPaidAmt>
      <CYGrantsAndSimilarPaidAmt>0</CYGrantsAndSimilarPaidAmt>
      <PYBenefitsPaidToMembersAmt>0</PYBenefitsPaidToMembersAmt>
      <CYBenefitsPaidToMembersAmt>0</CYBenefitsPaidToMembersAmt>
      <PYSalariesCompEmpBnftPaidAmt>31629477</PYSalariesCompEmpBnftPaidAmt>
      <CYSalariesCompEmpBnftPaidAmt>31621515</CYSalariesCompEmpBnftPaidAmt>
      <PYTotalProfFndrsngExpnsAmt>0</PYTotalProfFndrsngExpnsAmt>
      <CYTotalProfFndrsngExpnsAmt>0</CYTotalProfFndrsngExpnsAmt>
      <CYTotalFundraisingExpenseAmt>17996976</CYTotalFundraisingExpenseAmt>
      <PYOtherExpensesAmt>81790834</PYOtherExpensesAmt>
      <CYOtherExpensesAmt>92045058</CYOtherExpensesAmt>
      <PYTotalExpensesAmt>113420311</PYTotalExpensesAmt>
      <CYTotalExpensesAmt>123666573</CYTotalExpensesAmt>
      <PYRevenuesLessExpensesAmt>14301812</PYRevenuesLessExpensesAmt>
      <CYRevenuesLessExpensesAmt>8712916</CYRevenuesLessExpensesAmt>
      <TotalAssetsBOYAmt>816417531</TotalAssetsBOYAmt>
      <TotalAssetsEOYAmt>833330950</TotalAssetsEOYAmt>
      <TotalLiabilitiesBOYAmt>89894344</TotalLiabilitiesBOYAmt>
      <TotalLiabilitiesEOYAmt>94381459</TotalLiabilitiesEOYAmt>
      <NetAssetsOrFundBalancesBOYAmt>726523187</NetAssetsOrFundBalancesBOYAmt>
      <NetAssetsOrFundBalancesEOYAmt>738949491</NetAssetsOrFundBalancesEOYAmt>
      <InfoInScheduleOPartIIIInd>X</InfoInScheduleOPartIIIInd>
      <MissionDesc>MISSION: The OSU Foundation partners with Oregon State University to engage our community, inspire investment, and steward resources to enhance the university''s excellence and impact. VISION: To create a better world by inspiring support of - and for - Oregon State University.</MissionDesc>
      <SignificantNewProgramSrvcInd>0</SignificantNewProgramSrvcInd>
      <SignificantChangeInd>0</SignificantChangeInd>
      <ExpenseAmt>12674058</ExpenseAmt>
      <GrantAmt>0</GrantAmt>
      <RevenueAmt>0</RevenueAmt>
      <Desc>SCHOLARSHIPS &amp; OTHER STUDENT SUPPORT: The OSU Foundation distributed these amounts, in accordance with donor intent, to the University in FY20 to support students through scholarships, fellowships, and other awards. Through the years, donors have created over 1,975 unique scholarship and fellowship funds of various types and amounts at the OSU Foundation. These funds help ensure that an OSU education is accessible to qualified students, regardless of economic circumstances, and allow the University to recruit and retain the best and brightest students from Oregon and beyond.</Desc>
      <ProgSrvcAccomActy2Grp>
        <ExpenseAmt>39071677</ExpenseAmt>
        <GrantAmt>0</GrantAmt>
        <RevenueAmt>0</RevenueAmt>
        <Desc>CAPITAL PROGRAMS: The OSU Foundation distributed these amounts, in accordance with donor intent, to the University, in FY20, to support the construction or renovation of campus facilities. Major projects in FY20 included: the Marine Studies Building in Newport, the Oregon Forest Science Complex, the Arts &amp; Education Complex, renovations to: Merryfield Hall, President''s residence, Valley Football Center, P Wayne Valley Sports Performance Center, Reser Stadium, Gill Colosseum, and the softball complex.</Desc>
      </ProgSrvcAccomActy2Grp>
      <ProgSrvcAccomActy3Grp>
        <ExpenseAmt>17167254</ExpenseAmt>
        <GrantAmt>0</GrantAmt>
        <RevenueAmt>0</RevenueAmt>
        <Desc>INSTRUCTION AND RESEARCH: The OSU Foundation distributed these amounts, in accordance with donor intent, to the University, in FY20, to support teaching and research. With 150 endowed faculty positions at the University, donors to the OSU Foundation provide critically needed private funds to help recruit and retain world-class talent in the classrooms and labs. During the year, Foundation funds helped support OSU''s internationally recognized programs in marine sciences, forestry, and agricultural sciences, among others.</Desc>
      </ProgSrvcAccomActy3Grp>
      <ProgSrvcAccomActyOtherGrp>
        <ExpenseAmt>4888</ExpenseAmt>
        <GrantAmt>0</GrantAmt>
        <RevenueAmt>0</RevenueAmt>
        <Desc>Accounting services</Desc>
      </ProgSrvcAccomActyOtherGrp>
      <ProgSrvcAccomActyOtherGrp>
        <ExpenseAmt>31223</ExpenseAmt>
        <GrantAmt>0</GrantAmt>
        <RevenueAmt>0</RevenueAmt>
        <Desc>Advertising/Promotion</Desc>
      </ProgSrvcAccomActyOtherGrp>
      <ProgSrvcAccomActyOtherGrp>
        <ExpenseAmt>290774</ExpenseAmt>
        <GrantAmt>0</GrantAmt>
        <RevenueAmt>0</RevenueAmt>
        <Desc>Books, Periodicals &amp; Subscriptions</Desc>
      </ProgSrvcAccomActyOtherGrp>
      <ProgSrvcAccomActyOtherGrp>
        <ExpenseAmt>750</ExpenseAmt>
        <GrantAmt>0</GrantAmt>
        <RevenueAmt>0</RevenueAmt>
        <Desc>Capital Outlay</Desc>
      </ProgSrvcAccomActyOtherGrp>
      <ProgSrvcAccomActyOtherGrp>
        <ExpenseAmt>123611</ExpenseAmt>
        <GrantAmt>0</GrantAmt>
        <RevenueAmt>472080</RevenueAmt>
        <Desc>Conferences, Meetings and Seminars</Desc>
      </ProgSrvcAccomActyOtherGrp>
      <ProgSrvcAccomActyOtherGrp>
        <ExpenseAmt>541970</ExpenseAmt>
        <GrantAmt>0</GrantAmt>
        <RevenueAmt>0</RevenueAmt>
        <Desc>Donor Cultivation/Stewardship</Desc>
      </ProgSrvcAccomActyOtherGrp>
      <ProgSrvcAccomActyOtherGrp>
        <ExpenseAmt>19971</ExpenseAmt>
        <GrantAmt>0</GrantAmt>
        <RevenueAmt>87480</RevenueAmt>
        <Desc>Dues and Memberships</Desc>
      </ProgSrvcAccomActyOtherGrp>
      <ProgSrvcAccomActyOtherGrp>
        <ExpenseAmt>338351</ExpenseAmt>
        <GrantAmt>0</GrantAmt>
        <RevenueAmt>0</RevenueAmt>
        <Desc>Equipment</Desc>
      </ProgSrvcAccomActyOtherGrp>
      <ProgSrvcAccomActyOtherGrp>
        <ExpenseAmt>158235</ExpenseAmt>
        <GrantAmt>0</GrantAmt>
        <RevenueAmt>0</RevenueAmt>
        <Desc>Information Technology</Desc>
      </ProgSrvcAccomActyOtherGrp>
      <ProgSrvcAccomActyOtherGrp>
        <ExpenseAmt>594951</ExpenseAmt>
        <GrantAmt>0</GrantAmt>
        <RevenueAmt>0</RevenueAmt>
        <Desc>Insurance</Desc>
      </ProgSrvcAccomActyOtherGrp>
        </IRS990>
    </ReturnData>
</Return>');

WITH XMLNAMESPACES (DEFAULT 'http://www.irs.gov/efile')
SELECT ID
    , ProgSrvcAccomActyOtherGrp = c.value('(ProgSrvcAccomActyOtherGrp/text())[1]', 'BIGINT')
FROM @tbl
    CROSS APPLY XMLData.nodes('/Return/ReturnData/IRS990') AS t(c);

CodePudding user response：

Omitting your XML blob, take a look at this:

WITH XMLNAMESPACES (DEFAULT 'http://www.irs.gov/efile')
SELECT c.query('.')
FROM @tbl
    CROSS APPLY XMLData.nodes('/Return/ReturnData/IRS990/ProgSrvcAccomActyOtherGrp') AS t(c);

That gets all of the ProgSrvcAccomActyOtherGrp nodes out of the XML. If I'm understanding what you're describing as the next step, SQL isn't really set up for that. That is, it sounds like you're describing a result set with an unknown number of columns (i.e. you want N columns per ProgSrvcAccomActyOtherGrp node, but the number of nodes is unknown a priori). Search for "dynamic pivot" and buckle up.

CodePudding user response：

Changing the number of the element worked! Thank you always learning!

   CREATE VIEW VanHalen6AAA
    AS
    WITH XMLNAMESPACES (DEFAULT 'http://www.irs.gov/efile')
    SELECT ID, FilingYear, FilingPeriod, FilingType, [FileName]
    
    , ProgSrvcAccomActy3GrpExpenseAmt1 = c2.value('(//ExpenseAmt/text())[1]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpGrantAmt1 = c2.value('(//GrantAmt/text())[1]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpRevenueAmt1 = c2.value('(//RevenueAmt/text())[1]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpDesc1 = c2.value('(//Desc/text())[1]','varchar(MAX)')
    
    , ProgSrvcAccomActy3GrpExpenseAmt2 = c2.value('(//ExpenseAmt/text())[2]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpGrantAmt2 = c2.value('(//GrantAmt/text())[2]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpRevenueAmt2 = c2.value('(//RevenueAmt/text())[2]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpDesc2 = c2.value('(//Desc/text())[2]','varchar(MAX)')
    
    , ProgSrvcAccomActy3GrpExpenseAmt3 = c2.value('(//ExpenseAmt/text())[3]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpGrantAmt3 = c2.value('(//GrantAmt/text())[3]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpRevenueAmt3 = c2.value('(//RevenueAmt/text())[3]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpDesc3 = c2.value('(//Desc/text())[3]','varchar(MAX)')
    
    , ProgSrvcAccomActy3GrpExpenseAmt4 = c2.value('(//ExpenseAmt/text())[4]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpGrantAmt4 = c2.value('(//GrantAmt/text())[4]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpRevenueAmt4 = c2.value('(//RevenueAmt/text())[4]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpDesc4 = c2.value('(//Desc/text())[4]','varchar(MAX)')
    
    , ProgSrvcAccomActy3GrpExpenseAmt5 = c2.value('(//ExpenseAmt/text())[5]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpGrantAmt5 = c2.value('(//GrantAmt/text())[5]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpRevenueAmt5 = c2.value('(//RevenueAmt/text())[5]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpDesc5 = c2.value('(//Desc/text())[5]','varchar(MAX)')
    
    , ProgSrvcAccomActy3GrpExpenseAmt6 = c2.value('(//ExpenseAmt/text())[6]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpGrantAmt6 = c2.value('(//GrantAmt/text())[6]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpRevenueAmt6 = c2.value('(//RevenueAmt/text())[6]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpDesc6 = c2.value('(//Desc/text())[6]','varchar(MAX)')
    
    , ProgSrvcAccomActy3GrpExpenseAmt7 = c2.value('(//ExpenseAmt/text())[7]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpGrantAmt7 = c2.value('(//GrantAmt/text())[7]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpRevenueAmt7 = c2.value('(//RevenueAmt/text())[7]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpDesc7 = c2.value('(//Desc/text())[7]','varchar(MAX)')
    
    , ProgSrvcAccomActy3GrpExpenseAmt8 = c2.value('(//ExpenseAmt/text())[8]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpGrantAmt8 = c2.value('(//GrantAmt/text())[8]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpRevenueAmt8 = c2.value('(//RevenueAmt/text())[8]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpDesc8 = c2.value('(//Desc/text())[8]','varchar(MAX)')
    
    , ProgSrvcAccomActy3GrpExpenseAmt9 = c2.value('(//ExpenseAmt/text())[9]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpGrantAmt9 = c2.value('(//GrantAmt/text())[9]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpRevenueAmt9 = c2.value('(//RevenueAmt/text())[9]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpDesc9 = c2.value('(//Desc/text())[9]','varchar(MAX)')
    
    , ProgSrvcAccomActy3GrpExpenseAmt10 = c2.value('(//ExpenseAmt/text())[10]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpGrantAmt10 = c2.value('(//GrantAmt/text())[10]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpRevenueAmt10 = c2.value('(//RevenueAmt/text())[10]','varchar(MAX)')
    , ProgSrvcAccomActy3GrpDesc10 = c2.value('(//Desc/text())[10]','varchar(MAX)')
    
    FROM Form990
        CROSS APPLY XMLData.nodes('/Return') AS t(c) 
        CROSS APPLY XMLData.nodes('//Return//ReturnData//IRS990//ProgSrvcAccomActy3Grp') AS t2(c2);