I want to insert data into a table from a stored procedure that takes xml string as a paramter
Posted on 2006-05-18
I know how to insert data into a table using xml string from a stored procedure. However, my problem is when the xml string contain multiple sub nodes with the root node. In addition, all the sub nodes data will be inserted to a single table.
we used the sub nodes for organization purpose.
this is sample. My xml contains hundreds of data in different subnodes
In the stord procedure I tried this one:
alter procedure spAddChale
@xmlData text = NULL
declare @docHandle int, @OID int
exec sp_xml_preparedocument @docHandle output, @xmlData
insert into chale
from openxml(@docHandle, 'Person/FirstName',2) with
from openxml(@docHandle, 'Person/LastName',2) with
EXEC sp_xml_removedocument @docHandle
In this case I am having sql error saying "The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns."
What shall I do?
Any help is appreciated.