XML to JSON forcing XMLNode to be an Array

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;

Comments

  • sunmorningindiasunmorningindia Member Posts: 65
    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_ReinkingRemco_Reinking Member Posts: 74
    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.silvanuno.silva Member Posts: 29
    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
    6fuqojqj78w3.png

    But when I convert it to json I get

    l2fc99mhg0dy.png

    How can I get rid of that null?

    Thanks in advance,
    NS
    ===============
    Nuno Silva
Sign In or Register to comment.