Need help to insert value using XML as parameter

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help to insert value using XML as parameter

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-14 : 01:42:47

/*1st, me create as follow*/
create type Date from dateTime
create type Time from dateTime

/*2nd, me create as follow*/
create rule DateOnlyRule as
dateAdd(dd,datediff(dd,0,@DateTime),0) = @DateTime
go
create rule TimeOnlyRule as
datediff(dd,0,@DateTime) = 0
go

/*3rd, me create as follow*/
EXEC sp_bindrule 'DateOnlyRule', 'Date'
EXEC sp_bindrule 'TimeOnlyRule', 'Time'

/*my table as follow*/
create table tripschedule
(
trnxid int identity primary key,
route varchar(30) not null,
tid char(12) not null,
tripnme varchar(100) not null,
busno varchar(10) not null,
departdate Date not null
)
ALTER TABLE [dbo].[tripschedule] ADD CONSTRAINT [tripschedule_tid] UNIQUE NONCLUSTERED
(
[tid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

create table triptimer
(
trnxid int identity primary key,
tid char(12) not null,
cout varchar(10) not null,
departtime Time not null
)
ALTER TABLE [dbo].[triptimer] WITH CHECK ADD CONSTRAINT [FK_triptimer_tripschedule] FOREIGN KEY([tid])
REFERENCES [dbo].[tripschedule] ([tid])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[triptimer] CHECK CONSTRAINT [FK_triptimer_tripschedule]

/*Relationship between tripschedule and triptimer is 1 to many*/

/*my parameter in XML as follow*/
<trips>
<trip>
<route>IPH-HPT</route>
<tripnme>IPOH-HENTIAN PUTRA1</tripnme>
<busno>TX 3269</busno>
<departweekdays>
<departweekday>1</departweekday>
<departweekday>7</departweekday>
</departweekdays>
<departtimes>
<departtime cout="IPH">9:00AM</departtime>
<departtime cout="HPT">2:50PM</departtime>
</departtimes>
</trip>
<trip>
<route>IPH-HPT</route>
<tripnme>IPOH-HENTIAN PUTRA2</tripnme>
<busno>TY 1925</busno>
<departweekdays>
<departweekday>3</departweekday>
<departweekday>4</departweekday>
</departweekdays>
<departtimes>
<departtime cout="IPH">10:30AM</departtime>
<departtime cout="HPT">5:00PM</departtime>
</departtimes>
</trip>
</trips>

My question is,
1. let's say I'm schedule trip between 14 Feb 2010 to 20 Feb 2010.
2. How my T-SQL looks like to insert value in XML as above?

Then, my result as follow
tripschedule
route | tid | tripnme | busno | departdate
-------------------------------------------------------------------
IPH-HPT t0000000001 IPOH-HENTIAN PUTRA1 TX 3269 2010-02-14
IPH-HPT t0000000002 IPOH-HENTIAN PUTRA1 TX 3269 2010-02-20
IPH-HPT t0000000003 IPOH-HENTIAN PUTRA2 TY 1925 2010-02-16
IPH-HPT t0000000004 IPOH-HENTIAN PUTRA2 TY 1925 2010-02-17
/*tid is a running no with prefix t000000000x*/

triptimer
tid | cout | departtime
------------------------------------
t0000000001 IPH 9:00AM
t0000000001 HPT 2:50PM
t0000000002 IPH 9:00AM
t0000000002 HPT 2:50PM
t0000000003 IPH 10:30AM
t0000000003 HPT 5:00PM
t0000000004 IPH 10:30AM
t0000000004 HPT 5:00PM

Need help. I'm stuck

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-14 : 11:59:40

I've as follow,
declare @trips xml
set @trips='<trips>
<trip>
<route>IPH-HPT</route>
<tripnme>IPOH-HENTIAN PUTRA1</tripnme>
<busno>TX 3269</busno>
<departweekdays>
<departweekday>1</departweekday>
<departweekday>7</departweekday>
</departweekdays>
<departtimes>
<departtime cout="IPH">9:00AM</departtime>
<departtime cout="HPT">2:50PM</departtime>
</departtimes>
</trip>
<trip>
<route>IPH-HPT</route>
<tripnme>IPOH-HENTIAN PUTRA2</tripnme>
<busno>TY 1925</busno>
<departweekdays>
<departweekday>3</departweekday>
<departweekday>4</departweekday>
</departweekdays>
<departtimes>
<departtime cout="IPH">10:30AM</departtime>
<departtime cout="HPT">5:00PM</departtime>
</departtimes>
</trip>
</trips>
'

SELECT
c.value('route[1]','varchar(30)') [route],
c.value('tripnme[1]','varchar(30)') tripnme,
c.value('busno[1]','varchar(30)') busno
FROM @trips.nodes('trips') a(b)
CROSS APPLY
b.nodes('trip') t(c)

SELECT
busno,
Node.value('(/departtime/@cout)[1]','varchar(30)') AS cout,
Node.value('(/departtime)[1]','varchar(30)') AS departtime
FROM
(SELECT X.Y.query('.') AS Node,
b.value('busno[1]','varchar(30)') AS busno
FROM @trips.nodes('trips/trip') a(b)
CROSS APPLY
b.nodes('departtimes/departtime') X(Y)
) Z

I want to insert the result from 14 Feb 2010 to 20 Feb 2010. This insert based on departweekdays. So, it will insert on 14 Feb 2010, 20 Feb 2010, 16 Feb 2010, and 17 Feb 2010. 14,20,16,and 17 Feb 2010 become as departdate in tripschedule

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-14 : 12:08:11

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-15 : 02:56:21

quote:

Originally posted by visakh16


declare @trips xml
set @trips='<trips>
<trip>
<route>IPH-HPT</route>
<tripnme>IPOH-HENTIAN PUTRA1</tripnme>
<busno>TX 3269</busno>
<departweekdays> 
<departweekday>1</departweekday>
<departweekday>7</departweekday>
</departweekdays>
<departtimes>
<departtime cout="IPH">9:00AM</departtime>
<departtime cout="HPT">2:50PM</departtime>
</departtimes>
</trip>
<trip>
<route>IPH-HPT</route>
<tripnme>IPOH-HENTIAN PUTRA2</tripnme>
<busno>TY 1925</busno>
<departweekdays> 
<departweekday>3</departweekday>
<departweekday>4</departweekday>
</departweekdays>
<departtimes>
<departtime cout="IPH">10:30AM</departtime>
<departtime cout="HPT">5:00PM</departtime>
</departtimes>
</trip>
</trips>
'
DECLARE @TripStart date
SET @TripStart='2010-02-14'--your trip start date
SELECT 
c.value('route[1]','varchar(30)') [route],
c.value('tripnme[1]','varchar(30)') tripnme,
c.value('busno[1]','varchar(30)') busno,
DATEADD(dd,v.value('.','int') -1,@TripStart) departdate
FROM @trips.nodes('trips') a(b)
CROSS APPLY
b.nodes('trip') t(c)
CROSS APPLY c.nodes('departweekdays/departweekday') u(v)

SELECT 
busno,
Node.value('(/departtime/@cout)[1]','varchar(30)') AS cout,
Node.value('(/departtime)[1]','varchar(30)') AS departtime
FROM
(SELECT X.Y.query('.') AS Node,
b.value('busno[1]','varchar(30)') AS busno
FROM @trips.nodes('trips/trip') a(b)
CROSS APPLY
b.nodes('departtimes/departtime') X(Y)
) Z

output
-----------------------------------------------
route	tripnme	busno	departdate
IPH-HPT	IPOH-HENTIAN PUTRA1	TX 3269	2010-02-14
IPH-HPT	IPOH-HENTIAN PUTRA1	TX 3269	2010-02-20
IPH-HPT	IPOH-HENTIAN PUTRA2	TY 1925	2010-02-16
IPH-HPT	IPOH-HENTIAN PUTRA2	TY 1925	2010-02-17


busno	cout	departtime
TX 3269	IPH	9:00AM
TX 3269	HPT	2:50PM
TY 1925	IPH	10:30AM
TY 1925	HPT	5:00PM

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

I've as follow,


SELECT 
c.value('route[1]','varchar(30)') [route],
c.value('tripnme[1]','varchar(30)') tripnme,
c.value('busno[1]','varchar(30)') busno,
c.query('departtimes/*') AS data,DATEADD(dd,v.value('.','int') -1,@TripStart) departdate
FROM @trips.nodes('trips') a(b)
CROSS APPLY
b.nodes('trip') t(c)
CROSS APPLY c.nodes('departweekdays/departweekday') u(v)

How to adjust my SQL above, and the final result as follow

tripschedule
route | tid | tripnme | busno | departdate
-------------------------------------------------------------------
IPH-HPT t0000000001 IPOH-HENTIAN PUTRA1 TX 3269 2010-02-14
IPH-HPT t0000000002 IPOH-HENTIAN PUTRA1 TX 3269 2010-02-20
IPH-HPT t0000000003 IPOH-HENTIAN PUTRA2 TY 1925 2010-02-16
IPH-HPT t0000000004 IPOH-HENTIAN PUTRA2 TY 1925 2010-02-17
/*
tid is a running no with prefix t000000000x
Running no in table
tSysNo
Code | RunNo
----------------
tid | 0
*/

triptimer
tid | cout | departtime
------------------------------------
t0000000001 IPH 9:00AM
t0000000001 HPT 2:50PM
t0000000002 IPH 9:00AM
t0000000002 HPT 2:50PM
t0000000003 IPH 10:30AM
t0000000003 HPT 5:00PM
t0000000004 IPH 10:30AM
t0000000004 HPT 5:00PM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 08:41:05

[code]
declare @trips xml
set @trips='<trips>
<trip>
<route>IPH-HPT</route>
<tripnme>IPOH-HENTIAN PUTRA1</tripnme>
<busno>TX 3269</busno>
<departweekdays>
<departweekday>1</departweekday>
<departweekday>7</departweekday>
</departweekdays>
<departtimes>
<departtime cout="IPH">9:00AM</departtime>
<departtime cout="HPT">2:50PM</departtime>
</departtimes>
</trip>
<trip>
<route>IPH-HPT</route>
<tripnme>IPOH-HENTIAN PUTRA2</tripnme>
<busno>TY 1925</busno>
<departweekdays>
<departweekday>3</departweekday>
<departweekday>4</departweekday>
</departweekdays>
<departtimes>
<departtime cout="IPH">10:30AM</departtime>
<departtime cout="HPT">5:00PM</departtime>
</departtimes>
</trip>
</trips>
'
DECLARE @TripStart date
SET @TripStart='2010-02-14'--your trip start date

CREATE TABLE #tripschedule
(
ID int identity(1,1),
tid as 't' + RIGHT ('000000000' + CAST(ID AS varchar(5)),10),
[route] varchar(30),
tripnme varchar(30),
busno varchar(30),
departdate datetime
)

CREATE TABLE #triptimer
(
tid varchar(11),
cout varchar(10),
departtime varchar(30)
)

INSERT #tripschedule
SELECT
c.value('route[1]','varchar(30)') [route],
c.value('tripnme[1]','varchar(30)') tripnme,
c.value('busno[1]','varchar(30)') busno,
DATEADD(dd,v.value('.','int') -1,@TripStart) departdate
FROM @trips.nodes('trips') a(b)
CROSS APPLY
b.nodes('trip') t(c)
CROSS APPLY c.nodes('departweekdays/departweekday') u(v)

INSERT #triptimer
SELECT
s.tid,
Node.value('(/departtime/@cout)[1]','varchar(30)') AS cout,
Node.value('(/departtime)[1]','varchar(30)') AS departtime--,

FROM
(SELECT X.Y.query('.') AS Node,
b.value('busno[1]','varchar(30)') AS busno,
b.value('tripnme[1]','varchar(30)') AS tripme,
DATEADD(dd,v.value('.','int') -1,@TripStart) departdate
FROM @trips.nodes('trips/trip') a(b)
CROSS APPLY
b.nodes('departtimes/departtime') X(Y)
CROSS APPLY
b.nodes('departweekdays/departweekday') u(v)
) Z
INNER JOIN #tripschedule s
ON s.tripnme= Z.tripme
AND s.departdate=Z.departdate

SELECT * FROM #tripschedule
SELECT * FROM #triptimer

drop table #tripschedule
drop table #triptimer

output
------------------------------------------------
tripschedule
ID tid route tripnme busno departdate
1 t0000000001 IPH-HPT IPOH-HENTIAN PUTRA1 TX 3269 2010-02-14 00:00:00.000
2 t0000000002 IPH-HPT IPOH-HENTIAN PUTRA1 TX 3269 2010-02-20 00:00:00.000
3 t0000000003 IPH-HPT IPOH-HENTIAN PUTRA2 TY 1925 2010-02-16 00:00:00.000
4 t0000000004 IPH-HPT IPOH-HENTIAN PUTRA2 TY 1925 2010-02-17 00:00:00.000

triptimer
tid cout departtime
t0000000001 IPH 9:00AM
t0000000001 HPT 2:50PM
t0000000002 IPH 9:00AM
t0000000002 HPT 2:50PM
t0000000003 IPH 10:30AM
t0000000003 HPT 5:00PM
t0000000004 IPH 10:30AM
t0000000004 HPT 5:00PM

[/code]

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

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-15 : 10:10:55

Your guide, is my inspiration.

tq very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 10:33:31

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources