CREATE TABLE [dbo].[tPickPointH](
        [idx] [smallint] IDENTITY(1,1) NOT NULL,
        [tcoutcd] [varchar](20) NOT NULL,
 CONSTRAINT [PK_tPickPointH] PRIMARY KEY CLUSTERED 
(
        [idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [tPickPointH01] UNIQUE NONCLUSTERED 
(
        [tcoutcd] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tPickPointD](
        [idx] [smallint] IDENTITY(1,1) NOT NULL,
        [tpphidx] [smallint] NOT NULL,
        [desn] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tPickPointD] PRIMARY KEY CLUSTERED 
(
        [idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [tPickPointD01] UNIQUE NONCLUSTERED 
(
        [tpphidx] ASC,
        [desn] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tPickPointD]  WITH CHECK ADD  CONSTRAINT [FK_tPickPointD_tpphidx] FOREIGN KEY([tpphidx])
REFERENCES [dbo].[tPickPointH] ([idx])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tPickPointD] CHECK CONSTRAINT [FK_tPickPointD_tpphidx]

declare @idx int
declare @cout varchar(20)
set @cout='iph'
declare @data xml
set @data='<data>
<pickpoints><pickpoint>gopeng</pickpoint></pickpoints>
<pickpoints><pickpoint>slim river</pickpoint></pickpoints>
</data>'
insert into dbo.tPickPointH(tcoutcd) values(@cout);
set @idx=scope_identity();
insert into dbo.tPickPointD
(tpphidx,desn)
select @idx,a.b.value('pickpoint[1]','varchar(50)')
from @data.nodes('/data/pickpoints') a(b);

declare @idx int
set @idx=1
declare @cout varchar(20)
set @cout='iph'
declare @data xml
set @data='<data>
<pickpoints><pickpoint>gopeng</pickpoint></pickpoints>
<pickpoints><pickpoint>sungai rokam</pickpoint></pickpoints>
<pickpoints><pickpoint>sungkai</pickpoint></pickpoints>
</data>'
update tPickPointH set tcoutcd=@cout
where idx=@idx;
...
...

quote:

---

Originally posted by visakh16

Didnt you get what i suggested yesterday? You need to have some kind of unique valued node inside xml to update each rows correctly. just relating by tpphidx alone will match multiple records

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---