asked
How can I build a VS Solution/Project, which contains views with xquery tables?
Hello guys ! I'm new to this forum but I'm quite active on stackoverflow and had no luck there, so here goes. I've got this VS 2013 solution which contains some views that initially take data from a table which contains an XML column, in turn, I take certain elements from that XML column and I use something similar to following code CREATE VIEW dbo.v_Names AS SELECT A.[Accounts].value('firstname[1]', 'varchar(100)') AS First_Name FROM dbo.v_XML_view AS XV CROSS APPLY [Accounts].nodes('//primary/accounts') AS A([Accounts]); The error is the following: SQL71501: View: [viewname] has an unresolved reference to object [viewname in FROM clause] [script location] 21 3 [database name] I removed the real names for security reasons. This error happens when I try to build the solution, thus it will also prevent me from deploying it as it underlines the whole CROSS APPLY line and also the underlying column that is being used by the CROSS APPLY in the dbo.v_XML_view view. Any help would be appreciated, thanks.
I'm assuming that [Accounts] is a column from the first view? You'll need to change the syntax so that you're using a table name in the CROSS APPLY statement. You can't just reference a column there. It has to be a table, function, or other SELECT statement. Probably the best bet here would be to make it into a SELECT from the view.
@Grant Fritchey, thanks, I'll try that tomorrow and let you know if it worked.
I've sorted it out by creating CTE's to reference the main table where the XML column resides, so I'm using even up to 3 CTE's where necessary, thanks for the tip though !
Sounds fine. The CTE is going to work out the same way as making it a SELECT would have. No fundamental difference. Glad to know you've got it working.
16 People are following this question.
