invalid column name when using ".exist"

hi.

i am trying to issued this sql statement:

SELECT    AAA.Table1.*,  
                AAA.Table2.id  as myId,
FROM      AAA.Table1, AAA.Table2
WHERE   AAA.Table1.id = AAA.Table2.id
               and
              ( myColumnForXML.exist('field[value="cat"]') = 1 )
order by AAA.Table1.id

-  if i try to execute that sql stmt it gives me a "AAA column name not found" error.
- and the other weird thing is when i hit the "execute" icon , it does some pre formats on my sql statement (see below):

//notice tha the * is gone and was replaced to all the fields i have in Table1
//notice also that it added the "CROSS JOIN"
SELECT    AAA.Table1.id,  AAA.Table1.name, AAA.Table1.add, AAA.Table1.email, AAA.Table1.others
                AAA.Table2.id  as myId,
FROM      AAA.Table1  CROSS JOIN AAA.Table2
WHERE   AAA.Table1.id = AAA.Table2.id
               and
              ( myColumnForXML.exist('field[value="cat"]') = 1 )
order by AAA.Table1.id

i tried puting extra parenthesis around my exist like this:
( ( (myColumnForXML).exist('field[value="cat"]') = 1 )  )'
 because my colleague had the same problem before and that did the trick.. but mine just deletes the 2 sets of parenthesis i added and im still getting the "column name not found" error.

is this an SQL Server-version related? because we tried running it to my other colleague's sql server and it's working ok.... im using SQL Server Management Studio 2005 on windows 2000....

thanks in advance!

LVL 1
ayeenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
run @@version and check out:
support.microsoft.com/kb/321185

if that returns sql 2005 positively, the other issue might be the database compatibility being set to 80 (sq 2000), hence ignoring the new sql 2005 xml data type and it's implicit functions (like exist)
0
ayeenAuthor Commented:
actually i can run the ".exist" just fine if im just using just one table ...
0
ayeenAuthor Commented:
but if i started using two tables that's when the "invalid column name" appears
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the service pack on the server?
did you apply the same service pack on your client workstation?
0
ayeenAuthor Commented:
is it the service pack for the OS or the sql server itself?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for sql server
0
ayeenAuthor Commented:
the service pack for both the server and my client workstation is the same....

i tried running my sql using an application i did and it's running ok but if i'll try running my sql statement using SQL Server Management Studio 2005 on windows 2000 that's when i get errors and it's like it's pre-formatting my sql statements
0
ayeenAuthor Commented:
hi moderator...

i still haven't solved my problem and i'm abandoning this open question...
but..

can i refund my points? can i get back the 40 pts and you give 30pts to angelIII ....i'd like to give her points for the effort .....

thanks!
0
ayeenAuthor Commented:
cs97jjm3: please see my previous post..

the one that says:

hi moderator...

i still haven't solved my problem and i'm abandoning this open question...
but..

can i refund my points? can i get back the 40 pts and you give 30pts to angelIII ....i'd like to give her points for the effort .....
0
Computer101Commented:
PAQed with points refunded (70)

Computer101
EE Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.