如何在Sql Server2005中获取xml中节点的值

Question

表A中存储的是xml类型的数据
如：
<PolicyStandard_FZK_YWYXRD>
  <Money_Upper_Limit>100000</Money_Upper_Limit>
  <Time_Upper_Limit>20</Time_Upper_Limit>
</PolicyStandard_FZK_YWYXRD>该如何获取<Money_Upper_Limit>,<Time_Upper_Limit>中的值

Answer 1

SELECT 
    xmlcol.value('(/PolicyStandard_FZK_YWYXRD/Money_Upper_Limit)[1]','int'),
    xmlcol.value('(/PolicyStandard_FZK_YWYXRD/Time_Upper_Limit)[1]','int')
FROM tb

Answer 2

select  * from  a for XML path('\PolicyStandard_FZK_YWYXRD')

Answer 3

declare @ss xml
set @ss = '<PolicyStandard_FZK_YWYXRD>
  <Money_Upper_Limit>100000</Money_Upper_Limit>
  <Time_Upper_Limit>20</Time_Upper_Limit>
</PolicyStandard_FZK_YWYXRD>'SELECT @ss.value('data(/PolicyStandard_FZK_YWYXRD/Money_Upper_Limit)[1]', 'varchar(300)'),
@ss.value('data(/PolicyStandard_FZK_YWYXRD/Time_Upper_Limit)[1]', 'varchar(300)')