SQL-解析xml

最新推荐文章于 2023-03-06 14:16:00 发布

笨蛋girl

最新推荐文章于 2023-03-06 14:16:00 发布

阅读量330

点赞数

本文链接：https://blog.csdn.net/u011791378/article/details/102518760

版权

DECLARE @ItemMes XML 

SET @ItemMes=N'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfFund_bill_list>
  <fund_bill_list>
    <amount>1980.01</amount>
    <fundChannel>PCREDIT</fundChannel>
  </fund_bill_list>
  <fund_bill_list>
    <amount>0.01</amount>
    <fundChannel>acttext</fundChannel>
  </fund_bill_list>
</ArrayOfFund_bill_list>
'




--获取xml字段
select @ItemMes

--判断节点是否存在
select @ItemMes.exist('/ArrayOfFund_bill_list')


--获取所有fund_bill_list的节点
select @ItemMes.query('/ArrayOfFund_bill_list/fund_bill_list')

--
select Amount,FundChannel from (
SELECT T.c.value('(amount/text())[1]','NVARCHAR(50)') as Amount,T.c.value('(fundChannel/text())[1]','NVARCHAR(50)') as FundChannel FROM @ItemMes.nodes('/ArrayOfFund_bill_list/fund_bill_list') AS T(c)
) as table1