Can't find my c# CLR table value assembly in the list or create the associate SQL function

Question

I create an assembly, I can see it Management studio under Assemblies however when I try to list it with select * from sys.assembly_modules I can't see it. Also I can't create the SQL function calling it.

My c# code

public partial class UserDefinedFunctions
{
    private class Xmlparsing { 
        public SqlString question_text;
        public SqlString answer_text;
        public Xmlparsing(SqlString question_text, SqlString answer_text) {

            this.question_text = question_text;
            this.answer_text = answer_text;
        }
    [Microsoft.SqlServer.Server.SqlFunction(
            DataAccess=DataAccessKind.Read,
            FillRowMethodName="ParseResponse_FillRow",
            TableDefinition="question_text varchar(max),answer_text varchar(max)")]
    public static IEnumerable ParseResponse(SqlString xmldata)
    {
            // Put your code here
            String response_content = xmldata.ToString();
            XDocument doc = XDocument.Parse(response_content);
            var list = doc.Elements("response").Elements("questions").Elements("question").Select(X => new { question_text = X.Element("question_text").Value, answer_text = X.Element("answer_text").Value }).ToArray();
            return list;
        }
        public static void ParseResponse_FillRow(object XmlparsingObj, out SqlString question_text,out SqlString answer_text)
        {
            Xmlparsing parseresult = (Xmlparsing)XmlparsingObj;
            question_text = parseresult.question_text;
            answer_text = parseresult.answer_text;


        }
    }

And the SQL code I am trying to use is :

CREATE FUNCTION [dbo].[XmlParsing](@xmldata [varchar])
RETURNS [datetime] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [XmlParsing].[UserDefinedFunctions].ParseResponse

But it's giving me the error :

Msg 6506, Level 16, State 1, Procedure XmlParsing, Line 6
Could not find method 'ParseResponse' for type 'UserDefinedFunctions' in assembly 'XmlParsing'

[edit] This is my new code:

using System.Linq;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Xml.Linq;
using System.Collections.Generic;

public partial class UserDefinedFunctions
{


    [Microsoft.SqlServer.Server.SqlFunction(
            FillRowMethodName="ParseResponse_FillRow",
            TableDefinition="question_text nvarchar(max),answer_text nvarchar(max)")]
    public static IEnumerable ParseResponse(SqlString xmldata)
    {
            // Put your code here
            String response_content = xmldata.ToString();
            XDocument doc = XDocument.Parse(response_content);
            var list = doc.Elements("response").Elements("questions").Elements("question").Select(X => new { question_text = X.Element("question_text").Value, answer_text = X.Element("answer_text").Value }).ToArray();
            return list;
        }
        public static void ParseResponse_FillRow(object row, out SqlString question_text,out SqlString answer_text)
        {

            question_text = row.GetType().GetProperty("question_text").GetValue(row,null).ToString();
            answer_text = row.GetType().GetProperty("answer_text").GetValue(row, null).ToString();


        }

}

Answer 1

There are several issues here:

1. While not specific to the C# code or related T-SQL code, I am stating this first for visibility: why are you using SQLCLR to do simple XML parsing when the XML datatype does that, and would most likely be faster for such a simple case?

2. CREATE FUNCTION [dbo].[XmlParsing] : Shouldn't the function name be ParseResponse ?

3. (@xmldata [varchar]) : You should never specify a string type ( CHAR, VARCHAR, NCHAR, and NVARCHAR ) without a length. In some cases the default size is 1, and in other cases it is 30. It depends on where the declaration is being done (e.g. variable vs column vs parameter etc).

   Also, the SQLCLR API only allows for NVARCHAR, not VARCHAR, so you will need to change this datatype anyway. Besides, XML data in SQL Server is encoded as UTF-16, so it is best to use NVARCHAR instead of VARCHAR for XML data, even when handling everything in T-SQL.

   Hence your input parameter datatype should be NVARCHAR(MAX).

4. If at all possible, pass in XML data as XML. So, it would be even better to use XML as the input parameter datatype.

5. RETURNS [datetime] : Your function is returning a table / IEnumerable yet your return type in the T-SQL wrapper object is a scalar type. This will never work. You need to change the [datetime] to be TABLE (question_text NVARCHAR(MAX), answer_text NVARCHAR(MAX)).

6. DataAccess=DataAccessKind.Read : You aren't connecting to the database so you should either remove this, or change it to be DataAccessKind.None. Setting it to Read is a performance hit, and there is no reason for it here.

7. (SqlString xmldata) : If you do change the input parameter datatype to be XML, then change the SqlString here to be SqlXml. This will also change how you get the XmlDocument since SqlXml will give you an XmlReader (I believe).