3

this is what my query looks like:

select top 5   cast(content_html as xml) as [prodxml],
prodxml.value('data(ClassTemplate[1]', 'nvarchar(max) ') as prod2
from content 
where 
end_date >= getdate()
and folder_id != 71682

and i keep getting:

Msg 4121, Level 16, State 1, Line 1
Cannot find either column "prodxml" or the user-defined function or aggregate "prodxml.value", or the name is ambiguous.

what am i doing wrong??

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
Madam Zu Zu
  • 6,437
  • 19
  • 83
  • 129

1 Answers1

3

i can't query prod1 directly, how else can i find all records that have "Other" as the Class Template?

You can't reference a column alias in another column of the same SELECT statement - use:

SELECT TOP 5   
       CAST(t.content_html AS XML).value('(/root/ClassTemplate)[1]', 'NVARCHAR(max)') AS prod2
  FROM CONTENT t
 WHERE t.end_date >= getdate()
   AND t.folder_id != 71682

If you want to filter out based on the prod2 value in the WHERE clause - use:

  FROM CONTENT t
 WHERE CAST(t.content_html AS XML).value('(/root/ClassTemplate)[1]', 'NVARCHAR(max)') = 'Other'
   AND t.end_date >= getdate()
   AND t.folder_id != 71682
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • select top 5 cast(content_html as xml).value('data(ClassTemplate[1]', 'nvarchar(500)) ') as prod1 from content where content_status = 'A' and content_type=3333 and end_date >= getdate() and folder_id != 71682 and i am now getting: The data type 'nvarchar(500)) ' used in the VALUE method is invalid. – Madam Zu Zu Oct 22 '10 at 21:47
  • @Tanya Xrum: There's a typo in what you posted - `'nvarchar(500))` should be `nvarchar(500)` – OMG Ponies Oct 22 '10 at 21:48
  • thanks, now i get 5 nulls..i should be getting the values of the "ClassTemplate" node, right? – Madam Zu Zu Oct 22 '10 at 21:52
  • There's a missing close bracket I think? `'data(ClassTemplate[1]'` should be `'data(ClassTemplate[1])'` – Martin Smith Oct 22 '10 at 21:53
  • @Tanya Xrum: The query is looking for a `data(ClassTemplate` node - can you post a sample of the XML? – OMG Ponies Oct 22 '10 at 21:53
  • Other test class description Demonstration Bethesda dneal@lacademie.com menu 2010-10-17 01 00 AM 2010-11-06 01 00 AM – Madam Zu Zu Oct 22 '10 at 21:54
  • hm, if i can't query prod1 directly, how else can i find all records that have "Other" as the Class Template? – Madam Zu Zu Oct 22 '10 at 21:56
  • @Tanya Xrum: I don't see how, based on the sample you provided - I tested on SQL Server 2005. – OMG Ponies Oct 23 '10 at 01:18
  • thanks! :) now i'm getting XML parsing: line 1, character 88, illegal name character, but i believe it's because i have some "&" in my code. Thanks again!!! – Madam Zu Zu Oct 23 '10 at 01:25