| Author |
 Topic  |
|
|
jimoomba
Yak Posting Veteran
India
86 Posts |
 Posted - 12/29/2011 : 13:53:24
|
Hi All,
Iam having a master and detailed tables as follows :
create table kids
(name varchar(25),
ID INT PRIMARY key IDENTITY
)
create table wishlist
(kid_name VARCHAR(25) references kids
,artno INT
,description varchar(50)
,price decimal
)
iam extracting the xml elements using sql query and saving into temp table and updating the master and detailed tables as shown below, but iam getting the wrong output when i ran the query for the second time.
kindly help me on the same:
declare @x xml
set @x = '<SinterklaasWishlists>
<child>
<name>Tim</name>
<wishlist>
<article>
<artno>21491269</artno>
<description>Crane</description>
<price>12.50</price>
</article>
<article>
<artno>21499517</artno>
<description>Keyboard</description>
<price>10</price>
</article>
<article>
<artno>21521591</artno>
<description>Crime Investigation Game</description>
<price>9.95</price>
</article>
</wishlist>
</child>
<child>
<name>Tim2</name>
<wishlist>
<article>
<artno>21491269</artno>
<description>Crane</description>
<price>12.50</price>
</article>
<article>
<artno>21499517</artno>
<description>Keyboard</description>
<price>10</price>
</article>
<article>
<artno>21521591</artno>
<description>Crime Investigation Game</description>
<price>9.95</price>
</article>
</wishlist>
</child>
</SinterklaasWishlists>'
insert into #list(kid_name,artno,descriptio,price)
SELECT
c1.value('name[1]','VARCHAR(255)') AS ChildName,
c2.value('artno[1]','BIGINT') AS Article_Number,
c2.value('description[1]','varchar(255)') AS [Description],
c2.value('price[1]','varchar(255)') AS Price
FROM
@x.nodes('//child') T1(c1)
CROSS APPLY c1.nodes('wishlist/article') T2(c2);
--select * from #list
insert into kids(name)
select distinct kid_name from #list
select * from kids
--CREATE TABLE #list (
--kid_name VARCHAR(255),
--artno BIGINT,
--descriptio varchar(255),
-- price varchar(255))
--drop table #list
--select * from kids
--select * from #list
update #list set kid_name= ( select k.ID from kids k where k.name= kid_name)
insert into wishlist(kid_name,artno,description,price)
select * from #list
select * from kids
select * from wishlist
rams |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 12/29/2011 : 14:51:13
|
No, he means a master-detail table design (like Orders-OrderLines)
--
Gail Shaw
SQL Server MVP |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 12/29/2011 : 15:28:49
|
Yes, I'll bet.
quote:
Iam having a master and detailed tables as follows
There is absolutely nothing in his post about a specific database.
--
Gail Shaw
SQL Server MVP |
Edited by - GilaMonster on 12/29/2011 15:31:23 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 12/29/2011 : 15:37:18
|
I read it like Gail too. What odds are you offering?  |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 12/29/2011 : 15:43:51
|
quote:
Originally posted by X002548
...and that is how I will win
Use [Master]
GO
Which does not appear anywhere in the OP's code whatsoever.
I don't post the USE statements when I post code. Going to assume I have everything in master?
--
Gail Shaw
SQL Server MVP |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 12/29/2011 : 15:44:44
|
Rams, you say you're getting incorrect output. What's wrong and what should it be?
--
Gail Shaw
SQL Server MVP |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
| |
Topic |
|
inserting data in master and detailed tables
)