Open XML Insert in Temp table - SQL 2005

Question

I have a stored procedure where i am passing a simple XML :

'<Products><ProductId>1</ProductId><ProductId>2</ProductId></Products>'

I have a @temp table in SQL which has a ProductId Column:

DECLARE @Temp TABLE(        
ProductId NVARCHAR(10)

)

I need to write an insert statement which will loop through the ProductId's in the XML (which can be infinite) and keep on inserting (in the @temp table) until the XML has no more ProductId nodes left.

A solution involving cursors is not feasible!

Following is the code i am trying to execute :

Declare @test XML
SET @test = '<Products><ProductId>1</ProductId><ProductId>2</ProductId></Products>'
DECLARE @Temp TABLE(        
    ProductId NVARCHAR(10)
   )  
INSERT INTO @Temp(ProductId)
SELECT tab.col.value('./ProductId[1]','NVARCHAR(10)') AS 'ProductId'
FROM @test
CROSS APPLY
xml_data.nodes('//Products') AS tab(col)

I keep getting the erorr : Must declare the table variable "@test".

Answer 1

You could put the xml into another temp table, and then do an insert statement from there:

http://www.lockergnome.com/sqlsquirrel/2008/05/23/how-to-turn-imported-xml-into-a-relational-format-in-sql-server-2005/

Answer 2

DECLARE @test XML
    SET @test = '<Products><ProductId>1</ProductId><ProductId>2</ProductId></Products>'

DECLARE @Temp TABLE(ProductId NVARCHAR(10))  

DECLARE @docHandle int 
EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc 

INSERT INTO @Temp(ProductId)
     SELECT t.value('./ProductId[1]','NVARCHAR(10)') AS 'ProductId'
       FROM OPENXML(@docHandle, '//Products', 1) t

EXEC sp_xml_removedocument @docHandle