SQL - Query xml attribute

 

SELECT TOP 100 s.owner_id, mpln_key, mpln_member, mpln_owner, pln_key , pln_cb_id ,

asset_mix.value('(/asset_mix/mem_plan/@market_value)[1]','decimal(18,2)') as market_value,

asset_mix.value('(/asset_mix/mem_plan/@plan_allocate_rating)[1]','varchar(2)') as plan_allocate_rating,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Short Term (GTD Investments)"]/@investment_type_EN)[1]','varchar(30)') as investment_type_EN,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Short Term (GTD Investments)"]/@asset_allocation)[1]','varchar(30)') as asset_allocation,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Global Equity"]/@investment_type_EN)[1]','varchar(30)') as investment_type_EN,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Global Equity"]/@asset_allocation)[1]','varchar(30)') as asset_allocation,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Canadian Equity"]/@investment_type_EN)[1]','varchar(30)') as investment_type_EN,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Canadian Equity"]/@asset_allocation)[1]','varchar(30)') as asset_allocation,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Bonds"]/@investment_type_EN)[1]','varchar(30)') as investment_type_EN,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Bonds"]/@asset_allocation)[1]','varchar(30)') as asset_allocation

 

from [DC4Reports].[dbo].[member_plan_wrapper_extraction] as s

where owner_id= 3620