December 29, 2011 at 11:59 am
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
December 29, 2011 at 1:44 pm
When you say you get the wrong output when you run the query a second time, what exactly do you mean?
Also, whey have the sub-table reference the kid name, instead of the ID from the kids table? Seems unusual to me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply