sql server解析xml字段

declare @t nvarchar(max)
set @t='<ApplicationInfo xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ApplicationDate>2019-09-24T14:06:03.097</ApplicationDate>
  <QueryReason>1</QueryReason>
  <Mobile>15801606585</Mobile>
  <ApplyAmount>1000.00</ApplyAmount>
  <CustomerType>PA</CustomerType>
</ApplicationInfo>'
select cast(@t as xml).value('data(/ApplicationInfo/CustomerType)[1]', 'varchar(30)')CustomerType

DECLARE @x XML
SELECT @x = '
<People>
  <dongsheng>
    <Info Name="Email">dongsheng@xxyy.com</Info>
    <Info Name="Phone">678945546</Info>
    <Info Name="qq">36575</Info>
  </dongsheng>
</People>'
-- 方法1
SELECT @x.value('data(/People/dongsheng/Info[@Name="Email"])[1]', 'varchar(30)')
-- 方法2
SELECT @x.value('(/People/dongsheng/Info[@Name="Email"])[1]', 'varchar(30)')
-- 方法3
SELECT C.value('.','varchar(30)') FROM @x.nodes('/People/dongsheng/Info[@Name="Email"]') T(C)
-- 方法4
SELECT C.value('(Info[@Name="Email"])[1]','varchar(30)') FROM @x.nodes('/People/dongsheng') T(C)
-- 方法5
SELECT C.value('(dongsheng/Info[@Name="Email"])[1]','varchar(30)') FROM @x.nodes('/People') T(C)
-- 方法6
SELECT C.value('.','varchar(30)') FROM @x.nodes('/People/dongsheng/Info') T(C) WHERE C.value('(.[@Name="Email"])[1]','varchar(30)') IS NOT NULL
-- 方法7
SELECT C.value('.','varchar(30)') FROM @x.nodes('/People/dongsheng/Info') T(C)

 

参考

　　SqlServer解析XML数据

　　SQL Server解析XML数据的方法详解