SQL Server Performance

Need help to shred the xml data

Discussion in 'SQL Server 2005 General Developer Questions' started by wkm1925, Feb 15, 2010.

  1. wkm1925 New Member

    /*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*/
    create table SysRunno
    (
    Pref varchar(10) not null,
    Runn int not null
    )
    insert into SysRunno values('_t',1)

    /*my XML data 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>

    --so far, 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>
    '
    DECLARE @TripStart datetime
    SET @TripStart='2010-02-14'-- 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,
    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)

    my result as follow,
    route | tripnme | busno | data | departdate
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    IPH-HPT IPOH-HENTIAN PUTRA1 TX 3269 <departtime cout="IPH">9:00AM</departtime><departtime cout="HPT">2:50PM</departtime> 2010-02-14 00:00:00.000
    IPH-HPT IPOH-HENTIAN PUTRA1 TX 3269 <departtime cout="IPH">9:00AM</departtime><departtime cout="HPT">2:50PM</departtime> 2010-02-20 00:00:00.000
    IPH-HPT IPOH-HENTIAN PUTRA2 TY 1925 <departtime cout="IPH">10:30AM</departtime><departtime cout="HPT">5:00PM</departtime> 2010-02-16 00:00:00.000
    IPH-HPT IPOH-HENTIAN PUTRA2 TY 1925 <departtime cout="IPH">10:30AM</departtime><departtime cout="HPT">5:00PM</departtime> 2010-02-17 00:00:00.000
    How to insert this result into tripschedule and triptimer? 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
    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
    SysRunno
    Pref | Runn
    -------------------
    _t | 5

    Really need help
  2. FrankKalis Moderator

    Is you problem still current or have you found a solution in the meantime?
  3. wkm1925 New Member

    hi sir,
    i found the solution [:D]
  4. FrankKalis Moderator

    Perfect!
    Would it be possible to post your solution here, so that others with similar problems could potentially benefit from it?

Share This Page