Server将XML列解析为表格式- IRS 990

Question

表中有三列，第三列是有效的XML类型。下面列出了XML的示例，其中包括几个标记。此XML是从IRS窗体990下载的。我正在尝试得到一些标签，以表格格式返回。我在下面完成了这个查询，它运行，但是它不返回任何值。我可能在这条路上做错了什么，但我似乎弄不明白。

这是我的桌子结构：

CREATE TABLE dbo.XMLFilesTable
(
Id INT IDENTITY PRIMARY KEY,
FileName VARCHAR(100),
XMLData XML,
LoadedDateTime DATETIME
)

GO

以下是xml列中的xml：

<?xml version="1.0" encoding="utf-8"?>
<Return xmlns="http://www.irs.gov/efile" returnVersion="2019v5.1">
  <ReturnData documentCnt="7">
    <IRS990 documentId="IRS990">
      <PrincipalOfficerNm>Fr Francis Pizzarelli</PrincipalOfficerNm>
      <USAddress>
        <AddressLine1Txt>One High Street</AddressLine1Txt>
        <CityNm>Port Jefferson</CityNm>
        <StateAbbreviationCd>NY</StateAbbreviationCd>
        <ZIPCd>11777</ZIPCd>
      </USAddress>
      <GrossReceiptsAmt>6463631</GrossReceiptsAmt>
      <GroupReturnForAffiliatesInd>false</GroupReturnForAffiliatesInd>
      <Organization501c3Ind>X</Organization501c3Ind>
      <TypeOfOrganizationCorpInd>X</TypeOfOrganizationCorpInd>
      <FormationYr>1980</FormationYr>
      <LegalDomicileStateCd>NY</LegalDomicileStateCd>
      <ActivityOrMissionDesc>To provide help to individuals who can not find help in the form of housing, counseling, and other support so that they can eventually live productive independent lives. Hope House provides hope, care and compassion to nearly 2,000 individuals in need each month.</ActivityOrMissionDesc>
      <VotingMembersGoverningBodyCnt>8</VotingMembersGoverningBodyCnt>
      <VotingMembersIndependentCnt>7</VotingMembersIndependentCnt>
      <TotalEmployeeCnt>122</TotalEmployeeCnt>
      <TotalGrossUBIAmt>0</TotalGrossUBIAmt>
      <NetUnrelatedBusTxblIncmAmt>0</NetUnrelatedBusTxblIncmAmt>
      <PYContributionsGrantsAmt>3307653</PYContributionsGrantsAmt>
      <CYContributionsGrantsAmt>4963545</CYContributionsGrantsAmt>
      <CYProgramServiceRevenueAmt>0</CYProgramServiceRevenueAmt>
      <PYInvestmentIncomeAmt>25158</PYInvestmentIncomeAmt>
      <CYInvestmentIncomeAmt>122678</CYInvestmentIncomeAmt>
      <PYOtherRevenueAmt>1302778</PYOtherRevenueAmt>
      <CYOtherRevenueAmt>1016131</CYOtherRevenueAmt>
      <PYTotalRevenueAmt>4635589</PYTotalRevenueAmt>
      <CYTotalRevenueAmt>6102354</CYTotalRevenueAmt>
      <CYGrantsAndSimilarPaidAmt>0</CYGrantsAndSimilarPaidAmt>
      <CYBenefitsPaidToMembersAmt>0</CYBenefitsPaidToMembersAmt>
      <PYSalariesCompEmpBnftPaidAmt>3294184</PYSalariesCompEmpBnftPaidAmt>
      <CYSalariesCompEmpBnftPaidAmt>3352675</CYSalariesCompEmpBnftPaidAmt>
      <CYTotalProfFndrsngExpnsAmt>0</CYTotalProfFndrsngExpnsAmt>
      <CYTotalFundraisingExpenseAmt>501828</CYTotalFundraisingExpenseAmt>
      <PYOtherExpensesAmt>1793710</PYOtherExpensesAmt>

我尝试了以下代码并运行，但没有返回任何内容：

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX), @rootxmlns VARCHAR(100)

SELECT @XML = XMLData FROM XMLFilesTable
SET @rootxmlns = '<Return xmlns="http://www.irs.gov/efile"/'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


