xml procedure

DECLARE @XMLdoc XML
SET @XMLdoc =
'<Book name="SQL Server 2000 Fast Answers">
<Chapters>
<Chapter id="1" name="Installation, Upgrades">
 <CreateDate>2009-12-30</CreateDate>
</Chapter>
<Chapter id="2" name="Configuring SQL Server"/>
<Chapter id="3" name="Creating and Configuring Databases">
 <CreateDate>2009-12-30</CreateDate>
</Chapter>
<Chapter id="4" name="SQL Server Agent and SQL Logs"/>
</Chapters>
</Book>'

DECLARE @docpointer int
EXEC sp_XML_preparedocument @docpointer OUTPUT, @XMLdoc

SELECT *
FROM OPENXML (@docpointer, '/Book/Chapters/Chapter',1)
WITH (Chapter int '@id',
ChapterNM varchar(50) '@name',
CreateDate VARCHAR(50) './CreateDate/text()')
GO

结果如下：

Chapter    ChapterNM                                              CreateDate
1               Installation, Upgrades                            2009-12-30
2               Configuring SQL Server                           NULL
3               Creating and Configuring Databases      2009-12-30
4               SQL Server Agent and SQL Logs             NULL

 

 

-- =============================================
-- Author:      ZHANGWEI
-- Create date: 2009.12.24
-- Description: 收藏物品
-- =============================================
ALTER PROC [dbo].[AS_SP_AddCollectProducts]
(
 @uUserCode UNIQUEIDENTIFIER,  --用户码
 @strProductID VARCHAR(32),   --商品ID
 @strErrInfo VARCHAR(250) = '' OUTPUT 
)
AS
 BEGIN
 DECLARE @iRetCode INT
 SET @iRetCode = -1
 SET @strErrInfo = '';

 DECLARE @xmlRootPre VARCHAR(50),@xmlRootEnd VARCHAR(50)
 DECLARE @iUserID BIGINT
 SELECT TOP 1 @iUserID = UserID FROM dbo.AS_DT_Users
    WHERE UserCode = @uUserCode
 SELECT TOP 1 PCID FROM dbo.AS_DT_ProductCollect
    WHERE UserID = @iUserID
 IF(@@ROWCOUNT < 1)
  BEGIN
   DECLARE @xmlProduct NVARCHAR(2000)
   SET @xmlProduct = N'<Products><Product id="'+ @strProductID +'">
         <CreateDate>'+ CONVERT(VARCHAR(36),GETDATE())+'</CreateDate>
         </Product></Products>'
   INSERT INTO AS_DT_ProductCollect(UserID,Products,CreateDate)
       VALUES(@iUserID,@xmlProduct,GETDATE())
    IF(@@ROWCOUNT > 0)
     BEGIN
      SET @iRetCode = 1
      SET @strErrInfo = '物品收藏成功!';
     END
    ELSE
     BEGIN
      SET @iRetCode = -3
      SET @strErrInfo = '物品收藏失败!';
     END
  END
 ELSE
  BEGIN
 --  UPDATE AS_DT_ProductCollect SET Products.modify('
 --   insert element Product {sql:variable("@strProductID")}
 --    as first into (/Products)[1]')
 --    WHERE UserID = @iUserID
   DECLARE @SQL NVARCHAR(256)
   DECLARE @iPID INT
   SET @SQL = 'SELECT TOP 1 @iPCID=PCID
     FROM dbo.AS_DT_ProductCollect
       WHERE UserID = ' + CONVERT(VARCHAR(8),@iUserID) + '
          AND Products.exist
           (''/Products/Product[@id="'+@strProductID+'"]'') = 1'
   EXEC SP_EXECUTESQL @SQL,N'@iPCID INT OUTPUT',@iPID OUTPUT
   IF(@iPID IS NULL)
    BEGIN
     SET @SQL = 'UPDATE AS_DT_ProductCollect
         SET Products.modify(''insert <Product id="'+@strProductID+'">
          <CreateDate>'+ CONVERT(VARCHAR(36),GETDATE())+'</CreateDate>
           </Product> into (/Products)[1]'')
             WHERE UserID = ' + CONVERT(VARCHAR(8),@iUserID)
     EXEC(@SQL)
     IF(@@ROWCOUNT > 0)
      BEGIN
       SET @iRetCode = 1
       SET @strErrInfo = '物品收藏成功!';
      END
     ELSE
      BEGIN
       SET @iRetCode = -2
       SET @strErrInfo = '物品收藏失败!';
      END
    END
   ELSE
    BEGIN
     SET @iRetCode = -1
     SET @strErrInfo = '该商品已经存在!'
    END
   
  END
 RETURN @iRetCode
 END

 

 

-- =============================================
-- Author:      ZHANGWEI
-- Create date: 2009.12.18
-- Description: 查询商品收藏列表
-- =============================================
ALTER PROC [dbo].[AS_SP_SearchPCollectByUserID]
(
 @gUserCode UNIQUEIDENTIFIER,  --用户码(积分副表)
 @iPageIndex INT,     --当前页
 @iPageSize INT,     --页记录数
 @iRecords INT OUTPUT,    --总记录数
 @strErrInfo VARCHAR(512) OUTPUT  --返回信息
)
AS
 BEGIN
  DECLARE @iRetCode INT
  SET @iRetCode = 0
  SET @iRecords = 0
  SET @strErrInfo = '';
  
  DECLARE @xmlProduct XML
  SELECT TOP 1 @xmlProduct = Products FROM dbo.AS_DT_ProductCollect PC
   INNER JOIN dbo.AS_DT_Users U ON PC.UserID = U.UserID
    WHERE U.UserCode = @gUserCode
  IF(@xmlProduct IS NOT NULL AND CONVERT(VARCHAR(MAX),@xmlProduct) != '')
   BEGIN
    DECLARE @docPointer INT
    EXEC SP_XML_PREPAREDOCUMENT @docPointer OUTPUT,@xmlProduct;
    SELECT * INTO #TProducts FROM OPENXML(@docpointer,'/Products/Product',0)
      WITH(ProductID INT '@id',
        CreateDate VARCHAR(50) './CreateDate/text()');
    EXEC SP_XML_REMOVEDOCUMENT @docPointer
    IF(@@ROWCOUNT > 0)
     BEGIN
      DECLARE @p INT,@sql NVARCHAR(1024),@currRecord INT,@rowscount INT;
      SET @sql =
       '
       SELECT SPO.SPOID AS PID,SPO.Score,P.ProductID,P.ProductName,P.MTypeID,P.Picture,PC.CreateDate FROM  dbo.AS_DT_SolidProductsOnSale SPO
       INNER JOIN dbo.AS_RT_Products P ON SPO.ProductID = P.ProductID
       INNER JOIN #TProducts PC ON SPO.ProductID = PC.ProductID
        UNION ALL
       SELECT VPO.VPOID AS PID,VPO.Score,P.ProductID,P.ProductName,P.MTypeID,P.Picture ,PC.CreateDate FROM  dbo.AS_DT_VirtualProductsOnSale VPO
       INNER JOIN dbo.AS_RT_Products P ON VPO.ProductID = P.ProductID
       INNER JOIN #TProducts PC ON VPO.ProductID = PC.ProductID
        ORDER BY PC.CreateDate DESC'
        
      EXEC SP_CURSOROPEN @p OUTPUT,@sql,@scrollopt=1,@ccopt=1,@rowcount=@rowscount OUTPUT 
      SELECT @iRecords=ceiling(1.0*@rowscount),@currRecord=(@iPageIndex-1)*@iPageSize+1
      EXEC SP_CURSORFETCH  @p,16,@currRecord,@iPageSize --table1
      IF(@@ROWCOUNT > 0)
       SET @iRetCode = 1 
      EXEC SP_CURSORCLOSE @p
     END
    ELSE
     BEGIN
      SET @iRetCode = -2;
    SET @strErrInfo = '没有收藏的物品!';
     END
   END
  ELSE
   BEGIN
    SET @iRetCode = -1;
    SET @strErrInfo = '暂时没有收藏的物品!';
   END
  RETURN @iRetCode;
 END

 

 

-- =============================================
-- Author:      ZHANGWEI
-- Create date: 2009.12.26
-- Description: 删除物品收藏
-- =============================================
ALTER PROC  [dbo].[AS_SP_DelProductCollectByID]
(
 @iPCID  INT,     --行标识
 @iProductID INT ,     --物品ID     
 @strErrInfo VARCHAR(512) OUTPUT  --返回信息
)
AS
BEGIN
  DECLARE @iRetCode INT;
  SET @iRetCode = -1;
  SET @strErrInfo = '';

  DECLARE @strSQL NVARCHAR(512)
  SET @strSQL = 'UPDATE dbo.AS_DT_ProductCollect
       SET Products.modify(''
        delete(/Products/Product[@id='+CONVERT(VARCHAR(4),@iProductID)+'])'')
         WHERE PCID =' + CONVERT(VARCHAR(4),@iPCID);
  EXEC SP_EXECUTESQL @strSQL;
  IF(@@ROWCOUNT > 0)
    BEGIN
     SET @iRetCode = 1;
     SET @strErrInfo = '删除成功!';
    END   
  ELSE
    BEGIN
     SET @iRetCode = -1;
     SET @strErrInfo = '删除失败!';
    END

  RETURN @iRetCode; 
 END