Can someone explain how OpenXML works(in this SP)?

Posted by chobo2 on Stack Overflow See other posts from Stack Overflow or by chobo2
Published on 2010-05-19T23:45:03Z Indexed on 2010/05/19 23:50 UTC
Read the original article Hit count: 357

Filed under:
|

Hi

I am looking at this tutorial

and it confuses me as I don't get the SP

CREATE PROCEDURE [dbo].[spTEST_InsertXMLTEST_TEST](@UpdatedProdData nText)
AS 
 DECLARE @hDoc int   

 exec sp_xml_preparedocument @hDoc OUTPUT,@UpdatedProdData 

 INSERT INTO TBL_TEST_TEST(NAME)
 SELECT XMLProdTable.NAME
    FROM OPENXML(@hDoc, 'ArrayOfTBL_TEST_TEST/TBL_TEST_TEST', 2)   
       WITH (
                ID Int,                 
                NAME varchar(100)
            ) XMLProdTable

EXEC sp_xml_removedocument @hDoc

First I am using SQL 2005 and do I need to install something on the server to get OPENXML to work? Next I don't get what these statements do

// not sure what @hDoc is for and why it is an int
 DECLARE @hDoc int   

 // don't get what this is and where the output is.
 exec sp_xml_preparedocument @hDoc OUTPUT,@UpdatedProdData 

// don't get why it is "XMLProdTable" and if it always has to be like this
 SELECT XMLProdTable.NAME

// pretty muct don't get anything what is happening after OPENXML
 FROM OPENXML(@hDoc, 'ArrayOfTBL_TEST_TEST/TBL_TEST_TEST', 2)   
           WITH (
                    ID Int,                 
                    NAME varchar(100)
                ) XMLProdTable

// Don't know what this is really executing
EXEC sp_xml_removedocument @hDoc

Thanks

© Stack Overflow or respective owner

Related posts about stored-procedures

Related posts about openxml