XML to JSON forcing XMLNode to be an Array

Remco_Reinking

When using XMLPorts and NewtonSoft to create a json message where we have nested nodes these should be converted to a json array. But when such a node only contains one "record" it will not convert to a json array.
How to get this working is explained at the newtonsoft website (https://newtonsoft.com/json/help/html/ConvertXmlToJsonForceArray.htm)
But doing so is not possible only using a xmlport.
I could not find any example to accomplish this, so here it is.

Where

TreatAsArray := '//role';

when we would have the same sample as on the newtonsoft website.

[TryFunction] ExportXmlPort2Json(ThisRecAsVar : Variant;XMLPortNo : Integer;VAR JsonResult : DotNet "System.String")
begin
  TempBlob.Blob.CREATEOUTSTREAM( OutStr);
  XMLPORT.EXPORT( XMLPortNo, OutStr, ThisRecAsVar);
  ConvertXMLToJson(TempBlob, JsonResult);
end;

function ConvertXMLToJson(VAR TempBlob : TEMPORARY Record TempBlob;VAR Json : DotNet "System.String")
begin
  TempBlob.Blob.CREATEINSTREAM(InStr);
  XmlDocument := XmlDocument.XmlDocument;
  XmlDocument.Load(InStr);
  IF TreatAsArray <> '' THEN
  BEGIN
    XmlAtt := XmlDocument.CreateAttribute( 'json', 'Array','http://james.newtonking.com/projects/json');
    XmlAtt.InnerText := 'true';
    WHILE STRPOS(TreatAsArray,';') >0 DO // to allow multiple nodes to be marked as an array.
    BEGIN
      ArrayNode := COPYSTR( TreatAsArray, 1, STRPOS(TreatAsArray,';')-1);
      TreatAsArray := COPYSTR( TreatAsArray, STRPOS(TreatAsArray,';')+1, STRLEN(TreatAsArray));
      XMLNode := XmlDocument.SelectSingleNode( ArrayNode);
      XMLNode.Attributes.Append( XmlAtt);
    END;
    ArrayNode := TreatAsArray;
    XMLNode := XmlDocument.SelectSingleNode( ArrayNode); // xpath notation 
    XMLNode.Attributes.Append( XmlAtt);
  END;
  Json := JSONConvert.SerializeXmlNode(XmlDocument.DocumentElement,JSONFormatting.Indented,TRUE);
end;

sunmorningindia

Hi Remco,

I handled similar situation earlier, may be my idea help you. I don't have example ready with me.
Create extra Node with same name where source as Text in XMLport. In JSON this will create Array. In JSON if you see extra character that you wist to remove then use something like this:
// Special handling
DotNetString := DotNetString.Replace('},""]}}}','}]}}}');

Do let us know if this is helpful?

Remco_Reinking

Hi sunmorningindia,
there was no question in my post, just a small piece of code to share. I wanted to have a generic solution, where the XMLPort is coming from a setup table, together with the TreatAsArray variable.
Doing so allows me to have the XMLPort have the same elements as needed in the json structure.

nuno.silva

sunmorningindia wrote: »

Hi Remco,

I handled similar situation earlier, may be my idea help you. I don't have example ready with me.
Create extra Node with same name where source as Text in XMLport. In JSON this will create Array. In JSON if you see extra character that you wist to remove then use something like this:
// Special handling
DotNetString := DotNetString.Replace('},""]}}}','}]}}}');

Do let us know if this is helpful?

HI, if I use this solution I get always a null at the beginning of the list.
I have this XMLPort


But when I convert it to json I get



How can I get rid of that null?

Thanks in advance,
NS