Geek Answers Handbook

Removing XML deserialization in JSON without using the XmlDocument.Loadxml () function

I have a slightly unique problem. I register a dll as an assembly inside a SQL Server database that takes a SQLXml variable along with two lines and converts the data to JSON format.

For reference, here is a 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 it was some other type of application. I usually followed the answer here and did something similar to:

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

However, since I use SQLClr, there are certain rules for the road. One of them is that .Load() and any other inherited method cannot be used. I think .Net infrastructure has best said:

System.InvalidOperationException: Unable to load dynamically generated serialization. In some hosted environments, side loading functionality is limited; consider using a pre-generated serializer. For more information, see Internal Exception. ---> System.IO.FileLoadException:
LoadFrom (), LoadFile (), Load (byte []) and LoadModule () were disabled by the host.

I do not own SqlClr in any way, but if I understand this blog correctly, it is caused by SqlCLR rules that do not allow .Load () and inherited methods without signing and having a strong name. My DLL and the third-party DLLs that I use do not have a strong name, and I cannot rebuild and sign them myself. That way, it leaves me stuck in trying to complete this task without using the load (if someone does not know another way, this can be done)

My only solution I could come up with is a very ugly while loop that doesn't work properly, I get "Jayrock.Json.JsonException: the value of the JSON member inside the JSON object must precede its member name". 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 that I cannot use .Load() and the while loop is a mess for debugging, what would be the best approach here? Another approach that is commonly discussed is deserialization in Object with the appropriate variables, but I have pretty big XML coming out of SQL Server. My loop is an attempt at dynamic programming, as there are ~ 200 fields that are pulled to make this XML.

Note. I use Jayrock and work in the .NET Framework 2.0. I can’t change the version of the frame at the moment.

+7
source share
2 answers

Code for JayRock

Your code throws 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."); }

Containing a call from this code:

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

The only time your code calls a method that calls EnsureMemberOnObjectBracket 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. Perhaps you could try or catch or refine your code here.

0
source

I hope this applies to you, but will not know if you do not try. From here :

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

UNSAFE code resolution is intended for situations in which the assembly cannot be reliably secure or require additional access to limited resources, such as the Microsoft Win32 API.

I believe that one of these two is what you need, possibly EXTERNAL_ACCESS . More details:

We recommend creating an asymmetric key from the assembly file in the main database. An entry mapped to this asymmetric key must then be created, and the login must be provided by EXTERNAL ACCESS OF THE ASSEMBLY or UNSAFE ASSEMBLY.

Note. . You must create a new login to communicate with the asymmetric key. This login is used only for granting permissions; it should not be user-related or used in the application.

And the corresponding code (I have 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 assume that you also want to create a serialization assembly in your project.

This circumvents your question in reality, but it is really the preferred way to allow the loading of another DLL so that you can write the correct code.

+1
source

More articles:


All Articles