从 SQL 读取保存为文本的 XML

问题描述

这是我的查询的样子:

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 

我不断得到:

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.

我做错了什么??

推荐答案

我不能直接查询prod1，我怎么能找到所有以Other"作为类模板的记录?

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

您不能在同一 SELECT 语句的另一列中引用列别名 - 使用:

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 

如果要根据 WHERE 子句中的 prod2 值过滤掉 - 使用:

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