视图里怎么办XML的字符值

2013-08-23

视图里怎么处理XML的字符值？有一个表，里面有个字段是varchar(max)，保存的是XML内容。注意，字段是VARCAHR类

视图里怎么处理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') 
 
FROM a.additionalpid.nodes('/Others') T(c)
) b
CROSS APPLY
(
SELECT m.rowid, m.name, n.pid FROM
(
SELECT
rowid = ROW_NUMBER() OVER(ORDER BY GETDATE()),  
name = T.c.value('(./text())[1]', 'nvarchar(max)')
FROM b.NAME.nodes('/name') T(c)
) m
INNER JOIN
(
SELECT
rowid = ROW_NUMBER() OVER(ORDER BY GETDATE()),  
pid = T.c.value('(./text())[1]', 'nvarchar(max)')
FROM b.pid.nodes('/pid') T(c)
) n
ON m.rowid = n.rowid
) c
--ORDER BY id
/*
idnamepid
1户主A1001
1妻子A1541
1儿子A1025
1女儿A0025
2户主B2432
*/

热点排行

SQL Server

视图里怎么办XML的字符值