ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker SupportConvert XML to JSON

Post new topicReply to topic Goto page Previous  1, 2
Convert XML to JSON View previous topic :: View next topic
Author Message
eammnsh
PostPosted: Wed Jan 04, 2017 2:18 pm Post subject: ESQL JSON-XML Array Handling Procedures Reply with quote

Apprentice

Joined: 22 Aug 2016
Posts: 29

I know this is an old post, but check the below procedures, it will help you
to convert from JSON to XML and vice versa with handling the JSON Array Type:

From JSON to XML, first convert JSON to XML, then let the procedures fix the JSON arrays created in XML:

Code:

CREATE PROCEDURE createXMLArrays(IN root REFERENCE )
        BEGIN
                -- track the start and end of 'arrays' ( sequences of same-named siblings )
                DECLARE firstArrayElementRef REFERENCE TO root;
                DECLARE siblingRef REFERENCE TO root;
                DECLARE arraySize INTEGER 0;

                WHILE LASTMOVE(siblingRef) DO
                       
                        -- Process any child elements first
                        DECLARE firstChildRef REFERENCE TO siblingRef;
                        MOVE firstChildRef FIRSTCHILD;
                        IF LASTMOVE(firstChildRef) THEN
                                CALL createxmlArrays(firstChildRef);
                        END IF;

                        -- IF this sibling's name is different from the previous one.
                        IF FIELDNAME(siblingRef) = FIELDNAME(firstArrayElementRef) THEN
                                SET arraySize = arraySize + 1;
                        ELSE
                                -- IF there was a sequence of two or more siblings with the same name
                                IF arraySize > 1 THEN
                                        CALL createXMLArray(firstArrayElementRef, arraySize);
                                END IF;                         

                                -- start scanning for a new array
                                MOVE firstArrayElementRef TO siblingRef;
                                SET arraySize = 1;
                        END IF;
                       
                        MOVE siblingRef NEXTSIBLING;
                END WHILE;
               
                IF arraySize > 1 THEN
                        CALL createXMLArray(firstArrayElementRef, arraySize);
                END IF;
        END;

        CREATE PROCEDURE createXMLArray(IN firstArrayElementRef REFERENCE, IN arraySize INTEGER) BEGIN
                -- Create a parent element for the array
                DECLARE arrayParentRef REFERENCE TO firstArrayElementRef;
                DECLARE arrayParentExtraRef1 REFERENCE TO firstArrayElementRef;
                MOVE arrayParentExtraRef1 PARENT;
                DECLARE arrayParentExtraRefT REFERENCE TO arrayParentExtraRef1;
                 DECLARE arrayParentExtraRefTcON REFERENCE TO arrayParentExtraRef1;       
                -- Make the array members children of the new parent element
                DECLARE pos INTEGER 1;
                WHILE pos < arraySize DO
                   CREATE NEXTSIBLING OF arrayParentExtraRef1
                        AS arrayParentExtraRefT
                        NAME FIELDNAME(arrayParentExtraRef1);
                       
                        DECLARE tempRef REFERENCE TO firstArrayElementRef;
                        -- advance the reference. This should never fail because we have
                        -- already walked all of these siblings to discover the array.
                        MOVE firstArrayElementRef NEXTSIBLING;
                  MOVE arrayParentExtraRef1 NEXTSIBLING;
                  
                        DETACH tempRef;
                        --ATTACH tempRef.Item TO arrayParentExtraRefT AS LASTCHILD;
                        SET arrayParentExtraRefT = tempRef;
                        SET pos = pos + 1;
                END WHILE;
                SET arrayParentExtraRefTcON = firstArrayElementRef;
                DETACH arrayParentExtraRefTcON;
                ATTACH arrayParentExtraRefTcON TO arrayParentExtraRefT AS NEXTSIBLING;
        END;


From XML to JSON

