Is there any sort of non-SQL API for talking to SQL Server? I'm curious if there is a more direct way to retrieve table or view data.
(I don't have a problem with SQL, just curious if any of the layer between the SQL parser and the underlying data store is exposed.)
No, not as part of the actual SQL Server product, you'd have to install some other application to present/map the data in the manner you're after. But that would probably have to use SQL to get the data itself, defeating your intention.
Your best bet for retrieving data is SQL, but if you're interested in the plumbing you could look at TDS: http://en.wikipedia.org/wiki/Tabular_Data_Stream
You could look at the FreeTDS library: http://www.freetds.org/faq.html