SELECT PYContributionsGrantsAmt, CYContributionsGrantsAmt, PYInvestmentIncomeAmt
FROM OPENXML(@hDoc, 'Return/ReturnData')
WITH
(
PYContributionsGrantsAmt [BIGINT] 'PYContributionsGrantsAmt',
CYContributionsGrantsAmt [BIGINT] 'CYContributionsGrantsAmt',
PYInvestmentIncomeAmt [BIGINT] 'PYInvestmentIncomeAmt'
)

EXEC sp_xml_removedocument @hDoc
GO

我的想法是，在打开xml之后的路径是不正确的。

对此有什么想法吗？

Answer 1

微软专有的OPENXML及其伙伴sp_xml_preparedocument和sp_xml_removedocument只是为了与过时的Server 2000向后兼容而保留。它们的使用只会减少到极少数附带的情况。

从Server 2005开始，强烈建议重写SQL并将其切换到XQuery。

SQL

-- 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 xmlns="http://www.irs.gov/efile" returnVersion="2019v5.1">
    <ReturnData documentCnt="7">
        <IRS990 documentId="IRS990">
            <PrincipalOfficerNm>Fr Francis Pizzarelli</PrincipalOfficerNm>
            <USAddress>
                <AddressLine1Txt>One High Street</AddressLine1Txt>
                <CityNm>Port Jefferson</CityNm>
                <StateAbbreviationCd>NY</StateAbbreviationCd>
                <ZIPCd>11777</ZIPCd>
            </USAddress>
            <GrossReceiptsAmt>6463631</GrossReceiptsAmt>
            <GroupReturnForAffiliatesInd>false</GroupReturnForAffiliatesInd>
            <Organization501c3Ind>X</Organization501c3Ind>
            <TypeOfOrganizationCorpInd>X</TypeOfOrganizationCorpInd>
            <FormationYr>1980</FormationYr>
            <LegalDomicileStateCd>NY</LegalDomicileStateCd>
            <ActivityOrMissionDesc>To provide help to individuals who can not find help in the form of housing, counseling, and other support so that they can eventually live productive independent lives. Hope House provides hope, care and compassion to nearly 2,000 individuals in need each month.</ActivityOrMissionDesc>
            <VotingMembersGoverningBodyCnt>8</VotingMembersGoverningBodyCnt>
            <VotingMembersIndependentCnt>7</VotingMembersIndependentCnt>
            <TotalEmployeeCnt>122</TotalEmployeeCnt>
            <TotalGrossUBIAmt>0</TotalGrossUBIAmt>
            <NetUnrelatedBusTxblIncmAmt>0</NetUnrelatedBusTxblIncmAmt>
            <PYContributionsGrantsAmt>3307653</PYContributionsGrantsAmt>
            <CYContributionsGrantsAmt>4963545</CYContributionsGrantsAmt>
            <CYProgramServiceRevenueAmt>0</CYProgramServiceRevenueAmt>
            <PYInvestmentIncomeAmt>25158</PYInvestmentIncomeAmt>
            <CYInvestmentIncomeAmt>122678</CYInvestmentIncomeAmt>
            <PYOtherRevenueAmt>1302778</PYOtherRevenueAmt>
            <CYOtherRevenueAmt>1016131</CYOtherRevenueAmt>
            <PYTotalRevenueAmt>4635589</PYTotalRevenueAmt>
            <CYTotalRevenueAmt>6102354</CYTotalRevenueAmt>
            <CYGrantsAndSimilarPaidAmt>0</CYGrantsAndSimilarPaidAmt>
            <CYBenefitsPaidToMembersAmt>0</CYBenefitsPaidToMembersAmt>
            <PYSalariesCompEmpBnftPaidAmt>3294184</PYSalariesCompEmpBnftPaidAmt>
            <CYSalariesCompEmpBnftPaidAmt>3352675</CYSalariesCompEmpBnftPaidAmt>
            <CYTotalProfFndrsngExpnsAmt>0</CYTotalProfFndrsngExpnsAmt>
            <CYTotalFundraisingExpenseAmt>501828</CYTotalFundraisingExpenseAmt>
            <PYOtherExpensesAmt>1793710</PYOtherExpensesAmt>
        </IRS990>
    </ReturnData>
</Return>');

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