Good morning everyone, 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. Thank you in advance!
Note: I am using Jayrock and working in .Net Framework 2.0. I can not change the framework version at this time.
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.