sql 通过游标 拆分xml结构

-----------------------定义游标变量------------------------------------------
  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--------------------

转载于:https://www.cnblogs.com/Impulse/p/5149754.html