how to use xml in sql server 2005

[pkuchaliya]

hi, i m fetching the record from the given xml but not success full
please help

DECLARE @XMLString xml 
set @XMLString ='<timetable>
<day1 id="DY101">
<lecture1 id="LECT101">
<subject>SUB101</subject>
<faculty>FAC101</faculty>
<room>ROOM101</room>
</lecture1>
<lecture2 id="LECT102">
<subject>SUB102</subject>
<faculty>FAC102</faculty>
<room>ROOM102</room>
</lecture2>
<lecture3 id="LECT103">
<subject>SUB103</subject>
<faculty>FAC103</faculty>
<room>ROOM103</room>
</lecture3>
<lecture4 id="LECT104">
<subject>SUB104</subject>
<faculty>FAC104</faculty>
<room>ROOM104</room>
</lecture4>
<lecture5 id="LECT105">
<subject>SUB105</subject>
<faculty>FAC105</faculty>
<room>ROOM105</room>
</lecture5>
<lecture6 id="LECT106">
<subject>SUB106</subject>
<faculty>FAC106</faculty>
<room>ROOM106</room>
</lecture6>
<lecture7 id="LECT107">
<subject>SUB107</subject>
<faculty>FAC107</faculty>
<room>ROOM107</room>
</lecture7>
<lecture8 id="LECT108">
<subject>SUB108</subject>
<faculty>FAC108</faculty>
<room>ROOM108</room>
</lecture8> 
</day1>
<day2 id="DY101">
<lecture1 id="LECT101">
<subject>SUB101</subject>
<faculty>FAC101</faculty>
<room>ROOM101</room>
</lecture1>
<lecture2 id="LECT102">
<subject>SUB101</subject>
<faculty>FAC108</faculty>
<room>ROOM101</room>
</lecture2>
<lecture3 id="LECT103">
<subject>SUB103</subject>
<faculty>FAC103</faculty>
<room>ROOM103</room>
</lecture3>
<lecture4 id="LECT104">
<subject>SUB104</subject>
<faculty>FAC104</faculty>
<room>ROOM104</room>
</lecture4>
<lecture5 id="LECT105">
<subject>SUB105</subject>
<faculty>FAC105</faculty>
<room>ROOM105</room>
</lecture5>
<lecture6 id="LECT106">
<subject>SUB106</subject>
<faculty>FAC106</faculty>
<room>ROOM106</room>
</lecture6>
<lecture7 id="LECT107">
<subject>SUB107</subject>
<faculty>FAC107</faculty>
<room>ROOM107</room>
</lecture7>
<lecture8 id="LECT108">
<subject>SUB108</subject>
<faculty>FAC108</faculty>
<room>ROOM108</room>
</lecture8> </day2>
</timetable>'


declare @day as varchar(50)
declare @count_day as int
declare @lecture as varchar(50)
declare @count_lecture as int 
declare @subject as varchar(50)
declare @faculty as varchar(50)
declare @room as varchar(50)
declare @strsql as nvarchar(max)
declare @output as varchar(max)
declare @test as nvarchar(max)

set @count_day=1
set @count_lecture=1
while @count_day<2
begin
    set @day=@day+convert(varchar,@count_day) 
    while @count_lecture<2
    begin
        set @lecture=@lecture+convert(varchar,@count_lecture) 
            
        set @test='/timetable/day1/lecture1'
        set @strsql ='SELECT timetable.value(''.'', ''varchar(50)'')
        FROM '+N'@XMLString'+'.nodes('''+@test+''') T(timetable)'
         declare @ii as nvarchar(max)
        set @ii=@strsql
        EXEC sp_executesql  @ii
    
SET     @count_lecture = @count_lecture + 1 END
SET     @count_day = @count_day + 1 END