STOREED PROCEDURE NEEDED - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

STOREED PROCEDURE NEEDED

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-08 : 08:23:11

HI,

IM HAVING ONE TABLE LIKE THIS
EMPLOYEE TABLE

NAME VARCHAR,
ID INT,
DEPARTMENT CHAR,
SECTION CHAR,
BLOCK CHAR,
DESIGNATION CHAR

ACTIVITY TABLE

NAME CHAR,
HOBBIES CHAR,
AWARDS INT,
AGE INT
I want to insert details into this tables for that i have write one sp for

name
id
department are taken as input parameters

and else every thing will in xml format ,How we can sp for this

How we insert into employee table from xml format, using temporary table

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-08 : 08:36:25

What's the structure of the XML? Can you give us a sample?

Why do you say 'using temporary table'. Does it have to be done using a temporary table?

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-08 : 08:39:51

<details>

<hobbies>bhhjh</hobbies>
<awards>3</awards>
<age>5<age>
</details>

regards,
Divya

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-08 : 08:40:42

ya it has to use a tempary table like these tables i have update 1 more table...

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-08 : 10:01:15

Let's try this for starters...

declare @EMPLOYEE TABLE (
    NAME VARCHAR(50),
    ID INT,
    DEPARTMENT CHAR(50),
    SECTION CHAR(50),
    BLOCK CHAR(50),
    DESIGNATION CHAR(50))

declare @ACTIVITY TABLE (
    NAME CHAR(50),
    HOBBIES CHAR(50),
    AWARDS INT,
    AGE INT)

declare @NAME VARCHAR, @ID INT, @DEPARTMENT CHAR, @XML xml
set @NAME = 'A'; set @ID = 1; set @DEPARTMENT = 'B'
set @XML = '<details>
<hobbies>bhhjh</hobbies>
<awards>3</awards> 
<age>5</age>
</details>'

insert @EMPLOYEE (NAME, ID, DEPARTMENT) select @NAME, @ID, @DEPARTMENT

insert @ACTIVITY (NAME, HOBBIES, AWARDS, AGE)
select
    @NAME,
    c.value('./hobbies[1]', 'CHAR(50)') hobbies,
    c.value('./awards[1]', 'INT') awards,
    c.value('./age[1]', 'INT') age
from @XML.nodes('details') T(c)

select * from @EMPLOYEE
select * from @ACTIVITY

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-08 : 23:12:09

:) thank you Ryan...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources