Javascript to extract specific value from a xml field | SUBSTR function | Adobe Campaign Classic

Just something that I came across during an implementation.

There was a simple requirement to extract a specific attribute from an XML field in a schema. I think this use case is worth sharing.

This can be done using the SUBSTR JavaScript function.

Example:

• To explain this better, let’s take a simple example.

Suppose our requirement is to extract only the CUSTOMER_ID value from the Message column of XtkWorkflowLog schema

Below is the code that you will use to do this.

The code works on the simple SUBSTR function.

Note:

• The SUBSTR() method extracts parts of a string, beginning at the character at the specified position, and returns the specified number of characters.
• Also, the if condition will vary depending on the fields to extract.
• You need to check the start and end position of attribute CUSTOMER_ID in the Message field. The value SUBSTR(49,8) is specific for the customer_id field here.

This value will change depending on what attribute you want to extract.

/*
Author: Prajwal Shetty
Version: 2.0
Purpose: Test to extract customerId
*/

logInfo("Start nmsEventHisto")

//nmsEventHistovar 

cnx = application.getConnection()var stmt = cnx.query("SELECT mData " + "FROM nmsEventHisto limit 50")

for each(var row in stmt)  {
	logInfo(row[0])
}

logInfo("End nmsEventHisto")  
logInfo("Start XtkWorkflowLog")  

//XtkWorkflowLogvar 

stmt = cnx.query("SELECT sMessage " + "FROM XtkWorkflowLog WHERE sMessage LIKE '<?xml%' limit 50")

for each(var row in stmt)  {
	if(row[0].substr(49,8) == 'stomerNu')  
		logInfo("Invalid -->" + row[0].substr(49,8)) 
	else  
		logInfo("Valid -->" + row[0].substr(49,8))
}

	logInfo("End XtkWorkflowLog") 
	cnx.dispose()

Logs:

I have added the valid/invalid condition to handle the unwanted data.

Once the script is run, all the CUSTOMER_ID will be extracted from the XML field into the logs.

Note:

You can remove the valid/invalid letters from the below line so that only id is displayed in the logs. This can be then copied and used accordingly.

logInfo("Invalid -->" + row[0].substr(49,8))

Hope this will be helpful for you at some point in your implementation