none
Single element XML to JSON Array RRS feed

  • Question

  • Stuck again.

    I have a XML source that could have 1 or more items.  When it has multiple items, and I flip to json, I get the bracket as needed. When I flip XML with only 1 item, it removes the brackets

    Example:

    "packages": [

    {

      "packageid": "1",

    }

    {

     "packageid":"1",

    ]

    With 1 element I get

    "packages": {

    {

      "packageid": "1",

    }

    }

    I am flipping XML to JSON using @json(xml(Trigger_Body))

    I am using XML file coming in, then Transform XML to get to correct format needed for json, then @json(xml...)

    Any assistance would be greatly appreciated.


    Brian A. Lemaster Tecodo, Inc.

    Monday, April 3, 2017 3:16 PM

Answers

  • Yes sorry unfortunately I'm not sure the @json() conversion will provide the right types for single-item arrays in this case - we'd need something like a destination schema first which we don't yet support.  Your options are:

    1. Use the "Compose" action to generate the object by hand (manually put all the properties and arrays where they need, potentially using the @array() action

    2. Call an Azure Function or some external code that can more specifically craft a valid JSON

    It's a good suggestion that we should potentially have a "Destination Schema" for the @json() action to more accurately create the desired object.  It's not there yet but would be good to create an item or vote here:

    http://aka.ms/logicapps-wish

    Tuesday, April 4, 2017 6:27 PM
    Moderator
  • Hi,

    if you have to convert the xml in your case you have to write your own custom Xml to Json function or Compose shape and extract the Xpath Values and convert it to Json.

    How Ever if you can tweak your xml for the Records to have an attribute  json:Array='true' .

    Below Azure Function Can work. Code is like below

    public static async Task<object> Run(HttpRequestMessage req, TraceWriter log)
    {
        log.Info($"Webhook was triggered!");
    
    
    
        string JsonContent;
        try
        {
            string XmlContent = await req.Content.ReadAsStringAsync();
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(XmlContent);
            string jsonText = JsonConvert.SerializeXmlNode(doc);
            JsonContent = jsonText;
        }
        catch (Exception ex)
        {
            log.Info($" {ex.Message}");
            return req.CreateResponse(HttpStatusCode.BadRequest,
            new { error = ex.Message });
        }
    
        var response = req.CreateResponse(HttpStatusCode.OK);
        response.Content = new StringContent(JsonContent,
                             System.Text.Encoding.UTF8, "application/json");
    
        return response;
    
    }

    Example of a Source xml  and converted Json is like below

     <ns0:Root xmlns:ns0="Msdn" xmlns:json='http://james.newtonking.com/projects/json'>
    	<Header>
    		< innum>1010101</innum>
    		<indate>1999-05-31</indate>
    	</Header>
    	<Details json:Array='true'>
    		<ItemSubTot>-219.0700</ItemSubTot>
    		<Priced>-29.0700</Priced>
    		
    	</Details>
    
    </ns0:Root>


    {
      "ns0:Root": {
        "@xmlns:ns0": "Msdn",
        "Header": {
          "innum": "1010101",
          "indate": "1999-05-31"
        },
        "Details": [
          {
            "ItemSubTot": "-219.0700",
            "Priced": "-29.0700"
          }
        ]
      }
    }

    One of the Article  which explains this is like below

    http://www.newtonsoft.com/json/help/html/ConvertingJSONandXML.htm

    If you cannot change your xml and you have to write you own custom code then use the below Link. it might help.

    https://forums.asp.net/p/1064842/1538141.aspx

    Mat be these can help you in solving your Problem.

    Thanks,

    Sujith.


    Sujith


    Saturday, April 8, 2017 8:40 PM

All replies

  • Yes this is one of the hiccups with XML to JSON transformation universially.  We have an @array() function to help do this - if you pass in an array, we will return the array.  If you pass in a single object, we will return an array with single object inside.  So something like:

    @array(json(xml(triggerBody()))['packages'])

    Monday, April 3, 2017 7:22 PM
    Moderator
  • Jeff, thanks for the reply.  Here is the total solution, which I am stuck on.

    1. XML file gets posted to FTP (I can read that in with Logic App)

    2. I transform XML with BizTalk Map, to get the correct format I need.

    3. I need to then convert the entire XML to JSON

    4. I need to post to HTTP API, which expects certain elements to be arrays, even if they are single element arrays. 

    You tip helps me get the packages array, but what I am not grasping is step 3, I would need to go through the entire XML, and find all elements that need to be arrays.  If the original XML has multiple elements, it all works great. If not, I am missing the brackets for arrays in the @json(xml(Trigger_Body())) call.

    The following is the JSON output I am trying to create, with elements that may/may not be arrays in original XML. Orders, Packages, and Items.  There could be one or more of each.  @json leaves out the single array brackets.

    /// sample json output.

    {
      "orders": [
        {
          "orderNumber": "Order Number 123",
          "orderDate": "2017-03-22T10:54:32.9834604Z",
          "packages": [
            {
              "packageId": "Merchant package id",
              "shippingCost": 10.0,
              "totalValue": 12.0,
              "currency": "USD",
              "totalItems": 1,
              "totalWeight": 15.0,
              "serviceLevel": "Order Service",
              "facility": "Facility Alias",
              "parentCompany": "Parent Company Alias",
              "consignee": {
                "fullName": "Consignee Name",
                "phone": "+38212312132",
                "email": "consignee@email.com",
                "address": {
                  "addressLine1": "Address Line 1",
                  "city": "City",
                  "state": "State",
                  "country": "USA",
                  "zipCode": "1231289",
                },
               },
              "items": [
                {
                  "sku": "Item SKU",
                  "title": "Item Name",
                  "localTitle": "Item Local Name",
                  "price": 12.0,
                  "quantity": 1,
                  "grossWeight": 15.0,
                  "link": "www.something.com/item/123",
                  "fullDescription": "Full description of the item",
                  "originCountry": "USA"
                }
              ],
            }
          ]
        }
      ]
    }

    Sample XSD for Map:

    <?xml version="1.0" encoding="UTF-16"?>

    -<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://IntegrationApp.XML_Output" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://IntegrationApp.XML_Output">

    -<xs:element name="Root">
    -<xs:complexType>
    -<xs:sequence>
    -<xs:element name="orders">
    -<xs:complexType>
    -<xs:sequence>
    <xs:element name="orderNumber" type="xs:string"/>
    <xs:element name="orderDate" type="xs:string"/>
    -<xs:element name="packages" maxOccurs="unbounded">
    -<xs:complexType>
    -<xs:sequence maxOccurs="unbounded">
    <xs:element name="packageId" type="xs:string"/>
    <xs:element name="totalWeight" type="xs:decimal"/>
    <xs:element name="serviceLevel" type="xs:string"/>
    <xs:element name="facility" type="xs:string" default="ERBG"/>
    <xs:element name="parentCompany" type="xs:string"/>
    -<xs:element name="consignee">
    -<xs:complexType>
    -<xs:sequence>
    <xs:element name="fullName" type="xs:string"/>
    <xs:element name="phone" type="xs:string"/>
    <xs:element name="email" type="xs:string"/>
    -<xs:element name="address">
    -<xs:complexType>
    -<xs:sequence>
    <xs:element name="addressLine1" type="xs:string"/>
    <xs:element name="city" type="xs:string"/>
    <xs:element name="state" type="xs:string"/>
    <xs:element name="country" type="xs:string"/>
    <xs:element name="zipCode" type="xs:string"/>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    -<xs:element name="items" maxOccurs="unbounded">
    -<xs:complexType>
    -<xs:sequence maxOccurs="unbounded">
    <xs:element name="sku" type="xs:string"/>
    <xs:element name="localTitle" type="xs:string"/>
    <xs:element name="price" type="xs:decimal"/>
    <xs:element name="quantity" type="xs:integer"/>
    <xs:element name="link" type="xs:string"/>
    <xs:element name="fullDescription" type="xs:string"/>
    <xs:element name="originCountry" type="xs:string" default="USA"/>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    </xs:schema>

    At step 3 is where your suggestion would go, but I am stuck on how to find all three possible arrays, and make sure they get flipped correctly to [] json arrays, as the backend API will fail without them.

    SAMPLE XML WITH 1 ORDER AND 1 PACKAGE AND 2 ITEMS, each needing to end up as array in json.

    <?xml version="1.0" encoding="utf-8"?><Manifest xmlns="http://www.brian.com/contracts" xmlns:xsi="<Account>GUID</Account><Reference>20170319080111</Reference><Weight>Kilograms</Weight><Sender>Brian</Sender><Orders><Add">http://www.w3.org/2001/XMLSchema-instance"><Account>GUID</Account><Reference>20170319080111</Reference><Weight>Kilograms</Weight><Sender>Brian</Sender><Orders><Add Id="IB14895057739291ZX" Service="SERVICE1"><Consignee>BRIAN LEMASTER</Consignee><Address><Street>123 MAIN STREET</Street><City>DOWNTOWN</City><State>VA</State><Zip>20121</Zip><Country>USA</Country></Address><Contact><Add Kind="Email">steve@steve.com</Add></Contact><Packages><Add Id="IB14895057739291"><Weight>5.187</Weight><Items><Add SKU="207731602"><Description>A PAIR OF SOCKS</Description><Value>314.53</Value><NativeDescription>PAIR OF SOCKS EXPENSIVE</NativeDescription><Quantity>1</Quantity><URL>http://www.BRIAN.com/SOCKS.HTM</URL></Add><Add SKU="PB0110551"><Description>SOCKET ADAPTERS</Description><Value>0.01</Value><NativeDescription>NICE SOCKET ADAPTERS</NativeDescription><Quantity>1</Quantity><URL>http://www.BRIAN.com/adapters.htm</URL></Add></Items></Add></Packages></Add></Orders></Manifest>


    Brian A. Lemaster Tecodo, Inc.


    Monday, April 3, 2017 8:23 PM
  • Jeff,

    Any input would be great. I am really stuck.


    Brian A. Lemaster Tecodo, Inc.

    Tuesday, April 4, 2017 5:54 PM
  • Yes sorry unfortunately I'm not sure the @json() conversion will provide the right types for single-item arrays in this case - we'd need something like a destination schema first which we don't yet support.  Your options are:

    1. Use the "Compose" action to generate the object by hand (manually put all the properties and arrays where they need, potentially using the @array() action

    2. Call an Azure Function or some external code that can more specifically craft a valid JSON

    It's a good suggestion that we should potentially have a "Destination Schema" for the @json() action to more accurately create the desired object.  It's not there yet but would be good to create an item or vote here:

    http://aka.ms/logicapps-wish

    Tuesday, April 4, 2017 6:27 PM
    Moderator
  • I am open to Number 2. above. 

    If you could point me in correct direction that would be good. I am not a good coder at all.

    I guess I would need a function that we would pass the XML into? Then flip to Json?

    This is a real pain, as I expect some of this to work. 

    Using unbounded in the XML XSD or Map did not seem to have an effect.  This seems so simple.

    I guess I will probably need to install full BizTalk somewhere else. I need to do this same transformation for CSV, XML, and EDI/AS2 data.  If I can't get the Logic Apps to do what I need, it will need to be coded somewhere else I guess.

    Thanks again.  Lemaster@tecodo.com if you decide to ping me directly


    Brian A. Lemaster Tecodo, Inc.

    Tuesday, April 4, 2017 6:48 PM
  • Azure functions and logic app are great combination  and use them both to have proper design .

    In your case you can use compose action to contract a json from the triggered body and send it to azure functions . 

    In azure function create a class and use newtonsoft.json dynamic key word to extract the xml and iterate through xml to populate the object of your class . 

    Let us know if you still face problem.


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply

    Friday, April 7, 2017 2:54 AM
  • Hi,

    if you have to convert the xml in your case you have to write your own custom Xml to Json function or Compose shape and extract the Xpath Values and convert it to Json.

    How Ever if you can tweak your xml for the Records to have an attribute  json:Array='true' .

    Below Azure Function Can work. Code is like below

    public static async Task<object> Run(HttpRequestMessage req, TraceWriter log)
    {
        log.Info($"Webhook was triggered!");
    
    
    
        string JsonContent;
        try
        {
            string XmlContent = await req.Content.ReadAsStringAsync();
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(XmlContent);
            string jsonText = JsonConvert.SerializeXmlNode(doc);
            JsonContent = jsonText;
        }
        catch (Exception ex)
        {
            log.Info($" {ex.Message}");
            return req.CreateResponse(HttpStatusCode.BadRequest,
            new { error = ex.Message });
        }
    
        var response = req.CreateResponse(HttpStatusCode.OK);
        response.Content = new StringContent(JsonContent,
                             System.Text.Encoding.UTF8, "application/json");
    
        return response;
    
    }

    Example of a Source xml  and converted Json is like below

     <ns0:Root xmlns:ns0="Msdn" xmlns:json='http://james.newtonking.com/projects/json'>
    	<Header>
    		< innum>1010101</innum>
    		<indate>1999-05-31</indate>
    	</Header>
    	<Details json:Array='true'>
    		<ItemSubTot>-219.0700</ItemSubTot>
    		<Priced>-29.0700</Priced>
    		
    	</Details>
    
    </ns0:Root>


    {
      "ns0:Root": {
        "@xmlns:ns0": "Msdn",
        "Header": {
          "innum": "1010101",
          "indate": "1999-05-31"
        },
        "Details": [
          {
            "ItemSubTot": "-219.0700",
            "Priced": "-29.0700"
          }
        ]
      }
    }

    One of the Article  which explains this is like below

    http://www.newtonsoft.com/json/help/html/ConvertingJSONandXML.htm

    If you cannot change your xml and you have to write you own custom code then use the below Link. it might help.

    https://forums.asp.net/p/1064842/1538141.aspx

    Mat be these can help you in solving your Problem.

    Thanks,

    Sujith.


    Sujith


    Saturday, April 8, 2017 8:40 PM
  • I had the same issue after transforming an X12 document and passing it to a custom Web API. The quick solution was to handle the data in the Web API as a JSON string and manually parse it and handle whether the property received was a JObject or a JArray. 

    But I didn't like that at all. It seemed to me I was taking a step backwards by not using strongly-typed data. After reviewing the solution I found a better solution, which stayed closer to using strongly defined data types, which Web API 2 gave us. The solution was to receive that one property as a JToken instead of a list of some class. Then using a helper function to convert the JToken property and store it to a new property that will be a strongly-typed List as expected.  Then I just use the new property in my API's code instead of the original property.

    Here are the four steps to do it:

    1) Update the model class:

    1. Change the property type in the model to a JToken instead of List<{item class}>
    2. Added a new property (which is not populated by Controller) as a List<{item class}>

    Example where LineItems can have 1 or more records:

    Old:

    public class OrderData
    {
            ...{some other properties}...
    	public List<LineItem> LineItems { get; set; }
    }

    New:

    public class OrderData
    {
    	...{some other properties}...
    	public JToken LineItems { get; set; }
    	public List<LineItem> FixedLineItems { get; set; }
    }

    2) Add the following Helper method to the Controller Class:

    protected List<T> ConvertJson<T>(JToken jsonData)
    {
    	List<T> items = new List<T>();
    
    	switch (jsonData.Type) {
    		case JTokenType.Array:
    			items = jsonData.ToObject<List<T>>();
    			break;
    		case JTokenType.Object:
    			items.Add(jsonData.ToObject<T>());
    			break;
    		default:
    			throw new Exception("Unknown parameter type [" + jsonData.Type.ToString + "]!");
    	}
    
    	return items;
    }

    3) Add one line to the beginning of your API's method to populate the new field using the helper method passing in the JToken property.

    Example: 

    public HttpResponseMessage Insert([FromBody()] OrderData data) 
    {
    	data.FixedLineItems = ConvertJson<LineItem>(data.LineItems);
    	...{your code here}...
    }

    4) Now update your code to use the new property instead of the original property.  Eg. use FixedLineItems instead of LineItems.

    Now you can use your class as expected and the data is strongly-typed.

    FYI:  You may need to add, using Newtonsoft.Json.Linq; to your classes if you don't already have it.

    Hopefully, in the future, Logic Apps Transformation will preserve in the XML a tag (as suggested by Sujith) so it will automatically create the data as an array when converting to JSON.  Or maybe the Web API's Controller class can be enhanced to automatically accept a single item and convert it to an Array or List as expected instead of leaving the property uninitialized.

    Friday, July 28, 2017 5:26 PM
  • Tom - thank you very much for posting this; I have hit exactly the same problem and this may need to be the solution.

    I've been looking for some way to generate either a json:Array='true' attribute or add a processing instruction such as <?xml-multiple?> but so far have not had much luck in doing so. 

    I think you could edit the xslt by hand and add a processing instruction (https://www.w3schools.com/xml/ref_xsl_el_processing-instruction.asp).  I have not tried this yet because even if it works, every time I compile my map it will go away.  Is there a way to generate something like that?

    For now I'll implement something similar to your suggestion - thanks again for posting that.


    Thursday, September 28, 2017 8:33 PM