Using XML -- need help - SQL Server Forums

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)

Using XML -- need help

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-10 : 06:43:21

I'm defined Date Only and Time Only data types based on
[url]http://weblogs.sqlteam.com/jeffs/archive/2007/10/31/sql-server-2005-date-time-only-data-types.aspx[/url]

My tables 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];

My XML data and SQL 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();

insert into dbo.tRouteTimerD
select @idx,......???

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)*/

Really need help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 13:53:41

[code]

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)

insert into dbo.[tRouteTimerDx]
([troutetimerhidx] ,
[troutedidx],
[dprttime]
)
select t.idx,m.n.value('./@routed','smallint'),m.n.value('.','datetime')
from @INSERTED_VALUES t
CROSS JOIN @data.nodes('/trips/trip') a(b)
CROSS APPLY b.nodes('dprttimes/dprttime')m(n)
WHERE a.b.exist('routeh/text() [. = sql:column("t.troutehidx")]') = 1
AND a.b.exist('seq/text()[. = sql:column("t.seq")]') = 1

SELECT * FROM dbo.[tRouteTimerDx]

output
--------------------------------------------
idx troutetimerhidx troutedidx dprttime
1 1 6 1900-01-01 09:00:00.000
2 1 7 1900-01-01 14:30:00.000
3 2 6 1900-01-01 11:00:00.000
4 2 7 1900-01-01 16:30:00.000

[/code]

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