CLR Problem
-
I have a function in my dll which calls xml from a url passed to it and is supposed to send this back to the t-sql calling it so i can import it directly into my db.
namespace XmlPuller
{
public class XmlFunctions
{
public static void getXML(string url)
{
using (SqlConnection conn = new SqlConnection("context connection = true"))
{
// Retrieve the RSS feed
XPathDocument doc = new XPathDocument(url);
XPathNavigator nav = doc.CreateNavigator();
XPathNodeIterator i = nav.Select("//Item");
// create metadata for four columns
SqlMetaData[] smdResults = new SqlMetaData[5];
smdResults[0] = new SqlMetaData("field1", SqlDbType.NVarChar, 1000);
smdResults[1] = new SqlMetaData("field2", SqlDbType.NVarChar, 150);
smdResults[2] = new SqlMetaData("field3", SqlDbType.NVarChar, -1);
smdResults[3] = new SqlMetaData("field4", SqlDbType.DateTime);
smdResults[4] = new SqlMetaData("field5", SqlDbType.NVarChar, 50);
// construct the record which holds metadata and data buffers
SqlDataRecord record = new SqlDataRecord(smdResults);
// Store reference to our pipe
SqlPipe pipe = SqlContext.Pipe;
// Send the metadata (not the values)
pipe.SendResultsStart(record);
// Iterate through XPathNavigator results (RSS items)
// For each interation we'll send down a row of data to the client
while (i.MoveNext())
{
//Set Title
record.SetString(0, (string)i.Current.Evaluate("string(field1[1]/text())"));
record.SetString(1, (string)i.Current.Evaluate("string(field2[1]/text())"));
record.SetString(2, (string)i.Current.Evaluate("string(field3[1]/text())"));
record.SetDateTime(3, DateTime.Parse((string)
//Set Publication Date
i.Current.Evaluate("string(field4[1]/text())")));
record.SetString(4, (string)i.Current.Evaluate("string(field5[1]/text())"));
//Send row to client
pipe.SendResultsRow(record);
}
// Let client know we're done
pipe.SendResultsEnd();
}
}
}
}
This all compiles fine. I register it in SQL server as an assembly like so:
CREATE ASSEMBLY XMLPuller FROM 'd:\xmlpuller.dll' WITH PERMISSION_SET = SAFE
GO
but when I try and add my function which will do the work, i'm getting the following error:
"T-SQL and CLR types for return value do not match"
The line is:
CREATE function fn_getXML(@url nvarchar(max))
returns nvarchar(max) AS
EXTERNAL NAME [XMLPuller].[XMLPuller.XmlFunctions].[getXML]
GO
Can anyone help?! I'm not sure where I'm going wrong.
-
your C# code declares the function as static void, yet the function definition TSQL declares it as returning nvarchar(MAX).
Having said that I'm not so sure what you are trying to do and why you would attempt to implement it in this way. Perhaps if you would elaborate a bit more on the bigger picture.
The probability of survival is inversely proportional to the angle of arrival.
-
I would have expected your function to look like this
CREATE function fn_getXML(@url nvarchar(max))
returns TABLE (field1 nvarchar(1000), field2 nvarchar(150), field3 nvarchar(max), field4 datetime, field5 nvarchar(50))
EXTERNAL NAME [XMLPuller].[XMLPuller.XmlFunctions].[getXML]
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi guys, cheers for the responses.
I've tried the RETURNS Table () on it but I still get an error. 🙁
I'm kind of new to the CLR thing so forgive me if I'm being stupid. Sturner, would you have any other recommendations for a solution to this? I basically need to pull XML from an external URL, converting and storing the data in row format.
-
I think your most flexible option would be to have a CLR scalar valued function that just downloads the XML and returns the data as XML, then do the XML manipulation in SQL (it's got good native XML handling). That way, if the XML document changes, you don't have to recompile your CLR.
You could also rewrite the CLR to be a Table Valued Function - which is what you're trying to achieve above
-
Does the data from this URL have to be pulled on demand by a client doing a SQL query or is it something that occurs on a schedule or in the background, where the client can get the data from a table?
Unless your requirements demand the data is always obtained directly from the url immediately I would have implemented this as an external process that inserts/updates the data to a real database table. Whether you implemented it as a process waiting on a broker queue or just a process that runs on a schedule.
I am leery of designs that implement real-time network requests to obtain data for SQL clients for obvious reasons (what if 1000 clients execute the same query... what if the network is down... or very slow, or the URL times out...etc. etc.) I would look at designs that decouple that aspect from the clients, funnel the requests to a service to control resources and perhaps buffer data when necessary.
If you must always execute this in real-time I'd go along with HowardW's suggestion and simply return a string (or appropriate error if the request fails).
The probability of survival is inversely proportional to the angle of arrival.
-
It's going to be a scheduled task, not something called by clients. so, that in mind, is it best i return a table of results or the block xml then have my stored proc do the manipulation of xml. I'm liking your vibe Howard on keeping the CLR function 'general' as i could re-use it then with other xml imports.
As a bit of background, im doing this to get out of the old habbit of using vbs scripts on the server for this kind of stuff. Its too old school and i have a batch of 40+ feeds i need to pull from thier respective url's, pull into the database and convert to my table structure once a day. We used to use VBS scripts for things like this on a smaller scale of course but they're so unreliable i want to step into the 21 century 🙂
-
Maybe I'm missing something, but isn't your function returning XML? So wouldn't it be:
CREATE function fn_getXML(@url nvarchar(max))returns XML AS
EXTERNAL NAME [XMLPuller].[XMLPuller.XmlFunctions].[getXML]
GO
(*edit: nevermind ... I looked at it more and see it is not)
Also, you could do this through SSIS as well. And honestly, with something that's scheduled that's probably what I would do.
-
FYI - if you do use a CLR for this, you must set the permission set to EXTERNAL_ACCESS if it needs access to network resources.
-
I'm pretty sure we don't have SSIS on our version of SQL Server. Is CLR my only option?
-
It's not your only option, but it's one of the better ones, especially if you're familiar with .net languages.
Which edition are you using? SSIS is well suited to batch jobs, but I don't think it's XML handling is particularly good and you'd end up using a .Net script task to download the XML anyway. The real advantage it gives is you're not opening your SQL Server up to external access.
A CLR with proper error handling/validation/timeout settings and that you restrict user access to is a good solution, so I wouldn't be put off.
-
I'm not hugely familiar with .net but I was thinking something along the lines of:
.NET Script:
Download the XML and place it in the DB
Stored Proc:
Get the XML and manipulate it into my table data.
-
I do something similar to this... hit a number of URLs and download various zipped files, move them to other servers and load them into different databases.
I elected to take the approach of building an external .net program to do this. It runs from a control table which specifies the URL, the type of download, login credentials, the name of files to be unzipped and what to do with them and what stored procedures to execute after bulk inserting, etc. etc. I felt having a centralized program that could run from any database was more flexible and easier to manage then dozens of specialized CLR assemblies.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply