?
Solved

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

Posted on 2006-05-18
4
Medium Priority
?
428 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:chaleastale
  • 3
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 16709321
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16709367
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
 

Author Comment

by:chaleastale
ID: 16709429
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16711422
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question