Help with parsing rest json response to XDS

Hi,

Please find the below response from servicenow rest end point and help to parse it as nds attributes.

<nds dtdversion="3.5" ndsversion="8.x">
<source>
<product build="20170130_0359" instance="Rest-ServicenowDW" version="1.0.0.1">Identity Manager REST Driver</product>
<contact>NetIQ Corporation.</contact>
</source>
<input>
<driver-operation-data class-name="" command="poll" event-id="Rest-ServicenowDW##119659145##0">
<response>
{"result":[{"banner_image_light":"","country":"DK","parent":{"display_value":"","link":"http://servicenow-test.BBC.cc/api/now/table/core_company/true"},"notes":"","stock_symbol":"","u_logopath":"","discount":"","u_kundenr":"1015425","sys_updated_on":"07.05.2019 04:47:12","apple_icon":"","sys_updated_by":"BBC_POB","u_customer_alias":"","fiscal_year":"","sso_source":"","sys_created_on":"06.10.2016 13:18:52","contact":"","sys_domain":{"display_value":"TOP/MSP/BBC","link":"http://servicenow-test.BBC.dk/api/now/table/domain/4c346138a44f12005821241c95d66248"},"u_problem_coordinator":{"display_value":"PROBLEM","link":"http://servicenow-test.BBC.dk/api/now/table/sys_user_group/b4dab65fff02220028765dff7fa4adc7"},"stock_price":"","banner_image":"","state":"","sys_created_by":"RestMQUser","longitude":"","zip":"8500","profits":"kr 0,00","active":"true","sys_domain_path":"!!!/!!#/!!$/","fax_phone":"","phone":"86326632","banner_text":"","name":"Børnehaven Regnbuen","primary":"false","city":"Grenaa","latitude":"","u_company_code":"","sys_class_name":"Account","manufacturer":"false","sys_id":"00067ea5ff9a220072816dff7fa4ad17","market_cap":"kr 0,00","num_employees":"","rank_tier":null,"street":"Lupinskrænten 6","vendor":"false","lat_long_error":"","u_ssoidentifier":"","vendor_type":"","u_ean_number":"","revenue_per_year":"kr 0,00","website":"","publicly_traded":"false","sys_mod_count":"5","u_email_domain":"","sys_tags":"","vendor_manager":"","customer":"false"}]}
</response>
</driver-operation-data>
</input>
</nds>

Thanks

