Tutorial : Consuming RESTful Resources (XML & JSON) in Zoho Creator
This post is in response to this Zoho Creator Forum Post
Using Zoho Creator's XML parsing capabilites.. Sounds daunting but it really isn't. Here's a small example of how you get your file, then turn it into an XMLLIST which then acts almost exactly like a regular list in Zoho Creator.
Let's walk through one..
Now this will be a getURL() example...
1. Create your REST URL call. This is the URL the getURL() goes OUT TO to retrieve the XML... Here's an example...
url = "http://mywebservice.com/api/dataform?param1=¶m2=¶m3=";
2. Now, we create a map variable to hold our response from the getURL() function. The false value tells the getURL to return a detailed response. THIS WE WANT for parsing XML.
mapVariable = getUrl(url,false);
3. Now that we have our response in the mapvar, we need to grab the "string" response that came back when we called the getUrl() function. We do this by "getting" the "responseText" key. This is exactly the same way you grab a key out of other maps.
stringResponse = mapvar.get("responseText");
4. Ok, now we have the whole XML response saved in the "stringResponse" variable. But at this point the XML isn't really actually XML it's actually a long "string" value. (Note: if you run an "info" log message on your stringResponse variable right now. YOU WON'T see the XML elements and you might think you did something wrong. YOU DIDN'T ZC doesn't show the XML markup in log messages unless you convert to XML then back to string. Ok, that was a TMI, but I just thought I would let you know.)
5. Ok let's convert that "string" value of stringResponse to XML!
xmlResponse = stringResponse.toXML();
6. Now, here's the hardest concept to understand I think for new users. Now that we have the REAL XML we need to message it a little bit so that we can get it in a format that's more usable in Zoho Creator.
What we need to do is "target"/put our focus on the XML nodes that we want to use in our data. Basically, we want to POINT to the nodes of the records we want to create a list out of..Let's take a look at some examples....
<result>
<data>
<row></row>
<row></row>
<row></row>
</data>
</result>
So in the above example the <rows> are what we want to import into Zoho Creator. So let's point our XPATH to that node "address".
result/data/row
Ok, let's do another example.. What if you just wanted to get the data OF THE FIRST <row>. Let's say our xml looked like this....
<result>
<data>
<row>
<first_name>Rick</first_name>
<last_name>James</last_name>
<email>rjames@imrickjames.com</email>
</row>
<row></row>
<row></row>
</data>
</result>
In the above example we would just want to make our xpath.....
result/data/row[1]
this would xpath would return only the nodes inside <row> one.
7. Ok, once you have targeted the data nodes that you want to create a list out of, let's shorten our XML to reflect the desired path..
startPath = xmlResponse.executeXPath("result/data/row[1]");
For the rest of this example. Let's just go with all the <row>'s and use this XML... . so again our xpath for our startPath URL would be result/data/row
<result>
<data>
<row>
<first_name>Rick</first_name>
<last_name>James</last_name>
<email>rjames@imrickjames.com</email>
</row>
<row>
<first_name>Charlie</first_name>
<last_name>Murphy</last_name>
<email>cmurphy@charliemurphy.com</email>
</row>
<row>
<first_name>Stephen</first_name>
<last_name>Baldwin</last_name>
<email>sbaldwin@realitytv.com</email>
</row>
</data>
</result>
8. Ok, now let's convert the XML to an XMLlist! Converting your data to a Zoho Creator XMLList() let's you ITERATE/LOOP through your desired XML nodes USING the built in "for each element" functionality already used for other lists.
xmllist = startPath.toXmlList();
9. Now we have our list! We can do all sorts of great wonderous things once you have your XML data in this format.
Let's create "for each element" loop
for each r in xmllist
{
}
10. Inside our loop we have to EXECUTE the xpath for each row's child nodes so that we can get the data out of it.
Here's how...
for each r in xmllist
{
firstName = r.executeXPath(''/row/first_name/text()');
lastName = r.executeXPath(''/row/last_name/text()');
email = r.executeXPath(''/row/email/text()');
}
11. Notice that my Xpath's in my executeXpath() functions START WITH the node we LEFT OFF of in step 7 when we targeted our XML node!
12. Ok, so you have your data in the XML list and you know how to get the data into variables.. What else can you do?
Example 1. check to see if these email addresses are already in your Zoho Creator app form and IF THEY AREN'T in the desired form then INSERT that xml row into the desired Zoho form!
get all the email addresses....
getForm = form1[email != ""];
emailList = getForm.email.getAll();
for each r in xmllist
{
firstName = r.executeXPath(''/row/first_name/text()');
lastName = r.executeXPath(''/row/last_name/text()');
email = r.executeXPath(''/row/email/text()');
if(!emailList.contains(email))
{
insert into form1
[
Added_User = zoho.loginuser
first_n ame = first_name
last_name = last_name
email = email
]
}
}
I hope this helps all of you that want to use this functionality but have struggled to wrap your head around it. I know it took me a little while to get used to.
Also, there are many other great things you can do with the Xpath functionalty. LIKE....
1. create a for each loop inside a loop
2. create node agnostic xpaths
3. MY FAVORITE stop using Xpath completely on XMLlists by converting your xml_list to a "list map"/associative array. See this post.
