SQL Server中的查询XML

Question

我有一个XML 像这样格式化

<Maximum.Edm.CodeIncludedInCalculation >
  <CalculationsByProvince>
    <Maximum.Edm.IncludedInByProvince  Province="QC">
      <RevenueCodeCalculations CalculationListType="RevenueCode">
        <Maximum.Edm.CalculationIncluded  Code="@Vacation" IsSelected="True" />
        <Maximum.Edm.CalculationIncluded  Code="@RRQ" IsSelected="True" />
        <Maximum.Edm.CalculationIncluded  Code="@EmploymentInsurance" IsSelected="True" />
        [...]
      </RevenueCodeCalculations>
      <TaxableBenefitCalculations CalculationListType="TaxableBenefits">
        <Maximum.Edm.CalculationIncluded  Code="1" IsSelected="False" />
        <Maximum.Edm.CalculationIncluded  Code="AV. VIE FE" IsSelected="True" />
      </TaxableBenefitCalculations>
      <DeductionCodeCalculations CalculationListType="DeductionCode">
        <Maximum.Edm.CalculationIncluded  Code="123" IsSelected="False" />
        <Maximum.Edm.CalculationIncluded  Code="456" IsSelected="True" />
        <Maximum.Edm.CalculationIncluded  Code="AC" IsSelected="False" />
       [...]
      </DeductionCodeCalculations>
    </Maximum.Edm.IncludedInByProvince>
    <Maximum.Edm.IncludedInByProvince  Province="ON">
      [...]
    </Maximum.Edm.IncludedInByProvince>
  </CalculationsByProvince>
</Maximum.Edm.CodeIncludedInCalculation>

我想要创建一个简单的查询，它将返回每个CalculationIncluded和code和IsSelected值。我的最终目标是创建一个存储过程，它将接收一个code、一个province和一个CalculationListType，并返回IsSelected值。

我试图使用来自这里、这里和这里的示例，但我一直得到null或空值。

我尝试使用其中一个示例进行查询：

select * from 
  (select 
     pref.value('(text())[1]', 'varchar(32)') as RoleName
   from 
      Payroll.RevenueCode CROSS APPLY
 IncludeInCalculation.nodes('/Maximum.Edm.CodeIncludedInCalculation/CalculationsByProvince/Maximum.Edm.IncludedInByProvince/RevenueCodeCalculations') AS IncludeInCalculation(pref)
 )  as Result

Answer 1

我的最终目标是创建一个存储过程，它将接收一个代码、一个省和一个CalculationListType，并返回IsSelected值。

这不需要存储过程..。

以下内容将返回1 (= true)或0 (= false)或NULL (=不存在)

DECLARE @Province NVARCHAR(100)='QC';
DECLARE @ListType NVARCHAR(100)='RevenueCode';
DECLARE @Code NVARCHAR(100)='@EmploymentInsurance'

SELECT @xml.value(N'(/Maximum.Edm.CodeIncludedInCalculation
                     /CalculationsByProvince
                     /Maximum.Edm.IncludedInByProvince[@Province=sql:variable("@Province")]
                     /RevenueCodeCalculations[@CalculationListType=sql:variable("@ListType")]
                     /Maximum.Edm.CalculationIncluded[@Code=sql:variable("@Code")]/@IsSelected)[1]',N'bit')