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.
| Author |
Topic |
|
maximus007
Starting Member
18 Posts |
 Posted - 2009-05-19 : 15:11:01
|
| Hi this sql statement works find in sql server 2005 but not in 2000. I would like to know how can modify the stament to work in sql 2000.I try a couple of things but was not successfull. but the way it's a menu tree. Thanks Max.--// groups select mg.GroupId as '@id' ,mg.ParentId as '@parentid' ,mg.GroupDesc as '@groupdesc' ,mg.GroupText as '@grouptext' -- // menu items ,( select mi.MenuId as '@id' ,mi.ActionId as '@actionid' ,mi.GroupId as '@groupid' ,mi.MenuText as '@menutext' ,mi.MenuDesc as '@menudesc' ,mi.URL as '@url' from MenuItems mi where mi.Enabled = 1 and mi.GroupId = mg.GroupId order by mi.Sort for xml xpath('item'), type ) from MenuGroups mg with (nolock) where mg.Enabled = 1 order by mg.ParentId, mg.Sort for xml xpath('group'), root('PGis') |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-19 : 15:33:42
|
| FOR XML PATH is not available in SQL Server 2000. You'll probably have to use FOR XML EXPLICIT to get similar output. |
 |
|
|
maximus007
Starting Member
18 Posts |
Posted - 2009-05-19 : 15:43:01
|
| It my fault for leave the "Path" in there. I did tried with explicit, auto, raw and they all give me "Incorrect syntax near the keyword 'FOR'. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-19 : 15:44:39
|
| The "root" keyword is not supported either, and the ORDER BY clause has to be after all other clauses. |
|
|
|
maximus007
Starting Member
18 Posts |
Posted - 2009-05-19 : 15:53:13
|
| Wow we really need to upgrade. now i am getting:Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-19 : 16:50:22
|
| You'll have to rewrite this completely to work with FOR XML EXPLICIT, the nested subquery will have to come out and you'll need to do UNIONs. |
|
|
|
|
|
|
|
|
sql statement