日期:2014-05-17 浏览次数:20537 次
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[testXML]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[testXML]
END
GO
CREATE TABLE [testXML] (
ID INT,
X XML
)
GO
INSERT INTO testXML
SELECT 1,'<a id="1" />' UNION
SELECT 2,'<a id="4" /><a id="2" />' UNION
SELECT 3,'<a id="1" /><a id="2" /><a id="3" />'
SELECT * FROM testXML tx
--ID X
------------- --------------------------------------
--1 <a id="1" />
--2 <a id="4" /><a id="2" />
--3 <a id="1" /><a id="2" /><a id="3" />
--如何将3中的X字段更新为 3-2-1, 比如上面的正确结果为 <a id="3" />
--当然, 这只是实验数据, 实际上可能有很多行, 即需要用一行数据排除其它所有行数据
--还有, 如何快速得到 数据个数, 比如上面分别为 1,2,3
;WITH cte AS
(
SELECT
A.ID, A.X, B.Y
FROM testXML A
CROSS APPLY
(
SELECT
Y=T.s.value('./@id','int')
FROM A.X.nodes('/a') T(s)
) B
)
SELECT
ID,
cnt = COUNT(1),
X = (SELECT CAST('<a id='''+RTRIM(Y)+'''/>' AS xml) FROM cte B WHERE B.ID = A.ID AND NOT EXISTS(SELECT 1 FROM cte C WHERE C.ID < B.ID AND C.Y = B.Y) FOR XML PATH(''))
FROM cte A
GROUP BY ID
/*
ID cnt X
1 1 <a id="1"/>
2 2 <a id="4"/><a id="2"/>
3 3 <a id="3"/>
*/