Deserializing XML into JSON without using XmlDocument.Loadxml() function

Question

I have a bit of a unique problem. I am registering a dll as an assembly inside of a SQL Server database that takes in an SQLXml variable, along with two strings, and serializes the data into JSON format.

For reference, here is the method call:

[SqlProcedure]
public static void Receipt(SqlString initiatorPassword,
                           SqlString initiatorId,
                           SqlXml XMLOut,
                           out SqlString strMessge)

I would use Newtonsoft.Json or Jayrock for this application if this was any other type of app. Normally I would follow the answer given here and do something similar to:

XmlReader r = (XmlReader)XmlOut.CreateReader();
XmlDocument doc = new XmlDocument();
doc.load(r);

However, since I am using SQLClr, there are certain rules of the road. One of which is that .Load() and any other inherited method can't be used. I think the .Net framework said it best:

System.InvalidOperationException: 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. ---> System.IO.FileLoadException:
LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.

I am not fluent in SqlClr by any means, but if I am understanding this blog correctly, this is caused by SqlCLR's rules not allowing .Load() and inherited methods without being signed for and having a strong name. My DLL and the 3rd party DLLs I'm using do not have a strong name nor can I rebuild and sign them myself. So, this leaves me stuck with attempting to complete this task without using load (Unless someone knows another way this can be done)

My only solution I could come up with is a very ugly while loop that isn't working properly, I have been getting a "Jayrock.Json.JsonException: A JSON member value inside a JSON object must be preceded by its member name" exception. Here is the while loop I wrote (not my best code, I know):

 int lastdepth = -1;
 Boolean objend = true;
 Boolean wt = false;
//Write Member/Object statements for the header omitted
JsonWriter w = new JsonTextWriter()
 while (m.Read())
                {
                    if ((lastdepth == -1) && (m.IsStartElement()))
                    {//Checking for root element
                        lastdepth = 0;
                    }
                    if ((m.IsStartElement()) && (lastdepth != -1))
                    {//Checking for Start element ( <html> )
                        w.WriteMember(m.Name);
                        if (objend)
                        { //Check if element is new Parent Node, if so, write start object
                            w.WriteStartObject();
                            objend = false;
                        }
                    }
                    if (m.NodeType == XmlNodeType.Text)
                    { //Writes text here.  NOTE: m.Depth > lastdepth here!!!!!!!
                        w.WriteString(m.Value);
                        wt = true;
                    }
                    if (m.NodeType == XmlNodeType.Whitespace) //If whitespace, keep on truckin
                    { m.Skip(); }
                    if ((m.NodeType == XmlNodeType.EndElement) && (wt == false) && (lastdepth > m.Depth))
                    {//End element that ends a series of "Child" nodes
                        w.WriteEndObject();
                        objend = true;
                    }
                    if ((m.NodeType == XmlNodeType.EndElement) && (wt == true))//Standard end of an el
                    { wt = false; }
                    lastdepth = m.Depth;
                }
                w.WriteEndObject();
                jout = w.ToString();
}

My question is, since I can't use .load() and my while loop is a mess to debug, what would be the best approach here? The other approach commonly discussed is deserialization into an Object with matching variables but I have a rather large XML coming out of SQL Server. My loop is an attempt at dynamic programming since there are ~200 fields that are being pulled to make this XML.

Note: I am using Jayrock and working in .Net Framework 2.0. I can not change the framework version at this time.

AFAIK `XmlDocument.LoadXml()` doesn't need to lead dynamic assemblies. Are you sure you're not trying to use XML serialization instead? Could you post the stack trace of the exception? — svick, Jun 21 '12 at 22:06

score 1 · Answer 1 · answered Jun 21 '12 at 21:14

I'm hoping this is applicable to you, but won't know unless you try. From here:

EXTERNAL_ACCESS addresses scenarios in which the code needs to access resources outside the server, such as files, network, registry, and environment variables. Whenever the server accesses an external resource, it impersonates the security context of the user calling the managed code.

UNSAFE code permission is for those situations in which an assembly is not verifiably safe or requires additional access to restricted resources, such as the Microsoft Win32 API.

I believe one of these two is what you need, probably EXTERNAL_ACCESS. More:

We recommend that an asymmetric key be created from the assembly file in the master database. A login mapped to this asymmetric key must then be created, and the login must be granted EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission.

Note: You must create a new login to associate with the asymmetric key. This login is only used to grant permissions; it does not have to be associated with a user, or used within the application.

And the relevant code (I've used this successfully in the past):

USE master; 
GO  

CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:\MyDBApp\SQLCLRTest.dll'   
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey   
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin; 
GO

I imagine you also want Generate serialization assembly turned on in your project.

This sidesteps your question in reality, but this really is the preferred way to allow other DLL's to be successfully loaded so you can write the correct code.

AH! I wish I had seen this before I changed my entire program because of this error. Thank you very much, I'm sure I'll have to use this soon! — ScruffMcGruff, Jun 22 '12 at 18:16

score 0 · Accepted Answer · answered Jun 21 '12 at 21:58

Code For JayRock

Your code is throwing an exception:

A JSON member value inside a JSON object must be preceded by its member name.

This exception comes from the method:

    private void EnsureMemberOnObjectBracket() 
    {
        if (_state.Bracket == JsonWriterBracket.Object)
            throw new JsonException("A JSON member value inside a JSON 
             object must be preceded by its member name.");
    }

The containing call from that code is from:

    public sealed override void WriteString(string value)
    {
        if (Depth == 0)
        {
            WriteStartArray(); WriteString(value); WriteEndArray();
        }
        else
        {
            EnsureMemberOnObjectBracket();
            WriteStringImpl(value);
            OnValueWritten();
        }
    }

The only time that your code calls a method which calls EnsureMemberOnObjectBracket is from one place:

if (m.NodeType == XmlNodeType.Text)
{ //Writes text here.  NOTE: m.Depth > lastdepth here!!!!!!!
 w.WriteString(m.Value);
 wt = true;
}

This means that there is an error here. Perhaps you could do some try/catch, or refinement of your code here.

Deserializing XML into JSON without using XmlDocument.Loadxml() function

2 Answers2