日期:2014-05-17  浏览次数:20531 次

视图里怎么处理XML的字符值?
有一个表,里面有个字段是varchar(max),保存的是XML内容。注意,字段是VARCAHR类型,不是XML类型。
举例如下

id  name   pid    additionalpid--这个字段内容是xml
1   户主A   1001   <Others><wife><name><妻子A></name><pid>1541</pid></wife><son>……
2   户主B   2432   


意思是,A户人家,家里有老婆,有孩子等等。B户人家,家里没老婆孩子.pid是每个人的代码

这个表是远程数据库的。我在本地数据库想弄个视图,读取这个表的内容。同时把XML内容给解析出来。比如解析成如下记录

id  name   pid    
1   户主A   1001   
1   妻子A   1541   
1   儿子A   1025  
1   女儿A   0025    
2   户主B   2432  


这样的视图该怎么写?

------解决方案--------------------
if OBJECT_ID('tempdb..#temp', 'u') is not null   drop table #temp;
go
create table #temp( [id] int, [name] varchar(100), [pid] varchar(100), additionalpid NVARCHAR(max));
insert #temp
select '1','户主A','1001','<Others><wife><name>妻子A</name><pid>1541</pid></wife><son><name>儿子A</name><pid>1025</pid></son><daughter><name>女儿A</name><pid>0025</pid></daughter></Others>' union all
select '2','户主B','2432', ''

--SQL:
--虽然可以实现楼主的例子,但解析出来的xml中的name和pid是否一一对应,需要注意一下,不敢100%保证
--其实楼主的这个XML结构设计的不好。应该这样来存储: <Other><Person><Reation>妻子</Reation><name>妻子A</name><pid>1541</pid></Person><Person><Reation>儿子</Reation><name>儿子A</name><pid>1001</pid></Person></Other>
SELECT id, name,pid FROM #temp
UNION ALL
SELECT a.id,c.NAME,c.pid
FROM(select id,name,PID,additionalpid=CONVERT(XML,additionalpid) from #temp) a
CROSS APPLY
(
SELECT  
name = T.c.query('*/name'),
pid = T.c.query('*/pid')