Query XML in SQL Server

Question

<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>

I want to create a simple query that will return each CalculationIncluded with the code and the IsSelected value. My final goal is to create a stored procedure that will receive a code, a province and a CalculationListType and return the IsSelected value.

I tried to use example from here, here and here but I keep getting null or empty values.

Query I tried to make using one of the example :

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

Gottfried Lesigang · Accepted Answer · 2017-04-05 14:28:34Z

2

My final goal is to create a stored procedure that will receive a code, a province and a CalculationListType and return the IsSelected value.

This should not need a stored procedure...

The following will return 1 (= true) or 0 (= false) or NULL (= not existing)

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')

answered Apr 5, 2017 at 14:28

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Cappelletti Over a year ago

Boy did I misunderstand the question +1

Collectives™ on Stack Overflow

Query XML in SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related