DECLARE @XML XML
DECLARE @TROLES TABLE (RoleID INT,UserID INT)SET @XML = '<roles><role id="3"><user id="31" /><user id="32" /></role><role id="4"><user id="41" /></role></roles>'Insert @TROLES......请教,在SQL如何把上面的@XML相应的数据插到表@TRoles ????
DECLARE @TROLES TABLE (RoleID INT,UserID INT)SET @XML = '<roles><role id="3"><user id="31" /><user id="32" /></role><role id="4"><user id="41" /></role></roles>'Insert @TROLES......请教,在SQL如何把上面的@XML相应的数据插到表@TRoles ????
请指出问题,谢谢.......................
小弟捣鼓了一下...似乎可以满足需求了
-----------------------------------------------------------------------------------
DECLARE @XML XML
DECLARE @TROLES TABLE ( RoleID INT, UserID INT )
SET @XML = '<roles><role roleid="3"><user id="31" /><user id="32" /></role><role roleid="4"><user id="41" /></role></roles>'
INSERT @TROLES
( RoleID ,
UserID
)
SELECT Roles.Role.value('../@roleid', 'int') AS RoleID ,
Roles.Role.value('@id', 'int') AS UserID
FROM @XML.nodes('//role/user') Roles ( Role )
SELECT *
FROM @TROLES
-------------------------------------------------------------------------