open xml looping - 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)

open xml looping

Author

Topic

hanumanth
Starting Member

21 Posts

Posted - 2008-07-30 : 09:34:04

Input xml format :
<Request>
<Profile>
<Name>Hanu</Name>
<Name>Siva</Name>
<Name>Ram</Name>
</Profiel>
</Request>
i want to get all the names list
output:
Name
Hanu
Siva
Ram

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 10:06:07

[code]DECLARE @xml xml

SET @xml='<Request>
<Profile>
<Name>Hanu</Name>
<Name>Siva</Name>
<Name>Ram</Name>
</Profiel>
</Request>'

SELECT
NameList.NameValue.value('.','VARCHAR(50)')
FROM @xml.nodes('/Request/Name') as NameList(NameValue)[/code]

hanumanth
Starting Member

21 Posts

Posted - 2008-07-31 : 01:37:39

Dear visakh the above is not working can you check once again plz

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 01:41:45

[code]DECLARE @xml xml

SET @xml =
'<Request>
<Profile>
<Name>Hanu</Name>
<Name>Siva</Name>
<Name>Ram</Name>
</Profile>
</Request>'

SELECT NameList.NameValue.value('.','varchar(50)')
FROM @xml.nodes('/Request/Profile/Name') AS NameList(NameValue)[/code]

KH
[spoiler]Time is always against us[/spoiler]

hanumanth
Starting Member

21 Posts

Posted - 2008-07-31 : 01:50:57

hiii ...

i copied above lines and then executed but following error as displaying.

Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '.'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 01:52:57

quote:
Originally posted by hanumanth

hiii ...

i copied above lines and then executed but following error as displaying.

Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '.'.

Works fine on my machine

KH
[spoiler]Time is always against us[/spoiler]

hanumanth
Starting Member

21 Posts

Posted - 2008-07-31 : 01:55:25

im executing in 2005 and 2000 in both the cases it was showing error. what may be the problem

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 01:59:14

what is the compatibility level in your 2005 ? set it to 90.

xml data type is not supported in SQL 2000

KH
[spoiler]Time is always against us[/spoiler]

hanumanth
Starting Member

21 Posts

Posted - 2008-07-31 : 02:04:05

hi,

i run "sp_dbcmptlevel" then it shows the output as
"Valid values of database compatibility level are 60, 65, 70, or 80."
how to set the compatibility level to 90

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 02:06:01

see http://msdn.microsoft.com/en-us/library/ms178653.aspx

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources