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 error:

Must declare the table variable "@test".

Try running just the SELECT portion of the INSERT, swapping out the @test with the xml string. Make sure that works first — OMG Ponies
– OMG Ponies, Commented Aug 27, 2009 at 19:07
Yeah, need to use OPENXML - see my updated answer. Ref: OPENXML: msdn.microsoft.com/en-us/library/aa276847%28SQL.80%29.aspx — OMG Ponies
– OMG Ponies, Commented Aug 27, 2009 at 19:15

OMG Ponies · Accepted Answer · 2009-08-27 19:35:40Z

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

edited Aug 27, 2009 at 19:35

answered Aug 27, 2009 at 18:48

OMG Ponies

334k85 gold badges536 silver badges509 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Murtaza Mandvi Over a year ago

Just added a standalone code to explain my example still throws an error !

RameezAli · Accepted Answer · 2014-02-11 10:11:38Z

DECLARE @idoc int

DECLARE @doc varchar(1000)

SET @doc ='
<OutLookContact>
<Contact FirstName="Asif" LastName="Ghafoor" EmailAddress1="[email protected]" />
<Contact FirstName="Rameez" LastName="Ali" EmailAddress1="[email protected]" />
<Contact FirstName="Aneel" LastName="Maqsood" EmailAddress1="[email protected]" />
</OutLookContact>'

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement that uses the OPENXML rowset provider.

DECLARE @Temp TABLE(FirstName VARCHAR(250),LastName VARCHAR(250),Email1 VARCHAR(250))  

INSERT INTO @Temp(FirstName,LastName,Email1)



SELECT *

FROM OPENXML (@idoc, '/OutLookContact/Contact',1)

WITH (FirstName varchar(50),LastName varchar(50),EmailAddress1 varchar(50))


select FirstName,LastName,Email1 from @Temp

kemiller2002 · Accepted Answer · 2009-08-27 18:29:48Z

0

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/

answered Aug 27, 2009 at 18:29

kemiller2002

116k28 gold badges202 silver badges255 bronze badges

Collectives™ on Stack Overflow

Open XML Insert in Temp table - SQL 2005

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related