SQL解读XML案例

ALTER PROCEDURE [dbo].[GetProductList1] 
    @Products XML
AS 
BEGIN
    SET NOCOUNT ON
    DECLARE @Pointer INT 
        
    DECLARE @TempCard TABLE
    (
     ProductID INT NOT NULL
    )
    
    BEGIN TRY
        EXECUTE sp_xml_preparedocument @Pointer OUTPUT, @Products
        
        INSERT @TempCard(ProductID)
        SELECT ProductID
        FROM OpenXML (@Pointer,'/Products/Product', 1) WITH (
        ProductID INT)
        
        EXEC sp_xml_removedocument @Pointer 
        SET @Pointer = 0
        
        SELECT tc.ProductID, p.SalePrice
        FROM @TempCard tc
        INNER JOIN dbo.Product p
        ON tc.ProductID = p.PID
            
    END TRY
    BEGIN CATCH
        IF (@Pointer <> 0)
            BEGIN
                EXEC sp_xml_removedocument @Pointer 
                SET @Pointer = 0
            END
        RETURN -1
    END CATCH
END