Siva ram T

  • First things firsts, find a JSON Formatting web site you like and format it so it is readable:

     

    { "result":[ { "banner_image_light":"", "country":"DK", "parent":{ "display_value":"", "link":"<a href="http://servicenow-test.BBC.cc/api/now/table/core_company/true" target="_blank">http://servicenow-test.BBC.cc/api/now/table/core_company/true</a>" }, "notes":"", "stock_symbol":"", "u_logopath":"", "discount":"", "u_kundenr":"1015425", "sys_updated_on":"07.05.2019 04:47:12", "apple_icon":"", "sys_updated_by":"BBC_POB", "u_customer_alias":"", "fiscal_year":"", "sso_source":"", "sys_created_on":"06.10.2016 13:18:52", "contact":"", "sys_domain":{ "display_value":"TOP/MSP/BBC", "link":"<a href="http://servicenow-test.BBC.dk/api/now/table/domain/4c346138a44f12005821241c95d66248" target="_blank">http://servicenow-test.BBC.dk/api/now/table/domain/4c346138a44f12005821241c95d66248</a>" }, "u_problem_coordinator":{ "display_value":"PROBLEM", "link":"<a href="http://servicenow-test.BBC.dk/api/now/table/sys_user_group/b4dab65fff02220028765dff7fa4adc7" target="_blank">http://servicenow-test.BBC.dk/api/now/table/sys_user_group/b4dab65fff02220028765dff7fa4adc7</a>" }, "stock_price":"", "banner_image":"", "state":"", "sys_created_by":"RestMQUser", "longitude":"", "zip":"8500", "profits":"kr 0,00", "active":"true", "sys_domain_path":"!!!/!!#/!!$/", "fax_phone":"", "phone":"86326632", "banner_text":"", "name":"Børnehaven Regnbuen", "primary":"false", "city":"Grenaa", "latitude":"", "u_company_code":"", "sys_class_name":"Account", "manufacturer":"false", "sys_id":"00067ea5ff9a220072816dff7fa4ad17", "market_cap":"kr 0,00", "num_employees":"", "rank_tier":null, "street":"Lupinskrænten 6", "vendor":"false", "lat_long_error":"", "u_ssoidentifier":"", "vendor_type":"", "u_ean_number":"", "revenue_per_year":"kr 0,00", "website":"", "publicly_traded":"false", "sys_mod_count":"5", "u_email_domain":"", "sys_tags":"", "vendor_manager":"", "customer":"false" } ] }

     

     

    Now you can start thinking about how to parse it.  That does not look like the format the built in Java classes in the REST driver can convert.  But you can do it in ECMA.

    So get the result into a string variable.

    set local variable DATA as XPATH of driver-operation-data/response/text()

    Then you can do an ECMA expression of something like:

    Set local variable NAME as XPATH of es:(JSON.parse($DATA).result.name

     

    I think that should work.

    Then you could loop over the results or over a set of attrs you want, and get variables for each, or you could use the XML attribute/text tokens to build XML after it innto a instance or modify event document.

     

    (Note: I am being wrned I have invalid HTML in here, so if formatting is all screwed up, well drat then).

  • Thanks for the quick response. I applied the same and got the result like below. How to get the values from java object
    [07/05/19 14:42:11.908]:Rest-SN PT:{"result":[{"banner_image_light":"","country":"DK","parent":{"display_value":"","link":"http://servicenow-test.BBC.dk/api/now/table/core_company/true"},"notes":"","stock_symbol":"","u_logopath":"","discount":"","u_kundenr":"1015425","sys_updated_on":"07.05.2019 04:47:12","apple_icon":"","sys_updated_by":"BBC_POB","u_customer_alias":"","fiscal_year":"","sso_source":"","sys_created_on":"06.10.2016 13:18:52","contact":"","sys_domain":{"display_value":"TOP/MSP/BBC","link":"http://servicenow-test.BBC.dk/api/now/table/domain/4c346138a44f12005821241c95d66248"},"u_problem_coordinator":{"display_value":"PROBLEM","link":"http://servicenow-test.BBC.dk/api/now/table/sys_user_group/b4dab65fff02220028765dff7fa4adc7"},"stock_price":"","banner_image":"","state":"","sys_created_by":"RestMQUser","longitude":"","zip":"8500","profits":"kr 0,00","active":"true","sys_domain_path":"!!!/!!#/!!$/","fax_phone":"","phone":"86326632","banner_text":"","name":"Børnehaven Regnbuen","primary":"false","city":"Grenaa","latitude":"","u_company_code":"","sys_class_name":"Account","manufacturer":"false","sys_id":"00067ea5ff9a220072816dff7fa4ad17","market_cap":"kr 0,00","num_employees":"","rank_tier":null,"street":"Lupinskrænten 6","vendor":"false","lat_long_error":"","u_ssoidentifier":"","vendor_type":"","u_ean_number":"","revenue_per_year":"kr 0,00","website":"","publicly_traded":"false","sys_mod_count":"5","u_email_domain":"","sys_tags":"","vendor_manager":"","customer":"false"}]} Contenet of XML data
    [07/05/19 14:42:11.908]:Rest-SN ST: Pumping XDS to eDirectory.
    [07/05/19 14:42:11.924]:Rest-SN PT: Action: do-set-local-variable("lvname",scope="policy",token-xpath("es:JSONparse($data,'result.name')")).
    [07/05/19 14:42:11.924]:Rest-SN ST: Performing operation modify for \BBCIDV\system\driverset1\Rest-ServicenowDW\PermissionEntMapping.
    [07/05/19 14:42:11.924]:Rest-SN PT: arg-string(token-xpath("es:JSONparse($data,'result.name')"))
    [07/05/19 14:42:11.924]:Rest-SN ST: --JCLNT-- \BBCIDV\system\driverset1\Rest-ServicenowDW : Duplicating : context = 1951400030, tempContext = 1951400158
    [07/05/19 14:42:11.924]:Rest-SN PT: token-xpath("es:JSONparse($data,'result.name')")
    [07/05/19 14:42:11.924]:Rest-SN ST: Modifying entry \BBCIDV\system\driverset1\Rest-ServicenowDW\PermissionEntMapping.
    [07/05/19 14:42:11.924]:Rest-SN PT: Token Value: "com.novell.xsl.extensions.JavaObject@5120c915".
    [07/05/19 14:42:11.924]:Rest-SN PT: Arg Value: "com.novell.xsl.extensions.JavaObject@5120c915".
    [07/05/19 14:42:11.924]:Rest-SN PT: Action: do-trace-message(token-local-variable("lvname") " Contenet of lvname ").
    [07/05/19 14:42:11.924]:Rest-SN PT: arg-string(token-local-variable("lvname") " Contenet of lvname ")
    [07/05/19 14:42:11.924]:Rest-SN PT: token-local-variable("lvname")
    [07/05/19 14:42:11.924]:Rest-SN PT: Token Value: "com.novell.xsl.extensions.JavaObject@5120c915".
  • So I am not 100% sure how to pick out the name node from the JSON since it is inside an array.

    So I would find consider opening an ECMA Object in Designer.

     

    Then set a variable say "payload" with the text of your JSON.

    Then JSON.parse($payload) and then play around with differing syntax to see which works

    The good news is that the ECMA editor for ECMA Resources at the bottom third of the screen has a greater than sign, which is a command prompt and you can execute a function from there and test your ECMA. Much faster than in the engine. In theory the ECMA in Designer is the same interpreter in the engine so it should be a valid test.

    Might be more like:
    token-xpath("es:JSONparse($data,'result[0].name')

    I would have to spend some time testing to be sure I got it correct. But you can do that for yourself.

     

     

  • I find it easyest to do the JSON to XDS conversion in JavaScript. Define a function that takes JSON as input, parses it into a JS object then then use DOM methods to build an XDS document.

    In this example I create a status element and an instance element for every array member of result:

     

     

     

    /** * parse JSON returned by REST service and transform it into XDS elements * {string} payload JSON encoded string * @return {NodeSet} input converted to a NodeSet */ function JSONtoXDS (payload) { var nodeSet = new Packages.com.novell.xml.xpath.NodeSet(); var document = Packages.com.novell.xml.dom.DocumentFactory.newDocument(); var statusElement = document.createElement('status'); try { var response = JSON.parse(payload); } catch (e) { // parsing error statusElement.setAttributeNS(null, 'level', 'error'); statusElement.setAttributeNS(null, 'type', 'driver-general'); statusElement.appendChild(document.createTextNode(e.toString())); statusElement.appendChild(document.createTextNode(payload)); nodeSet.add(statusElement); return (nodeSet); } if (Array.isArray(response.result)) { statusElement.setAttributeNS(null, 'level', 'success'); statusElement.setAttributeNS(null, 'type', 'app-general'); statusElement.appendChild(document.createTextNode('number of responses: ' response.result.length)); nodeSet.add(statusElement); // create the instance node for (var i = 0; i < response.result.length; i ) { var instanceElement = document.createElement('instance'); instanceElement.setAttributeNS(null, 'src-dn', response.result[i]['u_kundenr']); for (var key in response.result[i]) { var attrElement = document.createElement('attr'); attrElement.setAttributeNS(null, 'attr-name', key); var valueElement = document.createElement('value'); var value = response.result[i][key]; if (typeof value !== 'string') { value = JSON.stringify(value); } valueElement.appendChild(document.createTextNode(value)); attrElement.appendChild(valueElement); instanceElement.appendChild(attrElement); } nodeSet.add(instanceElement); } } else { // handle application specific error statusElement.setAttributeNS(null, 'level', 'error'); statusElement.setAttributeNS(null, 'type', 'app-general'); statusElement.appendChild(document.createTextNode('some error')); nodeSet.add(statusElement); } return (nodeSet); }

     

    Call the function from the input tranformation policy set like this to replace the current operation with the parsed one:

     

     

    <policy xmlns:es='http://www.novell.com/nxsl/ecmascript'> <rule> <description>Translate JSON to XDS</description> <conditions> <and> <if-xpath op='true'>string-length(./driver-operation-data/response/value/text())>0</if-xpath> </and> </conditions> <actions> <do-set-local-variable name='lvResponse' scope='policy'> <arg-node-set> <token-xpath expression='es:JSONtoXDS(./driver-operation-data/response/value/text())'/> </arg-node-set> </do-set-local-variable> <do-clone-xpath dest-expression='..' src-expression='$lvResponse'/> <do-strip-xpath expression='$current-op'/> </actions> </rule> </policy>

     

  • Looks like your second part, the XML failed to render right.

    I have an ECMA package I maintain for my stuff. Do you mind if I add your function to it?  (Of course add credit to you in it). 

  • Hi Geoff,

    thanks for pointing out the formatting error. I did a convert XML to text in VSCode and posted that as HTML/XML code in the forum.

    Sure. Add it to your collection.

     

    Norbert

  • I tried creating the ecmascript with JSON toXDS content but getting syntax error in for loop. Can you please suggest.
  • Ya me too. Code pasted is not quite complete.  I got it to run, but not work, still working on fixing it on my end.

  • Verified Answer

    So the JavaScript Engine used in IDM doesn't yet like for..of. These needs to be changed to classic for loop and for..in.

    Also the appendChild calls need to be in the inner loop.

    I've edited the my original post.

    This gives me

    <status level="success" type="app-general">number of responses: 1</status> <instance src-dn="1015425"> <attr attr-name="banner_image_light"> <value></value> </attr> <attr attr-name="country"> <value>DK</value> </attr> <attr attr-name="parent"> <value>{"display_value":"","link":"http://servicenow-test.BBC.dk/api/now/table/core_company/true"}</value> </attr> <attr attr-name="notes"> <value></value> </attr> <attr attr-name="stock_symbol"> <value></value> </attr> <attr attr-name="u_logopath"> <value></value> </attr> <attr attr-name="discount"> <value></value> </attr> <attr attr-name="u_kundenr"> <value>1015425</value> </attr> <attr attr-name="sys_updated_on"> <value>07.05.2019 04:47:12</value> </attr> <attr attr-name="apple_icon"> <value></value> </attr> <attr attr-name="sys_updated_by"> <value>BBC_POB</value> </attr> <attr attr-name="u_customer_alias"> <value></value> </attr> <attr attr-name="fiscal_year"> <value></value> </attr> <attr attr-name="sso_source"> <value></value> </attr> <attr attr-name="sys_created_on"> <value>06.10.2016 13:18:52</value> </attr> <attr attr-name="contact"> <value></value> </attr> <attr attr-name="sys_domain"> <value>{"display_value":"TOP/MSP/BBC","link":"http://servicenow-test.BBC.dk/api/now/table/domain/4c346138a44f12005821241c95d66248"}</value> </attr> <attr attr-name="u_problem_coordinator"> <value>{"display_value":"PROBLEM","link":"http://servicenow-test.BBC.dk/api/now/table/sys_user_group/b4dab65fff02220028765dff7fa4adc7"}</value> </attr> <attr attr-name="stock_price"> <value></value> </attr> <attr attr-name="banner_image"> <value></value> </attr> <attr attr-name="state"> <value></value> </attr> <attr attr-name="sys_created_by"> <value>RestMQUser</value> </attr> <attr attr-name="longitude"> <value></value> </attr> <attr attr-name="zip"> <value>8500</value> </attr> <attr attr-name="profits"> <value>kr 0,00</value> </attr> <attr attr-name="active"> <value>true</value> </attr> <attr attr-name="sys_domain_path"> <value>!!!/!!#/!!$/</value> </attr> <attr attr-name="fax_phone"> <value></value> </attr> <attr attr-name="phone"> <value>86326632</value> </attr> <attr attr-name="banner_text"> <value></value> </attr> <attr attr-name="name"> <value>Børnehaven Regnbuen</value> </attr> <attr attr-name="primary"> <value>false</value> </attr> <attr attr-name="city"> <value>Grenaa</value> </attr> <attr attr-name="latitude"> <value></value> </attr> <attr attr-name="u_company_code"> <value></value> </attr> <attr attr-name="sys_class_name"> <value>Account</value> </attr> <attr attr-name="manufacturer"> <value>false</value> </attr> <attr attr-name="sys_id"> <value>00067ea5ff9a220072816dff7fa4ad17</value> </attr> <attr attr-name="market_cap"> <value>kr 0,00</value> </attr> <attr attr-name="num_employees"> <value></value> </attr> <attr attr-name="rank_tier"> <value>null</value> </attr> <attr attr-name="street"> <value>Lupinskrænten 6</value> </attr> <attr attr-name="vendor"> <value>false</value> </attr> <attr attr-name="lat_long_error"> <value></value> </attr> <attr attr-name="u_ssoidentifier"> <value></value> </attr> <attr attr-name="vendor_type"> <value></value> </attr> <attr attr-name="u_ean_number"> <value></value> </attr> <attr attr-name="revenue_per_year"> <value>kr 0,00</value> </attr> <attr attr-name="website"> <value></value> </attr> <attr attr-name="publicly_traded"> <value>false</value> </attr> <attr attr-name="sys_mod_count"> <value>5</value> </attr> <attr attr-name="u_email_domain"> <value></value> </attr> <attr attr-name="sys_tags"> <value></value> </attr> <attr attr-name="vendor_manager"> <value></value> </attr> <attr attr-name="customer"> <value>false</value> </attr> </instance>

     

  • it worked for me as well. Thanks.