March 10, 2010 at 7:41 am
I'm defined Date Only and Time Only data types as follow,
create type Date from dateTime
create type Time from dateTime
create rule DateOnlyRule as
dateAdd(dd,datediff(dd,0,@DateTime),0) = @DateTime
go
create rule TimeOnlyRule as
datediff(dd,0,@DateTime) = 0
go
EXEC sp_bindrule 'DateOnlyRule', 'Date'
EXEC sp_bindrule 'TimeOnlyRule', 'Time'
My tables design as follow,
CREATE TABLE [dbo].[tRouteTimerHx](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[troutehidx] [smallint] NOT NULL,
[seq] [tinyint] NOT NULL,
[tripnme] [varchar](50) NOT NULL,
[dprtweekday] [char](7) NOT NULL,
CONSTRAINT [PK_tRouteTimerHx] 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 [troutetimerhx01] UNIQUE NONCLUSTERED
(
[troutehidx] ASC,
[seq] 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].[tRouteTimerDx](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[troutetimerhidx] [smallint] NOT NULL,
[troutedidx] [smallint] NOT NULL,
[dprttime] [dbo].[Time] NOT NULL,
CONSTRAINT [PK_tRouteTimerDx] 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 [troutetimerdx01] UNIQUE NONCLUSTERED
(
[troutetimerhidx] ASC,
[troutedidx] 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
ALTER TABLE [dbo].[tRouteTimerDx] WITH CHECK ADD CONSTRAINT [FK_tRouteTimerDx_troutetimerhidx] FOREIGN KEY([troutetimerhidx])
REFERENCES [dbo].[tRouteTimerHx] ([idx])
GO
ALTER TABLE [dbo].[tRouteTimerDx] CHECK CONSTRAINT [FK_tRouteTimerDx_troutetimerhidx];
So far i've as follow,
declare @idx smallint
declare @data xml
set @data='<trips>
<trip>
<routeh>1</routeh>
<seq>1</seq>
<tripnme>trip1</tripnme>
<dprtweekday>1011101</dprtweekday>
<dprttimes>
<dprttime routed="6">9:00AM</dprttime>
<dprttime routed="7">2:30PM</dprttime>
</dprttimes>
</trip>
<trip>
<routeh>1</routeh>
<seq>2</seq>
<tripnme>trip2</tripnme>
<dprtweekday>1110001</dprtweekday>
<dprttimes>
<dprttime routed="6">11:00AM</dprttime>
<dprttime routed="7">4:30PM</dprttime>
</dprttimes>
</trip>
</trips>'
insert into dbo.tRouteTimerHx
select a.b.value('routeh[1]','smallint'),a.b.value('seq[1]','tinyint'),
a.b.value('tripnme[1]','varchar(50)'),a.b.value('dprtweekday[1]','char(7)')
from @data.nodes('/trips/trip') a(b);
set @idx=SCOPE_IDENTITY();
I'm looking for help to continue my code above. As result, XML data will inserted into tRouteTimerHx, and tRouteTimerDx.
The results as follow,
tRouteTimerHx
idx | troutehidx | seq | tripnme | dprtweekday
------------------------------------------------------
1 1 1 trip1 1011101
2 1 2 trip2 1110001
/*idx is a identity(1,1)*/
tRouteTimerDx
idx | troutetimerhidx | troutedidx | dprttime
------------------------------------------------------
1 1 6 1/1/1900 9:00:00 AM
2 1 7 1/1/1900 2:30:00 PM
3 2 6 1/1/1900 11:00:00 AM
4 2 7 1/1/1900 4:30:00 PM
/*idx is a identity(1,1)*/
Hopefully i will get some help
March 10, 2010 at 12:52 pm
My first advice is to get rid of the custom data type.
It won't add any value to your code or database structure. Instead it will cause pain to maintain it. I'd rather recommend to make sure to insert the data in a desired format and use CONVERT to extract the time portion if required. An even better solution would be to use SS2K8 since it will have those tow data types ready to use.
And here's how I'd use the sample data you provided to fill the second table:
SELECT
a.b.value('seq[1]','tinyint') AS troutetimerhidx ,
c.d.value('@routed[1]','varchar(10)') AS troutedidx ,
CAST('19000101 ' + c.d.value('.','varchar(10)') AS DATETIME) AS dprttime
FROM @data.nodes('/trips/trip') a(b)
CROSS APPLY
b.nodes('dprttimes/dprttime') c(d)
March 11, 2010 at 3:40 am
lmu92 (3/10/2010)
My first advice is to get rid of thecustom data type.
It won't add any value to your code or database structure. Instead it will
cause pain to maintain it. I'd rather recommend to make sure to insert the
data in a desired format and use CONVERT to extract the time portion if
required. An even better solution would be to use SS2K8 since it will have
those tow data types ready to use.
And here's how I'd use the sample data you provided to fill the second
table:
SELECTa.b.value('seq[1]','tinyint') AS troutetimerhidx ,
c.d.value('@routed[1]','varchar(10)') AS troutedidx ,
CAST('19000101 ' + c.d.value('.','varchar(10)') AS DATETIME) AS
dprttime
FROM @data.nodes('/trips/trip') a(b)
CROSS APPLY
b.nodes('dprttimes/dprttime') c(d)
Actually, your query below
SELECT
a.b.value('seq[1]','tinyint') AS troutetimerhidx ,
c.d.value('@routed[1]','varchar(10)') AS troutedidx ,
CAST('19000101 ' + c.d.value('.','varchar(10)') AS DATETIME) AS
dprttime
FROM @data.nodes('/trips/trip') a(b)
CROSS APPLY
b.nodes('dprttimes/dprttime') c(d) is great but cannot used to solve my problem.
This is because, you're using <seq>1</seq> as value to insert into tRouteTimerDx.
The requirement is value of tRouteTimerDx(troutetimerhidx) is identity field from tRouteTimerHx(idx).
That's why i'm using
set @idx=SCOPE_IDENTITY(); after each insert into
tRouteTimerHx is completed
The big problem is if <seq>1</seq> will be as <seq>7</seq>, and at that moment there's no idx=7 in tRouteTimerHx
March 11, 2010 at 5:10 am
unfortunately, neither your sample data nor any description did point to that... :ermm:
You might want to look into using the OUTPUT in your first insert statement to capture the id values.
Give it a try and post back if you get stuck.
March 11, 2010 at 5:48 pm
Sir, so far i've as follow,
declare @idx smallint
declare @data xml
set @data='<trips>
<trip>
<routeh>1</routeh>
<seq>1</seq>
<tripnme>trip1</tripnme>
<dprtweekday>1011101</dprtweekday>
<dprttimes>
<dprttime routed="6">9:00AM</dprttime>
<dprttime routed="7">2:30PM</dprttime>
</dprttimes>
</trip>
<trip>
<routeh>1</routeh>
<seq>2</seq>
<tripnme>trip2</tripnme>
<dprtweekday>1110001</dprtweekday>
<dprttimes>
<dprttime routed="6">11:00AM</dprttime>
<dprttime routed="7">4:30PM</dprttime>
</dprttimes>
</trip>
</trips>'
DECLARE @INSERTED_VALUES table
(idx int,
[troutehidx] [smallint] NOT NULL,
[seq] [tinyint] NOT NULL
)
insert into dbo.tRouteTimerHx (troutehidx,seq,tripnme,dprtweekday)
OUTPUT INSERTED.idx,inserted.troutehidx,inserted.seq INTO @INSERTED_VALUES
select a.b.value('routeh[1]','smallint'),a.b.value('seq[1]','tinyint'),
a.b.value('tripnme[1]','varchar(50)'),a.b.value('dprtweekday[1]','char(7)')
from @data.nodes('/trips/trip') a(b)
I'm stuck to get further value,
<dprttimes>
<dprttime routed="6">9:00AM</dprttime>
<dprttime routed="7">2:30PM</dprttime>
</dprttimes>
<dprttimes>
<dprttime routed="6">11:00AM</dprttime>
<dprttime routed="7">4:30PM</dprttime>
</dprttimes>
then insert into 2nd table
insert into dbo.[tRouteTimerDx]
([troutetimerhidx] ,
[troutedidx],
[dprttime]
)
March 12, 2010 at 10:27 am
Once you captured the id values from the first insert you need to join it to the secon xml statement. Something like the following:
;WITH cte AS
(
SELECT
a.b.value('routeh[1]','smallint') troutehidx,
a.b.value('seq[1]','tinyint') AS seq ,
c.d.value('@routed[1]','varchar(10)') AS troutedidx ,
CAST('19000101 ' + c.d.value('.','varchar(10)') AS DATETIME) AS dprttime
FROM @data.nodes('/trips/trip') a(b)
CROSS APPLY
b.nodes('dprttimes/dprttime') c(d)
)
SELECT i.idx, cte.troutedidx, cte.dprttime
FROM cte
INNER JOIN @INSERTED_VALUES i
ON cte.troutehidx = i.troutehidx
AND cte.seq = i.seq
March 12, 2010 at 7:29 pm
Dear Sir,
After a week, your guidance solved my problem.
tq very much
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply