I want to insert data into a table from a stored procedure that takes xml string as a paramter

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.

sample xml:

<Person>
       <FirstName>
              <fName>Girma</fName>
       </FirstName>
       <LastName>
             <lName>Alemgasha</lName>
       </LastName>
</Person>

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
)

as

declare @docHandle int, @OID int
exec sp_xml_preparedocument @docHandle output, @xmlData
insert into chale
(
      FirstName,
      LastName
)

select
      fName
      
from openxml(@docHandle, 'Person/FirstName',2) with
(
      fName char(10)
)

select
      lName
from openxml(@docHandle, 'Person/LastName',2) with
(
      
      lName char(10)
)


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.

chale
chaleastaleAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
replace the entire insert staements with

insert into chale
(
     FirstName,
     LastName
)

select   fName ,   lName  
from openxml(@docHandle, '/XML/Person') with
(
     fName char(10)  'FirstName',
     lname char(10)  'LastName'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
alter procedure spAddChale(
     @xmlData text = NULL
)
as
declare @docHandle int, @OID int
exec sp_xml_preparedocument @docHandle output, @xmlData
insert into chale
(
     FirstName,
     LastName
)

select   fName ,   lName  
from openxml(@docHandle, '/Person') with
(
     fName char(10)  'FirstName',
     lname char(10)  'LastName'

EXEC sp_xml_removedocument @docHandle
go
0
 
chaleastaleAuthor Commented:
That was really helpful. However, if I have hundred elements inside, for example, 'FirstName'  sub node, I have to write 'FirstName' hundred times inside my stored procedure to tell that specific field belongs to this sub node.

Is there any way out to reduce writing this sub node repeatedly?

Thank u so much.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
declare  @xmlData varchar(2000)
SET @xmlData = '<Person>
       <FirstName>
              <fName>Girma</fName>
              <fAge>23</fAge>
              <fAdd>Add</fAdd>
       </FirstName>
       <LastName>
             <lName>Alemgasha</lName>
       </LastName>
</Person>
'

declare @docHandle int, @OID int
exec sp_xml_preparedocument @docHandle output, @xmlData

select   *  
from openxml(@docHandle, '/Person') with
(
     fName char(10)  'FirstName',
     fAge  int       'FirstName/fAge',   ---- You wanna reduce tis 'FirstName'
     fadd  char(10)  'FirstName/fAdd',
     lname char(10)  'LastName'
)

--anpther method
select   *  
from openxml(@docHandle, '/Person/FirstName') with
(
     fName char(10)  '.',
     fAge  int       'fAge',   ---- You wanna reduce tis 'FirstName'
     fadd  char(10)  'fAdd',
     lname char(10)  '../LastName'
)




EXEC sp_xml_removedocument @docHandle
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.