sql 通过游标拆分xml结构

最新推荐文章于 2023-06-12 16:58:50 发布

转载最新推荐文章于 2023-06-12 16:58:50 发布 · 73 阅读

本文介绍了一种使用SQL游标结合XML数据处理的方法。通过声明变量并定义游标，可以遍历包含XML数据的记录集，并利用sp_xml_preparedocument存储过程解析XML文档，再通过OPENXML获取具体项进行数据插入操作。

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