XML解析

Question

if OBJECT_ID('t') is not null drop table tcreate table t 
(
id int,
content xml
)insert into t
select 1,'<Root>
<Columns2035 title="姓名">代岳强</Columns2035>
<Columns100000000 title="客户姓名">代岳强</Columns100000000>
<Columns100000001 title="主要联系方式">15652179778</Columns100000001>
<Columns100000002 title="备用联系方式">15652179778</Columns100000002>
<Columns100000003 title="民族">汉族</Columns100000003>
<Columns100000004 title="教育程度">博士后</Columns100000004>
</Root>'
union
select 1,'<Root>
<Columns2035 title="姓名">代岳强</Columns2035>
<Columns100000000 title="客户姓名">代岳强</Columns100000000>
<Columns100000001 title="主要联系方式">15652179778</Columns100000001>
<Columns100000002 title="备用联系方式">15652179778</Columns100000002>
<Columns100000003 title="民族">汉族</Columns100000003>
<Columns100000004 title="教育程度">博士后</Columns100000004>
<Columns100000005 title="公司名称">未知</Columns100000005>
<Columns100000006 title="公司地址">未知</Columns100000006>
<Columns100000007 title="公司领导">未知</Columns100000007>
</Root>'
union 
select 1,'<Root>
<Columns2035 title="姓名">代岳强</Columns2035>
<Columns100000000 title="客户姓名">代岳强</Columns100000000>
<Columns100000001 title="主要联系方式">15652179778</Columns100000001>
<Columns100000002 title="备用联系方式">15652179778</Columns100000002>
<Columns100000003 title="民族">汉族</Columns100000003>
<Columns100000004 title="教育程度">博士后</Columns100000004>
<Columns100000005 title="入职年份">未知</Columns100000005>
<Columns100000006 title="关注行业">未知</Columns100000006>
</Root>'select * from t
/*
简介：此表中包含多条记录，要求查询出所有的title值。要求输出结果为：
title
姓名
客户姓名
主要联系方式
备用联系方式
民族
教育程度
公司名称
公司地址
公司领导
入职年份
关注行业
*/

Answer 1

WITH x  AS 
(SELECT id,CONVERT(VARCHAR(MAX), CONTENT.query('data(/Root/*/@title)')) value
FROM t 
)SELECT DISTINCT A.id, B.value
FROM(SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ' ', '</v><v>') + '</v></root>') FROM x
)A
OUTER APPLY(
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)B

Answer 2

create table t 
(id int, content xml)insert into t
select 1,'<Root>
<Columns2035 title="姓名">代岳强</Columns2035>
<Columns100000000 title="客户姓名">代岳强</Columns100000000>
<Columns100000001 title="主要联系方式">15652179778</Columns100000001>
<Columns100000002 title="备用联系方式">15652179778</Columns100000002>
<Columns100000003 title="民族">汉族</Columns100000003>
<Columns100000004 title="教育程度">博士后</Columns100000004>
</Root>'
union
select 1,'<Root>
<Columns2035 title="姓名">代岳强</Columns2035>
<Columns100000000 title="客户姓名">代岳强</Columns100000000>
<Columns100000001 title="主要联系方式">15652179778</Columns100000001>
<Columns100000002 title="备用联系方式">15652179778</Columns100000002>
<Columns100000003 title="民族">汉族</Columns100000003>
<Columns100000004 title="教育程度">博士后</Columns100000004>
<Columns100000005 title="公司名称">未知</Columns100000005>
<Columns100000006 title="公司地址">未知</Columns100000006>
<Columns100000007 title="公司领导">未知</Columns100000007>
</Root>'
union 
select 1,'<Root>
<Columns2035 title="姓名">代岳强</Columns2035>
<Columns100000000 title="客户姓名">代岳强</Columns100000000>
<Columns100000001 title="主要联系方式">15652179778</Columns100000001>
<Columns100000002 title="备用联系方式">15652179778</Columns100000002>
<Columns100000003 title="民族">汉族</Columns100000003>
<Columns100000004 title="教育程度">博士后</Columns100000004>
<Columns100000005 title="入职年份">未知</Columns100000005>
<Columns100000006 title="关注行业">未知</Columns100000006>
</Root>'
 select distinct o.value('@title','char(16)') 'title'
from t
cross apply content.nodes('/Root/*') x(o)/*
title
----------------
备用联系方式    
公司地址        
公司领导        
公司名称        
关注行业        
教育程度        
客户姓名        
民族            
入职年份        
姓名            
主要联系方式    (11 row(s) affected)
*/