Inserting data from an XML string in a table

[ONeil Tomlinson]

i wnat to inset the following data into a table. The data is an xml file as folows ------------- <root> <employee> <id>20</id> <fname>tom</fname> <lname>smith</lname> </employee> </root> --------------------- i have the follwoing stored procedure that takes the xml text as a parameter, insert the data and return the table data back to me - but im getting this error = "Incorrect syntax near the keyword 'FROM'" and "Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon." Help Please -------------------------------- set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go create PROC [dbo].[sp_add_employee1] @empdata text AS DECLARE @hDoc int exec sp_xml_preparedocument @hDoc OUTPUT,@empdata INSERT into Employee (id, fname, lname) SELECT FROM OPENXML (@hDoc, '/root/Employee') WITH Employee (id varchar(50),fname varchar(50), lname varchar(50)) EXEC sp_xml_removedocument @hDoc SELECT * from Employee FOR XML AUTO -------------------------------------------

[Wendelius]

ONeil Tomlinson wrote:

SELECT FROM OPENXML (@hDoc, '/root/Employee')

SELECT * FROM OPENXML (@hDoc, '/root/Employee')