locked
How to simply convert XML file into JSON RRS feed

  • Question

  • So my XML file is this (channel.xml):

    <codeTemplateLibrary version="3.4.1">
      <id>69bd79c2-0e05-4633-ac6d-af7294d86cb5</id>
      <name>RuleBuilder</name>
      <revision>17</revision>
      <lastModified>
        <time>1516896217150</time>
        <timezone>America/Los_Angeles</timezone>
      </lastModified>
      <description>Connected to the Rules Database on DBADI / DBADITEST   - clas29, oct 2017</description>
      <includeNewChannels>true</includeNewChannels>
      <enabledChannelIds>
        <string>8d19c8bd-dd84-4c02-b42b-9b84089a880f</string>
        <string>aea94159-95a3-48ed-a466-d7fbe33a8a64</string>
        <string>ee18611a-ad74-4254-ad61-20fc1ff3928a</string>
        <string>78cd1ff9-71d8-41e3-bb5c-65e5e724c7e6</string>
        <string>76541eed-ecc6-46c6-9f5d-3bafc0434c60</string>
        <string>928b2d8c-84b7-43ec-9caf-7e09b107001b</string>
        <string>e83ff4e7-81b4-42e1-9130-b076d5e62ab7</string>
        <string>e3ec57f6-ebc2-408b-b8f8-6014a31a30a8</string>
        <string>b4f32aac-dcbf-4f01-85eb-453f82fe1680</string>
        <string>32ebdca5-28fd-4c25-94f2-1832d08c712c</string>
      </enabledChannelIds>
      <disabledChannelIds/>
      <codeTemplates>
        <codeTemplate version="3.4.1">
          <id>d99fb932-1531-4f7b-8456-07940c290a07</id>
          <name>addRule</name>
          <revision>41</revision>
          <lastModified>
            <time>1510248522846</time>
            <timezone>America/Los_Angeles</timezone>
          </lastModified>
          <type>FUNCTION</type>
          <contextSet>
            <delegate>
              <contextType>SOURCE_FILTER_TRANSFORMER</contextType>
              <contextType>GLOBAL_UNDEPLOY</contextType>
              <contextType>DESTINATION_DISPATCHER</contextType>
              <contextType>SOURCE_RECEIVER</contextType>
              <contextType>DESTINATION_RESPONSE_TRANSFORMER</contextType>
              <contextType>GLOBAL_DEPLOY</contextType>
              <contextType>GLOBAL_PREPROCESSOR</contextType>
              <contextType>DESTINATION_FILTER_TRANSFORMER</contextType>
              <contextType>GLOBAL_POSTPROCESSOR</contextType>
            </delegate>
          </contextSet>
          <code>/**
    	This adds rules/filters to the oject arry (cache object)
    
    	@param {Any} key - 
    	@param {Any} cacheMinutes - 
    
    */
    function addRule(key, cacheMinutes) {
    
    	var cacheKey = &apos;RuleBuilderCache&apos;;
    	
    	var cache = globalMap.get(cacheKey);
    	if (cache == undefined || cache == null)
    	{
    		//create cache object array
    		cache = {};
    		globalMap.put(cacheKey, cache);
    	}
    
    	var objRule = {
    			CacheMinutes : cacheMinutes,
    			LoadTime : null,
    			Items : null
    			};
    
    	cache[key] = objRule
    }</code>
        </codeTemplate>
        <codeTemplate version="3.4.1">
          <id>c6ffec4a-8af8-4d6b-8a8c-53b3704a534f</id>
          <name>checkRuleCache</name>
          <revision>82</revision>
          <lastModified>
            <time>1510688787366</time>
            <timezone>America/Los_Angeles</timezone>
          </lastModified>
          <type>FUNCTION</type>
          <contextSet>
            <delegate>
              <contextType>SOURCE_FILTER_TRANSFORMER</contextType>
              <contextType>GLOBAL_UNDEPLOY</contextType>
              <contextType>DESTINATION_DISPATCHER</contextType>
              <contextType>SOURCE_RECEIVER</contextType>
              <contextType>DESTINATION_RESPONSE_TRANSFORMER</contextType>
              <contextType>GLOBAL_DEPLOY</contextType>
              <contextType>GLOBAL_PREPROCESSOR</contextType>
              <contextType>DESTINATION_FILTER_TRANSFORMER</contextType>
              <contextType>GLOBAL_POSTPROCESSOR</contextType>
            </delegate>
          </contextSet>
          <code>/**
    	Set the current rule/filter values in the cache or pull from database
    */
    function checkRuleCache() {
    	//get the system date
    	var d = new Date();
    	var currentTime = d.getTime();
    
    	var cacheKey = &apos;RuleBuilderCache&apos;;
    	
    	//if globalMap variable is empty or RulesCache does not equal the current hour, pull from database  (Create globalMap in Deploy script or Global script)
    	if (globalMap.containsKey(cacheKey))
    	{
    		//get all the objects in that array and iterate through them
    		var arrRules = globalMap.get(cacheKey);
    		if (arrRules != null )
    		{
    			for (var key in arrRules)
    			{
    				var objRule = arrRules[key];
    
    				var dif = (currentTime - objRule.LoadTime);
    				//logger.debug(&quot;difference:  &quot; + (dif/1000/60));
    
    				if (objRule.Items == null)
    				{
    					//load from database (initial Load)
    					LoadFromDatabase(objRule, key);				
    				}				
    				else if ((dif)/1000/60 &gt;= objRule.CacheMinutes)
    				{
    					//Load from database (load if &quot;LoadTime&quot; condition is true)
    					LoadFromDatabase(objRule, key);
    				}
    			}
    		}
    	}
    	
    
    	/**
    		Make a connection string using configurationMap Keys
    		
    		@return {String} return database connection
    		 
    	*/
    	function getRulesDatabaseConnection() {
    		
    		var dbConnURL = configurationMap.get(&quot;RuleBuilderDatabaseConnectionString&quot;); 
    		var dbDriver = configurationMap.get(&quot;RuleBuilderDatabaseDriver&quot;); 
    		var dbUsername= configurationMap.get(&quot;RuleBuilderDatabaseUser&quot;); 
    		var dbPassword = configurationMap.get(&quot;RuleBuilderDatabaseUserPW&quot;);
    		var dbConn = DatabaseConnectionFactory.createDatabaseConnection(dbDriver,dbConnURL, dbUsername,dbPassword);
    	
    		return dbConn;
    	}
    	
    
    	/**
    		This calls a method to load the result values into the RuleObject Items property
    
    		@param {Any} objRule - 
    		@param {Any} key - 
    
    	**/
    	function LoadFromDatabase(objRule, key)
    	{
    		//get the system date
    		var d = new Date();
    		objRule.LoadTime = d.getTime();
    	
    		var resultSet = getRuleItemValuesByRuleKey(key)
    	
    		//null basically indicates that we could not get results from the database
    		if (resultSet != null)
    		{
    			objRule.Items = {};
    			while(resultSet.next())
    			{
    				objRule.Items[resultSet.getString(&apos;Value&apos;)] = resultSet.getString(&apos;Value&apos;);	
    			}	
    		}
    	}
    	
    
    	/**
    		This gets the rule values by Names from the database
    	
    		@param {Any} key - 
    		@return {array} return Results for stored procedure
    		
    	**/
    	function getRuleItemValuesByRuleKey(key) {
    	
    		try
    		{
    			var dbConn = getRulesDatabaseConnection();
    		}
    		catch(e)
    		{
    			logger.error(&quot;Unable to connect to database. &quot; + e);
    			return null;
    		}
    		
    		var rsResult = dbConn.executeCachedQuery(&quot;EXEC App.pGetRuleItemValuesByRuleKey &apos;&quot; + key + &quot;&apos;&quot;);
    		dbConn.close();
    		
    		return rsResult;
    	}
    }
    
    
    </code>
        </codeTemplate>
        <codeTemplate version="3.4.1">
          <id>77668db0-ad94-441b-9fc2-6d7912505566</id>
          <name>isInRule</name>
          <revision>13</revision>
          <lastModified>
            <time>1510248523284</time>
            <timezone>America/Los_Angeles</timezone>
          </lastModified>
          <type>FUNCTION</type>
          <contextSet>
            <delegate>
              <contextType>SOURCE_FILTER_TRANSFORMER</contextType>
              <contextType>GLOBAL_UNDEPLOY</contextType>
              <contextType>DESTINATION_DISPATCHER</contextType>
              <contextType>SOURCE_RECEIVER</contextType>
              <contextType>DESTINATION_RESPONSE_TRANSFORMER</contextType>
              <contextType>GLOBAL_DEPLOY</contextType>
              <contextType>GLOBAL_PREPROCESSOR</contextType>
              <contextType>DESTINATION_FILTER_TRANSFORMER</contextType>
              <contextType>GLOBAL_POSTPROCESSOR</contextType>
            </delegate>
          </contextSet>
          <code>/**
    	Checks to see if value is contained in the given key
    
    	@param {String} key - 
    	@param {String} value - 
    	@return {bool} return boolean
    */
    function isInRule(key, value) {
    	
    	var cacheKey = &apos;RuleBuilderCache&apos;;
    	
    	if (globalMap.containsKey(cacheKey))
    	{
    		var arrRules = globalMap.get(cacheKey);
    		if (arrRules != null )
    		{
    			var objRule = arrRules[key];
    			if (objRule.Items[value] == undefined || objRule.Items[value] == null )
    			{
    				return false;
    			}
    			else
    			{
    				return true;
    			}
    		}
    	}
    }</code>
        </codeTemplate>
      </codeTemplates>
    </codeTemplateLibrary>


    the JSON schema that I need this XML to look like is:

    [
      {
        "id": "string",
        "name": "string",
        "revision": 0,
        "lastModified": "2018-07-25T20:37:20.002Z",
        "description": "string",
        "includeNewChannels": false,
        "enabledChannelIds": [
          "string"
        ],
        "disabledChannelIds": [
          "string"
        ],
        "codeTemplates": [
          {
            "id": "string",
            "name": "string",
            "revision": 0,
            "lastModified": "2018-07-25T20:37:20.002Z",
            "type": "FUNCTION",
            "contextSet": [
              "GLOBAL_DEPLOY"
            ],
            "code": "string",
            "description": "string",
            "purgedProperties": {},
            "addToScripts": false,
            "functionDefinition": {
              "name": "string",
              "parameters": [
                {
                  "name": "string",
                  "type": "string",
                  "description": "string"
                }
              ],
              "returnType": "string",
              "returnDescription": "string",
              "transferData": "string"
            }
          }
        ],
        "purgedProperties": {}
      }
    ]

    My Powershell v3.0 Code looks likes this:

    $xmlObject = [XML](Get-Content -Path ${PathAndFile});
    $Json = $xmlObject | Select-Object -Property InnerXml | ConvertTo-Json -Depth 2

    But now the JSON looks like it is back to XML:

    {
        "InnerXml":  "\u003ccodeTemplateLibrary version=\"3.4.1\"\u003e\u003cid\u003e69bd79c2-0e05-4633-ac6d-af7294d86cb5\u003c/id\u003e\u003cname\u003eRuleBuilder\u003c/name\u003e\u003crevision\u003e17\u003c/revision\u003e\u003clastModified\u003e\u003ctime\u003e1516896217150\u003c/time\u003e\u003ctimezone\u003eAmerica/Los_Angeles\u003c/timezone\u003e\u003c/lastModified\u003e\u003cdescription\u003eConnected to the Rules Database on DBADI / DBADITEST   - clas29, oct 2017\u003c/description\u003e\u003cincludeNewChannels\u003etrue\u003c/includeNewChannels\u003e\u003cenabledChannelIds\u003e\u003cstring\u003e8d19c8bd-dd84-4c02-b42b-9b84089a880f\u003c/string\u003e\u003cstring\u003eaea94159-95a3-48ed-a466-d7fbe33a8a64\u003c/string\u003e\u003cstring\u003eee18611a-ad74-4254-ad61-20fc1ff3928a\u003c/string\u003e\u003cstring\u003e78cd1ff9-71d8-41e3-bb5c-65e5e724c7e6\u003c/string\u003e\u003cstring\u003e76541eed-ecc6-46c6-9f5d-3bafc0434c60\u003c/string\u003e\u003cstring\u003e928b2d8c-84b7-43ec-9caf-7e09b107001b\u003c/string\u003e\u003cstring\u003ee83ff4e7-81b4-42e1-9130-b076d5e62ab7\u003c/string\u003e\u003cstring\u003ee3ec57f6-ebc2-408b-b8f8-6014a31a30a8\u003c/string\u003e\u003cstring\u003eb4f32aac-dcbf-4f01-85eb-453f82fe1680\u003c/string\u003e\u003cstring\u003e32ebdca5-28fd-4c25-94f2-1832d08c712c\u003c/string\u003e\u003c/enabledChannelIds\u003e\u003cdisabledChannelIds /\u003e\u003ccodeTemplates\u003e\u003ccodeTemplate version=\"3.4.1\"\u003e\u003cid\u003ed99fb932-1531-4f7b-8456-07940c290a07\u003c/id\u003e\u003cname\u003eaddRule\u003c/name\u003e\u003crevision\u003e41\u003c/revision\u003e\u003clastModified\u003e\u003ctime\u003e1510248522846\u003c/time\u003e\u003ctimezone\u003eAmerica/Los_Angeles\u003c/timezone\u003e\u003c/lastModified\u003e\u003ctype\u003eFUNCTION\u003c/type\u003e\u003ccontextSet\u003e\u003cdelegate\u003e\u003ccontextType\u003eSOURCE_FILTER_TRANSFORMER\u003c/contextType\u003e\u003ccontextType\u003eGLOBAL_UNDEPLOY\u003c/contextType\u003e\u003ccontextType\u003eDESTINATION_DISPATCHER\u003c/contextType\u003e\u003ccontextType\u003eSOURCE_RECEIVER\u003c/contextType\u003e\u003ccontextType\u003eDESTINATION_RESPONSE_TRANSFORMER\u003c/contextType\u003e\u003ccontextType\u003eGLOBAL_DEPLOY\u003c/contextType\u003e\u003ccontextType\u003eGLOBAL_PREPROCESSOR\u003c/contextType\u003e\u003ccontextType\u003eDESTINATION_FILTER_TRANSFORMER\u003c/contextType\u003e\u003ccontextType\u003eGLOBAL_POSTPROCESSOR\u003c/contextType\u003e\u003c/delegate\u003e\u003c/contextSet\u003e\u003ccode\u003e/**\n\tThis adds rules/filters to the oject arry (cache object)\n\n\t@param {Any} key - \n\t@param {Any} cacheMinutes - \n\n*/\nfunction addRule(key, cacheMinutes) {\n\n\tvar cacheKey = \u0027RuleBuilderCache\u0027;\n\t\n\tvar cache = globalMap.get(cacheKey);\n\tif (cache == undefined || cache == null)\n\t{\n\t\t//create cache object array\n\t\tcache = {};\n\t\tglobalMap.put(cacheKey, cache);\n\t}\n\n\tvar objRule = {\n\t\t\tCacheMinutes : cacheMinutes,\n\t\t\tLoadTime : null,\n\t\t\tItems : null\n\t\t\t};\n\n\tcache[key] = objRule\n}\u003c/code\u003e\u003c/codeTemplate\u003e\u003ccodeTemplate version=\"3.4.1\"\u003e\u003cid\u003ec6ffec4a-8af8-4d6b-8a8c-53b3704a534f\u003c/id\u003e\u003cname\u003echeckRuleCache\u003c/name\u003e\u003crevision\u003e82\u003c/revision\u003e\u003clastModified\u003e\u003ctime\u003e1510688787366\u003c/time\u003e\u003ctimezone\u003eAmerica/Los_Angeles\u003c/timezone\u003e\u003c/lastModified\u003e\u003ctype\u003eFUNCTION\u003c/type\u003e\u003ccontextSet\u003e\u003cdelegate\u003e\u003ccontextType\u003eSOURCE_FILTER_TRANSFORMER\u003c/contextType\u003e\u003ccontextType\u003eGLOBAL_UNDEPLOY\u003c/contextType\u003e\u003ccontextType\u003eDESTINATION_DISPATCHER\u003c/contextType\u003e\u003ccontextType\u003eSOURCE_RECEIVER\u003c/contextType\u003e\u003ccontextType\u003eDESTINATION_RESPONSE_TRANSFORMER\u003c/contextType\u003e\u003ccontextType\u003eGLOBAL_DEPLOY\u003c/contextType\u003e\u003ccontextType\u003eGLOBAL_PREPROCESSOR\u003c/contextType\u003e\u003ccontextType\u003eDESTINATION_FILTER_TRANSFORMER\u003c/contextType\u003e\u003ccontextType\u003eGLOBAL_POSTPROCESSOR\u003c/contextType\u003e\u003c/delegate\u003e\u003c/contextSet\u003e\u003ccode\u003e/**\n\tSet the current rule/filter values in the cache or pull from database\n*/\nfunction checkRuleCache() {\n\t//get the system date\n\tvar d = new Date();\n\tvar currentTime = d.getTime();\n\n\tvar cacheKey = \u0027RuleBuilderCache\u0027;\n\t\n\t//if globalMap variable is empty or RulesCache does not equal the current hour, pull from database  (Create globalMap in Deploy script or Global script)\n\tif (globalMap.containsKey(cacheKey))\n\t{\n\t\t//get all the objects in that array and iterate through them\n\t\tvar arrRules = globalMap.get(cacheKey);\n\t\tif (arrRules != null )\n\t\t{\n\t\t\tfor (var key in arrRules)\n\t\t\t{\n\t\t\t\tvar objRule = arrRules[key];\n\n\t\t\t\tvar dif = (currentTime - objRule.LoadTime);\n\t\t\t\t//logger.debug(\"difference:  \" + (dif/1000/60));\n\n\t\t\t\tif (objRule.Items == null)\n\t\t\t\t{\n\t\t\t\t\t//load from database (initial Load)\n\t\t\t\t\tLoadFromDatabase(objRule, key);\t\t\t\t\n\t\t\t\t}\t\t\t\t\n\t\t\t\telse if ((dif)/1000/60 \u0026gt;= objRule.CacheMinutes)\n\t\t\t\t{\n\t\t\t\t\t//Load from database (load if \"LoadTime\" condition is true)\n\t\t\t\t\tLoadFromDatabase(objRule, key);\n\t\t\t\t}\n\t\t\t}\n\t\t}\n\t}\n\t\n\n\t/**\n\t\tMake a connection string using configurationMap Keys\n\t\t\n\t\t@return {String} return database connection\n\t\t \n\t*/\n\tfunction getRulesDatabaseConnection() {\n\t\t\n\t\tvar dbConnURL = configurationMap.get(\"RuleBuilderDatabaseConnectionString\"); \n\t\tvar dbDriver = configurationMap.get(\"RuleBuilderDatabaseDriver\"); \n\t\tvar dbUsername= configurationMap.get(\"RuleBuilderDatabaseUser\"); \n\t\tvar dbPassword = configurationMap.get(\"RuleBuilderDatabaseUserPW\");\n\t\tvar dbConn = DatabaseConnectionFactory.createDatabaseConnection(dbDriver,dbConnURL, dbUsername,dbPassword);\n\t\n\t\treturn dbConn;\n\t}\n\t\n\n\t/**\n\t\tThis calls a method to load the result values into the RuleObject Items property\n\n\t\t@param {Any} objRule - \n\t\t@param {Any} key - \n\n\t**/\n\tfunction LoadFromDatabase(objRule, key)\n\t{\n\t\t//get the system date\n\t\tvar d = new Date();\n\t\tobjRule.LoadTime = d.getTime();\n\t\n\t\tvar resultSet = getRuleItemValuesByRuleKey(key)\n\t\n\t\t//null basically indicates that we could not get results from the database\n\t\tif (resultSet != null)\n\t\t{\n\t\t\tobjRule.Items = {};\n\t\t\twhile(resultSet.next())\n\t\t\t{\n\t\t\t\tobjRule.Items[resultSet.getString(\u0027Value\u0027)] = resultSet.getString(\u0027Value\u0027);\t\n\t\t\t}\t\n\t\t}\n\t}\n\t\n\n\t/**\n\t\tThis gets the rule values by Names from the database\n\t\n\t\t@param {Any} key - \n\t\t@return {array} return Results for stored procedure\n\t\t\n\t**/\n\tfunction getRuleItemValuesByRuleKey(key) {\n\t\n\t\ttry\n\t\t{\n\t\t\tvar dbConn = getRulesDatabaseConnection();\n\t\t}\n\t\tcatch(e)\n\t\t{\n\t\t\tlogger.error(\"Unable to connect to database. \" + e);\n\t\t\treturn null;\n\t\t}\n\t\t\n\t\tvar rsResult = dbConn.executeCachedQuery(\"EXEC App.pGetRuleItemValuesByRuleKey \u0027\" + key + \"\u0027\");\n\t\tdbConn.close();\n\t\t\n\t\treturn rsResult;\n\t}\n}\n\n\n\u003c/code\u003e\u003c/codeTemplate\u003e\u003ccodeTemplate version=\"3.4.1\"\u003e\u003cid\u003e77668db0-ad94-441b-9fc2-6d7912505566\u003c/id\u003e\u003cname\u003eisInRule\u003c/name\u003e\u003crevision\u003e13\u003c/revision\u003e\u003clastModified\u003e\u003ctime\u003e1510248523284\u003c/time\u003e\u003ctimezone\u003eAmerica/Los_Angeles\u003c/timezone\u003e\u003c/lastModified\u003e\u003ctype\u003eFUNCTION\u003c/type\u003e\u003ccontextSet\u003e\u003cdelegate\u003e\u003ccontextType\u003eSOURCE_FILTER_TRANSFORMER\u003c/contextType\u003e\u003ccontextType\u003eGLOBAL_UNDEPLOY\u003c/contextType\u003e\u003ccontextType\u003eDESTINATION_DISPATCHER\u003c/contextType\u003e\u003ccontextType\u003eSOURCE_RECEIVER\u003c/contextType\u003e\u003ccontextType\u003eDESTINATION_RESPONSE_TRANSFORMER\u003c/contextType\u003e\u003ccontextType\u003eGLOBAL_DEPLOY\u003c/contextType\u003e\u003ccontextType\u003eGLOBAL_PREPROCESSOR\u003c/contextType\u003e\u003ccontextType\u003eDESTINATION_FILTER_TRANSFORMER\u003c/contextType\u003e\u003ccontextType\u003eGLOBAL_POSTPROCESSOR\u003c/contextType\u003e\u003c/delegate\u003e\u003c/contextSet\u003e\u003ccode\u003e/**\n\tChecks to see if value is contained in the given key\n\n\t@param {String} key - \n\t@param {String} value - \n\t@return {bool} return boolean\n*/\nfunction isInRule(key, value) {\n\t\n\tvar cacheKey = \u0027RuleBuilderCache\u0027;\n\t\n\tif (globalMap.containsKey(cacheKey))\n\t{\n\t\tvar arrRules = globalMap.get(cacheKey);\n\t\tif (arrRules != null )\n\t\t{\n\t\t\tvar objRule = arrRules[key];\n\t\t\tif (objRule.Items[value] == undefined || objRule.Items[value] == null )\n\t\t\t{\n\t\t\t\treturn false;\n\t\t\t}\n\t\t\telse\n\t\t\t{\n\t\t\t\treturn true;\n\t\t\t}\n\t\t}\n\t}\n}\u003c/code\u003e\u003c/codeTemplate\u003e\u003c/codeTemplates\u003e\u003c/codeTemplateLibrary\u003e"
    }

    So, I must clearly be doing something wrong.  It sounds like it should be simple, but is not working as it should.

    I would like the InnerXML formated to JSON that will fit the JSON schema for the REST call I am making.

    Any help would be greatly appreciated.

    Thanks,


    Thanks. sc

    Thursday, July 26, 2018 4:49 PM

All replies

  • We cannot write custom converters for you.  You will have to write a lot of code to convert the XML.  ConvertTo-Xl converts PS objects and no XML.  Convert the XML to an object with the structure you need then export to Json.


    \_(ツ)_/

    Thursday, July 26, 2018 4:58 PM