XML Serialization Issue | Microsoft Connect

XML Serialization Issue by Ganesh Muthuvelu

Closed

as Postponed Help for as Postponed

Type:	Bug
ID:	209210
Opened:	9/22/2006 11:29:38 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

A .NET assembly that generates a XML schema at run time cannot be called from SQL Server 2005. Please note that the assembly has been "sgen" and both assemblies are loaded into SQL Server with "SAFE" permission.

Below is the error message:
Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information.

Details (expand)

English

SQL Server 2005 - Standard Edition

XML Programmability

Windows Server 2003 (all Win32 editions)

Not Applicable

1) Build the assembly "XML_Serializer_Issue" (attached project)
2) "sgen" the assembly.
3) In my SQL Server 2005 database, I added these two assemblies. (XML_Serializer_Issue and XML_Serializer_Issue_XmlSerializers) and marked them as "SAFE".
4) I then created a function in the database as below:

CREATE FUNCTION dbo.XML_Issue()
RETURNS nvarchar(4000)

AS EXTERNAL NAME XML_Serializer_Issue.[XML_NS.MCL_Schema_Generator].GetSchema

5) If I run the query, select dbo.XML_Issue(), I get the following error:

******************
The Schema is EMPTY?? Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. The execution is complete.
******************
The lines in bold are what I get from SQL Server. The lines not in bold are what I added for debugging as you can see in my code.

6) I expect a XML Schema string as the query result.

The Schema is EMPTY?? Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. The execution is complete.

A XML Schema String..

0 attachments

Steven Hemingray already followed up with the problem offline in email, and I am just posting the findings here.

The issue is in method GetSchemaString, which calls XmlSchema.Write:
Public Function GetSchemaString(ByVal GeneratedSchema As XmlSchema, ByRef ErrMsg As String) As String
        Dim XMLSchemaStr As String
        Dim memStream As New MemoryStream
        Dim Writer As XmlTextWriter
        Try
            Writer = New XmlTextWriter(memStream, New UTF8Encoding)
            Writer.Formatting = Formatting.Indented
            GeneratedSchema.Write(Writer)

XmlSchema.Write however calls
XmlSerializer serializer = new XmlSerializer(typeof(XmlSchema));

This causes Serialization.TempAssembly.LoadGeneratedAssembly to try to load the System.Xml.XmlSerializers assembly. This assembly is not found, so it tries to create a dynamic assembly which fails under SQL CLR.

There is a workaround for the issue, however it is pretty messy. Here are the steps for this case:
1.     Run sgen on System.Xml.dll file to generate an XmlSerializer for the XmlSchema class, like so:
C:\Windows\Microsoft.NET\Framework\v2.0.50727>sgen /t:System.Xml.Schema.XmlSchema System.Xml.Dll
2.    This will output an error message saying the assembly cannot be signed with the correct key, however a System.Xml.XmlSerializers.dll.DELETED file will be created. Rename this file to System.Xml.XmlSerializers.Dll and add it as a reference in the project. Add the following to your declaration
Imports Microsoft.Xml.Serialization.GeneratedAssembly
3.    Replace this code in GetSchemaString:

            GeneratedSchema.Write(Writer)

With:
Dim GeneratedSchemaSerializer As XmlSchemaSerializer
            GeneratedSchemaSerializer = New XmlSchemaSerializer()
            GeneratedSchemaSerializer.Serialize(Writer, GeneratedSchema)

Best regards,
Jun