Code:

 CREATE PROCEDURE createJSONArrays(IN root REFERENCE )
        BEGIN
                -- track the start and end of 'arrays' ( sequences of same-named siblings )
                DECLARE firstArrayElementRef REFERENCE TO root;
                DECLARE siblingRef REFERENCE TO root;
                DECLARE arraySize INTEGER 0;

                WHILE LASTMOVE(siblingRef) DO
                       
                        -- Process any child elements first
                        DECLARE firstChildRef REFERENCE TO siblingRef;
                        MOVE firstChildRef FIRSTCHILD;
                        IF LASTMOVE(firstChildRef) THEN
                                CALL createJSONArrays(firstChildRef);
                        END IF;

                        -- IF this sibling's name is different from the previous one.
                        IF FIELDNAME(siblingRef) = FIELDNAME(firstArrayElementRef) THEN
                                SET arraySize = arraySize + 1;
                        ELSE
                                -- IF there was a sequence of two or more siblings with the same name
                                IF arraySize > 1 THEN
                                        CALL createJSONArray(firstArrayElementRef, arraySize);
                                END IF;                         

                                -- start scanning for a new array
                                MOVE firstArrayElementRef TO siblingRef;
                                SET arraySize = 1;
                        END IF;
                       
                        MOVE siblingRef NEXTSIBLING;
                END WHILE;
               
                IF arraySize > 1 THEN
                        CALL createJSONArray(firstArrayElementRef, arraySize);
                END IF;
        END;

        CREATE PROCEDURE createJSONArray(IN firstArrayElementRef REFERENCE, IN arraySize INTEGER) BEGIN
                -- Create a parent element for the array
                DECLARE arrayParentRef REFERENCE TO firstArrayElementRef;
                CREATE PREVIOUSSIBLING OF firstArrayElementRef
                        AS arrayParentRef
                        TYPE JSON.Array
                        NAMESPACE FIELDNAMESPACE(firstArrayElementRef)
                        NAME FIELDNAME(firstArrayElementRef);
                       
                -- Make the array members children of the new parent element
                DECLARE pos INTEGER 1;
                WHILE pos <= arraySize DO
                        DECLARE tempRef REFERENCE TO firstArrayElementRef;
                        -- advance the reference. This should never fail because we have
                        -- already walked all of these siblings to discover the array.
                        MOVE firstArrayElementRef NEXTSIBLING;

                        DETACH tempRef;
                        ATTACH tempRef TO arrayParentRef AS LASTCHILD;
                       
                        SET pos = pos + 1;
                END WHILE;
        END;

_________________
WMB 7 and 8 IIB 9 and 10
MQ 7, 7.5, and 8
Since 2012
Back to top
View user's profile Send private message
timber
PostPosted: Thu Jan 05, 2017 3:35 am Post subject: Reply with quote

Shaman

Joined: 25 Aug 2015
Posts: 704

Your XML to JSON solution is not 100% reliable. When the XML array only contains one member you will output a JSON object. When the array contains two or more members you will output a JSON array. That will often trip up the receiver.
Back to top
View user's profile Send private message
Ravinder
PostPosted: Tue Nov 14, 2017 11:10 pm Post subject: Convert XML to JSON Reply with quote

Newbie

Joined: 14 Nov 2017
Posts: 2

timber wrote:
Your XML to JSON solution is not 100% reliable. When the XML array only contains one member you will output a JSON object. When the array contains two or more members you will output a JSON array. That will often trip up the receiver.



Hi Timber,

I agree with you , i am facing the same issue when i have used the same code. Please suggest how we can create Json array in case of single record(member) also.
Back to top
View user's profile Send private message
timber
PostPosted: Wed Nov 15, 2017 1:56 am Post subject: Reply with quote

Shaman

Joined: 25 Aug 2015
Posts: 704

It will be more efficient if you explain what you think the options are. Then I'll happily comment. I've said a lot on this subject before - you can probably find my comments using the Search button.
Back to top
View user's profile Send private message
Ravinder
PostPosted: Wed Nov 15, 2017 11:17 pm Post subject: Reply with quote

Newbie

Joined: 14 Nov 2017
Posts: 2

timber wrote:
It will be more efficient if you explain what you think the options are. Then I'll happily comment. I've said a lot on this subject before - you can probably find my comments using the Search button.


Ok Let me explain my scenario.

I have one service which will give me xml response and after getting the response i should convert it into json array.

So if the service is giving me the xml response with multiple records the the above code is forming the json array in proper format.

But if the service is giving the response in xml with single record then the above code is not able to form a json array, which is not able to parse by the adaptor(frontend).

so how we can handle this case in esql?
Back to top
View user's profile Send private message
timber
PostPosted: Thu Nov 16, 2017 1:07 am Post subject: Reply with quote

Shaman

Joined: 25 Aug 2015
Posts: 704

Thanks - but I know that already. Have you thought about possible solutions to this?

How could this problem be solved? What extra information would you need if you want to generate a JSON array for a single tag?
Back to top
View user's profile Send private message
souciance
PostPosted: Fri Nov 17, 2017 4:05 pm Post subject: Reply with quote

Voyager

Joined: 29 Jun 2010
Posts: 83

Why not use jackson in java to do the conversion?
Back to top
View user's profile Send private message
timber
PostPosted: Fri Nov 17, 2017 4:09 pm Post subject: Reply with quote

Shaman

Joined: 25 Aug 2015
Posts: 704

Obvious question: does Jackson solve the OP's problem? If so, how?
Back to top
View user's profile Send private message
souciance
PostPosted: Wed Nov 22, 2017 1:01 pm Post subject: Reply with quote

Voyager

Joined: 29 Jun 2010
Posts: 83

timber wrote:
Obvious question: does Jackson solve the OP's problem? If so, how?


OP wanted hint for automatically converted XML to JSON. The Jackson library for java has functions for this and you have far more control for various configurations than you do in esql. At least it can be more fine tuned.
Back to top
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Goto page Previous  1, 2 Page 2 of 2

MQSeries.net Forum IndexWebSphere Message Broker SupportConvert XML to JSON
Jump to:



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.