Fastest way to move XML document into and back out of CLR function

by DR on 10/31/2007 11:42:00 AM Fastest way to move XML document into and back out of CLR function

In SQL Server 2005 and Visual Studio 2005, what is the fastest way to pass
an xml data type variable into a C# CLR function and retrieve it back out of
the C# CLR function when the C# CLR function is done modifying it?

I tried this but get deployment error becuase SQLServer2005/CLR dotn support
System.Xml.XmlDocument:

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static int InsertSomeNode(System.Xml.XmlDocument pDoc)
    {
        //
        return 1;
    }
};

thorws error:
Error 1 Column, parameter, or variable #1: Cannot find data type
XmlDocument. InsertSomeNode


 

Re: Fastest way to move XML document into and back out of CLR function

by Nicholas Paldino [.NET/C# MVP] on 10/31/2007 6:23:00 PM Well, the fastest way to move an XML document into and out of a CLR
function is to just send an XmlDocument. However, SQL Server isn't going to
let you do that outright.

    Rather, you want to declare the pDoc parameter as a string, and then
parse the document inside the CLR procedure.

    You might be able to get away with declaring the stored procedure as an
output parameter, but I don't know if the xml type can be passed in then.
It should be easy enough to figure it out with a simple example.

--
          - Nicholas Paldino [.NET/C# MVP]
          - mvp@spam.guard.caspershouse.com

"DR" <softwareengineer98037@yahoo.com> wrote in message
news:ukEWkyCHIHA.1548@TK2MSFTNGP05.phx.gbl...
> Fastest way to move XML document into and back out of CLR function
>
> In SQL Server 2005 and Visual Studio 2005, what is the fastest way to pass
> an xml data type variable into a C# CLR function and retrieve it back out
> of the C# CLR function when the C# CLR function is done modifying it?
>
> I tried this but get deployment error becuase SQLServer2005/CLR dotn
> support System.Xml.XmlDocument:
>
> public partial class UserDefinedFunctions
> {
> [Microsoft.SqlServer.Server.SqlFunction]
> public static int InsertSomeNode(System.Xml.XmlDocument pDoc)
> {
> //
> return 1;
> }
> };
>
> thorws error:
> Error 1 Column, parameter, or variable #1: Cannot find data type
> XmlDocument. InsertSomeNode
>
>