XML Insert

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Sareesh
-- Create date: 28 March 2012
-- Description:    here @TestXML root node is /STHome
--    and declare ID,TestValue same name as in the xml format

--var TestXML = "<STHome>";
--TestXML = TestXML + "<Home>";
--TestXML = TestXML + "<ID>" + $(this).val() + "</ID>";                                               
--TestXML = TestXML + "<TestValue>" + "" + "</TestValue>";                  
--TestXML = TestXML + "</Home>";
--TestXML = STHomeXML + "</STHome>";
-- =============================================
CREATE PROCEDURE SPR_TEST_Insert(
    @TestID INT,
    @TestXML XML
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @XmlDocumentHandle int
    DECLARE @ID INT
    DECLARE @TestValue VARCHAR(2000)
   
    -- reads the xml then parses the text using the MSXML parser,
    -- and provides the parsed document in a state ready for consumption
    EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @TestXML

    -- I create a cursor to iterate over the developer items in the XML file 
    -- OPENXML allows access to XML data as though it is a relational rowset
    DECLARE TestCursor CURSOR FOR
    SELECT ID, TestValue
    FROM OPENXML (@XmlDocumentHandle, '/STHome/Home',2)
    WITH (ID INT, TestValue VARCHAR(2000))
   
    OPEN DeficitCursor 
    FETCH NEXT FROM DeficitCursor INTO @DeficitID, @DeficitValue  
    WHILE @@FETCH_STATUS = 0
        BEGIN
              INSERT INTO TESTTABLE VALUES (@ID, @TestValue, @TestID)
              FETCH NEXT FROM TestCursor INTO @ID, @TestValue
        END

    CLOSE TestCursor
    DEALLOCATE TestCursor
   
    EXEC sp_xml_removedocument @XmlDocumentHandle
END
GO