Creating a table method on a user defined type (like like 'nodes' on the XML data type)

Question

I've created working SQLCLR-based user defined table-valued functions as well as user defined types.

What I want now is a method on a SQLCLR UDT that returns a table, similar to the nodes method on the XML data type.

The TableDefinition and FillRowMethodName properties of the SqlMethod attribute / decoration seem to imply that it should be possible, but nothing actually works. When I call the method like this (which I expect to fail):

SELECT @Instance.AsTable();

I get:

invalid data type

which is not the same error I get if I call a SQLCLR table-valued function that way.

When I call like this:

SELECT * FROM @Instance.AsTable();

I get the (possibly expected) error:

The table (and its columns) returned by a table-valued method need to be aliased.

When I call like this:

SELECT * FROM @Instance.AsTable() t(c1,c2);

It says:

Table-valued function 'AsTable' cannot have a column alias

I had a feeling the problem might be related to the fact that table functions are expected to be static so I also tried implementing it as an extension method but in that case it won't even compile. I get:

Extension method must be defined in a non-generic static class

Obviously I could implement it as a normal SQLCLR table function that expects a parameter of my UDT type. In fact I've actually done this and SELECT * FROM dbo.AsTable(@Instance); works but I don't like that solution. I'd really like to get this syntax to work: SELECT * FROM @Instance.AsTable() t(c1,c2);

Here's one non-working version:

[SqlMethod(IsDeterministic = false, DataAccess = DataAccessKind.None, OnNullCall=true
    , TableDefinition = "RowKey nvarchar(32),RowValue nvarchar(1000)"
    , FillRowMethodName = "FormatLanguageRow")]
public IEnumerable AllRows()
{
    return _rowCollection;
}

Answer 1

Sadly, it is not possible to create a method on a UDT that returns a table (i.e. returns IEnumerable). Your confusion regarding the two properties of the SqlMethod attribute are quite understandable as they are misleading. If you take a look at the MSDN page for SqlMethodAttribute Class, you will see the following note in the "Remarks" section:

SqlMethodAttribute inherits from a SqlFunctionAttribute, so SqlMethodAttribute inherits the FillRowMethodName and TableDefinition fields from SqlFunctionAttribute. Note that it is not possible to write a table-valued method, although the names of these fields might suggest that it is possible.

Outside of the work-around that you have already tried (i.e. creating a TVF), you could return the data as XML (i.e. SqlXml) from a UDT method. It certainly won't be as efficient as a streaming TVF, but it is returning a value that can be turned into a table inline via the nodes() method of the XML datatype.

---

UPDATE (2017-01-16)

I have formally submitted this as a Microsoft Connect suggestion:
Allow CLR UDT to return IEnumerable / Table / TVF from a method ( SqlMethod ) like XML.nodes()