-----------------------定义游标变量------------------------------------------
DECLARE @propertyid INT
DECLARE @propertyname VARCHAR(50)
DECLARE @xml NVARCHAR(1000)
DECLARE top3_Cursor CURSOR SCROLL FOR
--------------------------定义游标结束-----------------------------
SELECT s.id, s.name, CAST(PropertyValueSchema AS VARCHAR(1000)) PropertyValueSchema
FROM StyleProperty s INNER JOIN ( SELECT MAX(a.id) id , a.name
FROM dbo.StyleProperty a INNER JOIN dbo.StylePartsGroup b ON a.GroupId = b.Id
WHERE PropertyValueSchema.exist('//valueSchema//select//@mode')=1
GROUP BY a.name) b ON s.id = b.id
OPEN top3_Cursor
FETCH NEXT FROM top3_Cursor INTO @propertyid,@propertyname,@xml
WHILE(@@FETCH_STATUS=0)
BEGIN
----------------------------- body-------------------------------------
DECLARE @idoc INT
PRINT @xml
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
-- Execute a SELECT statement using OPENXML rowset provider.
INSERT INTO t1
SELECT @propertyid propertyid,@propertyname propertyname, *
FROM OPENXML (@idoc, '/valueSchema/select/items/item' ,1)
WITH (
codekey INT '@key',
codename varchar(100) '@name'
)
EXEC sp_xml_removedocument @idoc
------------------------------------body end---------------------------
FETCH NEXT FROM top3_Cursor INTO @propertyid,@propertyname,@xml
END
-----------------------footer-----------------------
close top3_Cursor
DEALLOCATE top3_Cursor
---------------footer end----------------------
--------------------------------------------end cursor--------------------