样例XML:
<Parents>
<Child id="1121" name="Yogesh21" mode="121" />
<Child id="2546" name="Yogesh23" mode="123" />
<Child id="3232" name="Yogesh23" mode="267" />
<Child id="4324" name="Yogesh32" mode="212" />
<Child id="5232" name="Yogesh12" mode="232" />
</Parents>
请教：
1、如何在SQL SERVER 2008存储过程中获取id="3232"的上一个节点的属性mode？
2、如何在SQL SERVER 2008存储过程中获取id="3232"的下一个节点的属性name？
3、可否获取到id="3232"这是第几个节点，如果使用XPATH、XQUERY等。
------解决思路----------------------
楼主参考：SqlServer XML数据类型DML相关操作（图文结合）
------解决思路----------------------
DECLARE @x xml
SET @x = '
<Parents>
<Child id="1121" name="Yogesh21" mode="121" />
<Child id="2546" name="Yogesh23" mode="123" />
<Child id="3232" name="Yogesh23" mode="267" />
<Child id="4324" name="Yogesh32" mode="212" />
<Child id="5232" name="Yogesh12" mode="232" />
</Parents>'
;WITH child AS (
SELECT T.c.value('@id','int') as id,
T.c.value('@name','varchar(10)') as [name],
T.c.value('@mode','int') as mode,
ROW_NUMBER() OVER(ORDER BY GETDATE()) rn
FROM @x.nodes('/Parents/Child') T(c)
)
SELECT c0.mode AS [问题1],
c2.name AS [问题2],
c1.rn AS [问题3]
FROM child c1
LEFT JOIN child c0
ON c0.rn = c1.rn - 1
LEFT JOIN child c2
ON c2.rn = c1.rn + 1
WHERE c1.id=3232
问题1 问题2 问题3
----------- ---------- -----------
123 Yogesh32 3