OPENXML - Insert value into other Store Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

OPENXML - Insert value into other Store Procedure

Author

Topic

cutever
Starting Member

32 Posts

Posted - 2002-09-25 : 23:16:35

I would like use OPENXML to insert XML files (EmpNo, JoinDate, EmailID, Name, ICNo) into SQL SERVER at the same time. Xml files contain the data that will then insert into two different tables: Person and Employee. The new identity (PersonID) wills then insert/store into the Employee Table: PersonID. Then, I’ve created the following code.

Could some one check whether the following code is correct or not? If incorrect, please give me some guidance.

CREATE Procedure sp_InsertRegisterEmployee2 as

Declare @iTree Integer
Declare @empdata varchar(2000)
Declare @MyIdent1 int
Declare @MyIdent2 int

set @empdata ='
<?xml version="1.0"?>
<EmployeeList>
<Employee>
<Name>7777</Name>
<ICNo>777777-77-7777</ICNo>
<EmpNo>E7777</EmpNo>
<JoinDate>12/12/01</JoinDate>
<EmailID>7777@hotmail.com</EmailID>
</Employee>
<Employee>
<Name>8888</Name>
<ICNo>888888-88-8888</ICNo>
<EmpNo>E8888</EmpNo>
<JoinDate>05/05/02</JoinDate>
<EmailID>8888@hotmail.com</EmailID>
</Employee>
</EmployeeList>'

Exec sp_xml_preparedocument @iTree Output, @empdata

Exec sp_InsertPerson
@Type = 'EM',
@Name = Name,
@ICNo = ICNo,
@PersonID = @MyIdent2 OUTPUT

Select Name, ICNo from Openxml(@iTree, 'EmployeeList/Employee',1)
with( Name varchar(50) '@Name', ICNo char(14) '@ICNo')

Exec sp_InsertEmployee
@EmpNo = EmpNo,
@PersonID = @MyIdent2,
@JoinDate = JoinDate,
@EmailID = EmailID,
@EmpID = @MyIdent1 OUTPUT

Select EmpNo, JoinDate, EmailID from Openxml(@iTree, 'EmployeeList/Employee',1)
with( EmpNo varchar(5) '@EmpNo', JoinDate datetime '@JoinDate', EmailID varchar(30) '@EmailID')

Select * from tbl_Employee a left outer join tbl_Person p on a.PersonID = p.PersonID
Where EmpID = @MyIdent1

Exec sp_xml_removedocument @iTree
GO

Another question is, can I pass the XML file (using OPENXML) into Store Procedure within other Store Procedure as following:

CREATE Procedure sp_InsertRegisterEmployee @empdata varchar(2000)
as
Declare @iTree Integer

Exec sp_xml_preparedocument @iTree Output, @empdata

Exec sp_InsertEmployeeDetail

Select * from Openxml(@iTree, 'EmployeeList/Employee/',1)
with( Name varchar(50), ICNo char(14), EmpNo varchar(5), JoinDate datetime, EmailID varchar(30))

Exec sp_xml_removedocument @iTree
GO

I really hope someone can help me with this! Thank You!

Ver

Edited by - cutever on 09/26/2002 21:21:33

cutever
Starting Member

32 Posts

Posted - 2002-09-26 : 21:23:05

I really need someone help ! Please ~~~~~~~~~~

Ver

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-27 : 00:05:26

Ver-

OK, you need to cool it:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19903
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19866

People have spent time trying to answer your questions, and all you do is continue posting the same thing without making any effort to follow up, or explain in more detail, or demonstrate that you've actually tried to solve the problem on your own.

We don't work for you and we don't get paid to help you, begging for our help doesn't help you or us. And you need to read Books Online and experiment with the XML features that are available. In all likelihood you'll figure it out, or you'll at least be able to tell us what you tried and why it didn't work.

cutever
Starting Member

32 Posts

Posted - 2002-09-27 : 01:10:33

Dear robvolk,

For the following questions, I’ve found the solution by using the ASP. Net.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19903
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19866

Thank for reminding me that the above questions is not SQL Question:

quote:

Hang on a minute:
quote:

I knew about sp_xml_preparedocument. However, I want to work without using SQL database.

“Ummmm, then why are you posting this question ON A SQL SERVER SITE?”

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20202 and the previous one is different question. In this question, I just want to know whether I can pass a table into Store Procedure like Insert…Select. After research, I found that we couldn’t pass Table into SP. Other solution is use looping to re-pass the record one by one.

I am beginner of SQLXML, I’ve tried so many times to find out the solution by read the Books Online and search the Article in Internet. I’ve also finished study “SQL SERVER 2000 with XML – Second Edition”, “Mastering SQL Server 2000”, and “SQL Server 2000 Programming”. However, I really can’t find the solution, that’s why I posted my question into forum to get some guidance from expert. I’ve posted my whole Store Procedure to forum side. I am not asking the solution exactly in code; I just want to know whether this can be done or not, or can show/direct me to any related article that able to provide me some guidance. May be some of developer tried it before, they can tell me whether it’s can be run or not. Then I not need spend more time to do research again. My project is urgent. Therefore, I post the question and doing the research at the same time.

If I‘ve done any stupid thing on the forum before, please forgive me! ^_^

Ver

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources