none
BizTalk XML to Json Object array conversion issue

    Question


  • Hi All,

    I have a requirement in BizTalk to send below json message to destination system and my source message would be an XML. The Problem here is the json content is a Json object array (starting with square bracket '[') and I am unable to transom from XML to similar kind of JSON array object. I am using newtonSoft dll , Is there a way to transform from xml or json object to below JSON Object array before sending the final output to end system?

    Expected Format:

         [{
    "ABC": {
    "ABC_ID": [{
    "lookup_key": "CODE",
    "lookup_value": "111"

                              }, {
    "lookup_key": "ID",
    "lookup_value": 222
            }],
    "TEST": "Chargeable"
            }
             },  
             {"ABC": {
    "ABC_ID": [{
    "lookup_key": "CODE",
    "lookup_value": "1111"
    }, {
    "lookup_key": "ID",
    "lookup_value": 2222
    }],
    "Test": "Chargeable",

          }
             }]

    The closest format I am able manage by adding extra root node "RootNode" is the below  format assuming the root node will be removed while executing Json Converter pipeline.

    {
      "RootNode": {
        "ABC": [{
            "ABC_ID": [{
                "lookup_key": "CODE",
                "lookup_value": "111"
              }, {
                "lookup_key": "ID",
                "lookup_value": 222
              }],
     "TEST": "Chargeable"
     },
           {
            "ABC_ID": [{
                "lookup_key": "CODE",
                "lookup_value": "1111"
              },{
                "lookup_key": "ID",
                "lookup_value": 2222
       }],
     "Test": "Chargeable"
          }
          ]
      }
    }

    Thank you!!!

    Friday, September 1, 2017 9:18 PM

