how to use xml statement in sql server

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

General SQL Server Forums

New to SQL Server Programming

how to use xml statement in sql server

Author

Topic

pkuchaliya
Starting Member

49 Posts

Posted - 2008-10-31 : 06:38:39

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

pankaj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 11:50:47

did you try using nodes() and value() functions?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources