Access SqlXml returned from SQLCLR stored procedure in SQL Server

Question

I have some C# code (SQLCLR) which is reading an XML file from the local machine into a stream and returning it to SQL as SQLXML:

C# code

 Stream str = File.OpenRead(xmlFilePath);
 sqlXMLToReturn = new SqlXml(str);

SQL CLR Call from SQL Server 2014:

DECLARE @ResultForPos xml
EXEC @ResultForPos = spGetXmlForFile
SELECT @ResultForPos

My breakpoints in C# code gets hit when I debug the above SQL Script from SQL Server Object explorer inside Visual Studio 2015. There are no exceptions raised in C# code. However after running the script I get the following error in SQL Server.

Msg 206, Level 16, State 2, Procedure spWcfCall, Line 43
Operand type clash: int is incompatible with xml

What am I doing wrong? Do I have to do sqlXMLToReturn.Value and return a string instead of SQLXML.

Solomon Rutzky · Accepted Answer · 2017-04-12 14:02:47Z

1

Stored Procedures, whether they are pure T-SQL or SQLCLR, always return INT / SqlInt32. If you want to pass back XML / SqlXml, then you need to use an OUTPUT / out / ref parameter.

The syntax, in the end, should look similar to the following:

EXEC dbo.spGetXmlForFile @OutParamName = @ResultForPos OUTPUT;

edited Apr 12, 2017 at 14:02

answered Apr 11, 2017 at 15:22

Solomon Rutzky

49.3k11 gold badges141 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Abe Over a year ago

Can I get a simple example please. When I pass in a Out parameter i still get the same error. Thank you.

Solomon Rutzky Over a year ago

@Abe If you get the same error then you are still using the EXEC @ResultForPos = spGetXmlForFile syntax, which can't work since stored procedures only return INT. I have updated my answer with the expected syntax.

Solomon Rutzky Over a year ago

@Abe Were you able to get any farther on this?

Collectives™ on Stack Overflow

Access SqlXml returned from SQLCLR stored procedure in SQL Server

C# code

SQL CLR Call from SQL Server 2014:

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

C# code

SQL CLR Call from SQL Server 2014:

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related