All replies

  • Why aren't you using the BizTalk JSON Encoder?
    Sunday, September 3, 2017 3:55 PM
    Moderator
  • Hi Subrata,

    Can you please post your input XML and you expected JSON output?

    I suggest you use the "Insert Code Block" feature when you reply , making it easier for us to read.

    Thanks,


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    Sunday, September 3, 2017 4:29 PM
  • @John - Biztalk Encoder is not giving me the right converted Json Array object.

    @Mauricio - Thanks for the suggestion.

    Below is the XML format that right now I am trying to use. So basically If I get to know what should be the XML structure which can be converted to expected json Array object(that I highlighted above) then I can construct the XML that way which won't be challenging. But I am unable to find a way to construct the Json array object.

    <?xml version="1.0" encoding="UTF-8" ?>
    	<RootNode>
    		<ABC>
    			<ABC_ID>
    				<lookup_key>CODE</lookup_key>
    				<lookup_value>111</lookup_value>
    			</ABC_ID>
    			<ABC_ID>
    				<lookup_key>ID</lookup_key>
    				<lookup_value>222</lookup_value>
    			</ABC_ID>
    			<TEST>Chargeable</TEST>
    		</ABC>
    		<ABC>
    			<ABC_ID>
    				<lookup_key>CODE</lookup_key>
    				<lookup_value>1111</lookup_value>
    			</ABC_ID>
    			<ABC_ID>
    				<lookup_key>ID</lookup_key>
    				<lookup_value>2222</lookup_value>
    			</ABC_ID>
    			<Test>Chargeable</Test>
    		</ABC>
    	</RootNode>


    Monday, September 4, 2017 4:33 AM
  • @Mauricio- Please find the Input and expected format below.

    Input format 

    <?xml version="1.0" encoding="UTF-8" ?>
    	<RootNode>
    		<ABC>
    			<ABC_ID>
    				<lookup_key>CODE</lookup_key>
    				<lookup_value>111</lookup_value>
    			</ABC_ID>
    			<ABC_ID>
    				<lookup_key>ID</lookup_key>
    				<lookup_value>222</lookup_value>
    			</ABC_ID>
    			<TEST>Chargeable</TEST>
    		</ABC>
    		<ABC>
    			<ABC_ID>
    				<lookup_key>CODE</lookup_key>
    				<lookup_value>1111</lookup_value>
    			</ABC_ID>
    			<ABC_ID>
    				<lookup_key>ID</lookup_key>
    				<lookup_value>2222</lookup_value>
    			</ABC_ID>
    			<Test>Chargeable</Test>
    		</ABC>
    	</RootNode>

    Expected JSON format

       [{
    "ABC": {
    "ABC_ID": [{
    "lookup_key": "CODE",
    "lookup_value": "111"
    
                              }, {
    "lookup_key": "ID",
    "lookup_value": 222
            }],
    "TEST": "Chargeable"
            }
             },  
             {"ABC": {
    "ABC_ID": [{
    "lookup_key": "CODE",
    "lookup_value": "1111"
    }, {
    "lookup_key": "ID",
    "lookup_value": 2222
    }],
    "Test": "Chargeable",
    
          }
             }]

    • Proposed as answer by F.Mondelo Monday, September 4, 2017 1:55 PM
    • Unproposed as answer by F.Mondelo Monday, September 4, 2017 1:55 PM
    Monday, September 4, 2017 12:11 PM
  • Hi, 

    You can achieve this representing your JSON as a class and then Serialize it to XML or JSON. This is the classes that you need:

    [Serializable]
        public class ABC_Container
        {
            private ABC_Item[] m_ABC;
    
            public ABC_Item[] ABC
            {
                get
                {
                    return m_ABC;
                }
                set
                {
                    m_ABC = value;
                }
            }
        }
    
        [Serializable]
        public class ABC_Item
        {
            private ABC_ID_Item[] m_ABC_ID;
            private string m_TEST;
    
            public ABC_ID_Item[] ABC_ID 
            {
                get
                {
                    return m_ABC_ID;
                }
                set
                {
                    m_ABC_ID = value;
                }
            }
    
            public string TEST
            {
                get
                {
                    return m_TEST;
                }
                set
                {
                    m_TEST = value;
                }
            }
        }
    
        [Serializable]
        public class ABC_ID_Item
        {
            private string m_lookup_key;
            private int m_lookup_value;
    
            public string lookup_key
            {
                get
                {
                    return m_lookup_key;
                }
                set
                {
                    m_lookup_key = value;
                }
            }
    
            public int lookup_value
            {
                get
                {
                    return m_lookup_value;
                }
                set
                {
                    m_lookup_value = value;
                }
            }
        }

    Then I create this program:

    static void Main(string[] args)
            {
                JavaScriptSerializer serializer = new JavaScriptSerializer();
    
                ABC_Container[] container = new ABC_Container[2];
    
                container[0] = new ABC_Container();
                container[0].ABC = new ABC_Item[1];
    
                ABC_Item abc = new ABC_Item();
                abc.ABC_ID = new ABC_ID_Item[2];
                abc.ABC_ID[0] = new ABC_ID_Item();
                abc.ABC_ID[0].lookup_key = "CODE";
                abc.ABC_ID[0].lookup_value = 111;
    
                abc.ABC_ID[1] = new ABC_ID_Item();
                abc.ABC_ID[1].lookup_key = "ID";
                abc.ABC_ID[1].lookup_value = 222;
    
                abc.TEST = "Chargeable";
    
                container[0].ABC[0] = abc;            
    
                container[1] = new ABC_Container();
                container[1].ABC = new ABC_Item[1];
                container[1].ABC[0] = abc;            
    
                StringBuilder sb = new StringBuilder ();
                serializer.Serialize(container, sb);
    
                Console.WriteLine(sb.ToString());
                Console.ReadLine();
    
                XmlSerializer serializerXml = new XmlSerializer(typeof(ABC_Container[]));
    
                MemoryStream mStream = new MemoryStream();
                serializerXml.Serialize(mStream, container);
    
                XmlDocument xmlDocument = new XmlDocument();
                mStream.Position = 0;
                xmlDocument.Load(mStream);
    
                Console.WriteLine(xmlDocument.OuterXml);
                Console.ReadLine();
            }

    When it's serialized to JSON (using System.Web.Script.Serialization.JavaScriptSerializer) I get your JSON:

    [{
    	"ABC": [{
    		"ABC_ID": [{
    			"lookup_key": "CODE",
    			"lookup_value": 111
    		},
    		{
    			"lookup_key": "ID",
    			"lookup_value": 222
    		}],
    		"TEST": "Chargeable"
    	}]
    },
    {
    	"ABC": [{
    		"ABC_ID": [{
    			"lookup_key": "CODE",
    			"lookup_value": 111
    		},
    		{
    			"lookup_key": "ID",
    			"lookup_value": 222
    		}],
    		"TEST": "Chargeable"
    	}]
    }]

    When it's serialized to XML I get:

    <?xml version="1.0"?>
    <ArrayOfABC_Container xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <ABC_Container>
        <ABC="">
          <ABC_Item>
            <ABC_ID>
              <ABC_ID_Item>
                <lookup_key>CODE</lookup_key>
                <lookup_value>
                  111
                </lookup_value>
              </ABC_ID_Item>
              <ABC_ID_Item>
                <lookup_key>ID</lookup_key>
                <lookup_value="">
                  222
                </lookup_value>
              </ABC_ID_Item>
            </ABC_ID>
            <TEST>Chargeable</TEST>
          </ABC_Item>
        </ABC>
      </ABC_Container>
      <ABC_Container>
        <ABC>
          <ABC_Item>
            <ABC_ID>
              <ABC_ID_Item>
                <lookup_key="">
                  CODE
                </lookup_key>
                <lookup_value>111</lookup_value>
              </ABC_ID_Item>
              <ABC_ID_Item>
                <lookup_key="">
                  ID
                </lookup_key>
                <lookup_value>222</lookup_value>
              </ABC_ID_Item>
            </ABC_ID>
            <TEST>Chargeable</TEST>
          </ABC_Item>
        </ABC>
      </ABC_Container>
    </ArrayOfABC_Container>
    
    

    Then you can play with these three representations of your JSON: C# class, XML or JSON.

    Monday, September 4, 2017 2:00 PM
  • Hi, 

    You can achieve this representing your JSON as a class and then Serialize it to XML or JSON. This is the classes that you need:

    [Serializable]
        public class ABC_Container
        {
            private ABC_Item[] m_ABC;
    
            public ABC_Item[] ABC
            {
                get
                {
                    return m_ABC;
                }
                set
                {
                    m_ABC = value;
                }
            }
        }
    
        [Serializable]
        public class ABC_Item
        {
            private ABC_ID_Item[] m_ABC_ID;
            private string m_TEST;
    
            public ABC_ID_Item[] ABC_ID 
            {
                get
                {
                    return m_ABC_ID;
                }
                set
                {
                    m_ABC_ID = value;
                }
            }
    
            public string TEST
            {
                get
                {
                    return m_TEST;
                }
                set
                {
                    m_TEST = value;
                }
            }
        }
    
        [Serializable]
        public class ABC_ID_Item
        {
            private string m_lookup_key;
            private int m_lookup_value;
    
            public string lookup_key
            {
                get
                {
                    return m_lookup_key;
                }
                set
                {
                    m_lookup_key = value;
                }
            }
    
            public int lookup_value
            {
                get
                {
                    return m_lookup_value;
                }
                set
                {
                    m_lookup_value = value;
                }
            }
        }

    Then I create this program:

    static void Main(string[] args)
            {
                JavaScriptSerializer serializer = new JavaScriptSerializer();
    
                ABC_Container[] container = new ABC_Container[2];
    
                container[0] = new ABC_Container();
                container[0].ABC = new ABC_Item[1];
    
                ABC_Item abc = new ABC_Item();
                abc.ABC_ID = new ABC_ID_Item[2];
                abc.ABC_ID[0] = new ABC_ID_Item();
                abc.ABC_ID[0].lookup_key = "CODE";
                abc.ABC_ID[0].lookup_value = 111;
    
                abc.ABC_ID[1] = new ABC_ID_Item();
                abc.ABC_ID[1].lookup_key = "ID";
                abc.ABC_ID[1].lookup_value = 222;
    
                abc.TEST = "Chargeable";
    
                container[0].ABC[0] = abc;            
    
                container[1] = new ABC_Container();
                container[1].ABC = new ABC_Item[1];
                container[1].ABC[0] = abc;            
    
                StringBuilder sb = new StringBuilder ();
                serializer.Serialize(container, sb);
    
                Console.WriteLine(sb.ToString());
                Console.ReadLine();
    
                XmlSerializer serializerXml = new XmlSerializer(typeof(ABC_Container[]));
    
                MemoryStream mStream = new MemoryStream();
                serializerXml.Serialize(mStream, container);
    
                XmlDocument xmlDocument = new XmlDocument();
                mStream.Position = 0;
                xmlDocument.Load(mStream);
    
                Console.WriteLine(xmlDocument.OuterXml);
                Console.ReadLine();
            }

    When it's serialized to JSON (using System.Web.Script.Serialization.JavaScriptSerializer) I get your JSON:

    [{
    	"ABC": [{
    		"ABC_ID": [{
    			"lookup_key": "CODE",
    			"lookup_value": 111
    		},
    		{
    			"lookup_key": "ID",
    			"lookup_value": 222
    		}],
    		"TEST": "Chargeable"
    	}]
    },
    {
    	"ABC": [{
    		"ABC_ID": [{
    			"lookup_key": "CODE",
    			"lookup_value": 111
    		},
    		{
    			"lookup_key": "ID",
    			"lookup_value": 222
    		}],
    		"TEST": "Chargeable"
    	}]
    }]

    When it's serialized to XML I get:

    <?xml version="1.0"?>
    <ArrayOfABC_Container xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <ABC_Container>
        <ABC="">
          <ABC_Item>
            <ABC_ID>
              <ABC_ID_Item>
                <lookup_key>CODE</lookup_key>
                <lookup_value>
                  111
                </lookup_value>
              </ABC_ID_Item>
              <ABC_ID_Item>
                <lookup_key>ID</lookup_key>
                <lookup_value="">
                  222
                </lookup_value>
              </ABC_ID_Item>
            </ABC_ID>
            <TEST>Chargeable</TEST>
          </ABC_Item>
        </ABC>
      </ABC_Container>
      <ABC_Container>
        <ABC>
          <ABC_Item>
            <ABC_ID>
              <ABC_ID_Item>
                <lookup_key="">
                  CODE
                </lookup_key>
                <lookup_value>111</lookup_value>
              </ABC_ID_Item>
              <ABC_ID_Item>
                <lookup_key="">
                  ID
                </lookup_key>
                <lookup_value>222</lookup_value>
              </ABC_ID_Item>
            </ABC_ID>
            <TEST>Chargeable</TEST>
          </ABC_Item>
        </ABC>
      </ABC_Container>
    </ArrayOfABC_Container>
    

    Then you can play with these three representations of your JSON: C# class, XML or JSON.

    I fail to see how this is a BizTalk based solution.

    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    Monday, September 4, 2017 2:13 PM
  • You have several options, for example:

    • You can implement into a class library referenced by your orchestration. You pass the XML message and gets ths JSON string.
    • You can develope a custom pipeline component, that receives the XML and encodes it as JSON string.
    • ...

    With these classes, you can implement inside your BizTalk solution with the best option for you.

    Monday, September 4, 2017 2:38 PM
  • @Mondelo - Thanks , But not sure if you call  class library from orchestration and convert into Json then how would you pass over messagebox/Sendport.

    But yes  in pipeline we can do that , but this json converter class won't be generic at all . It can be used only for  specific Json structure. 

    Regards,

    Subrata

    Monday, September 4, 2017 5:09 PM
  • To pass over MessageBox and Send Port, if you use a class library you will need a send pipeline component on the output to extract only the JSON text and send to the adapter.

    To be generic at pipeline, in your pipeline component expose a property with the name of the class that serializes the content as Xml Assembler does with schemas.

    Monday, September 4, 2017 7:00 PM
  • Hi Mondelo,

    Thanks for your reply. Do you have any example of how to expose C# class name in the pipeline or if you aware of any blog which has such example.

    I have tried from my end but unable to solve it.

    Regards,

    Subrata

    Wednesday, September 6, 2017 8:17 PM
  • Hi Subrata,

    You can expose on your Custom Pipeline Component (I am supposing that you know how to develope them, if you don't check this documentation Developing Custom Pipeline Components) two String properties:

    * Full Assembly Name where the class is defined

    * Class Name

    And in your Custom Pipeline Component, load that class:

    Assembly classAssembly = Assembly.Load(fullAssemblyName);
    Type myClassType = classAssembly.GetType(className);
    

    With this myClassType variable, you can use to Serialize/Deserialize between JSON and XML.

    Hope this helps.


    Thursday, September 7, 2017 10:08 AM
  • Hi,

    The option to serialize object to JSON will work but I would prefer this serialization should be done by the destination system instead of you dealing with object object   serialization  in pipeline .

    The drawback is any  change in schema structure you need to change the object class along with the pipeline . So better option is to ask the end system if they can do serialization  at there end rather than building a complex integration solution .

     


    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

    Thursday, September 7, 2017 2:38 PM
  • Thanks Mondelo. 

    Hi Abhishek,

    I agree its going to be complex integration and schema change is going to lead to object change, but again any schema change from end system also need a change in Biztalk change.

    There are couple of constraints for my project,

    1) End system can't change anything since the API has been consumed by other applications. 

    2) End system expect the JSON content to be in URL Encoded (I believe they process the payload along with the POST URL), so after converting to JSON format in biztalk I  need to convert into URL encoded before sending to end system.  Since the total number of payload record is going to be 500 to 1000 so I am not considering attaching the payload content in The URL itself(with the help of variable mapping) , instead I am sending as body payload. 

    Thursday, September 7, 2017 3:24 PM
  • But you only have to generate the correct JSON payload on the pipeline component, then you use a WCF-WebHttp port.

    See Sending JSON Request via WCF-WebHttp using Http POST method as example.

    Thursday, September 7, 2017 8:34 PM