Remove From My Forums

How to simply convert XML file into JSON

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

0

Sign in to vote

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

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?

Asked by:

How to simply convert XML file into JSON

Question

All replies