Export different record(s) from different tables into one XML

General Add comments
by:

In Service-now (SNC) it is possible to export records into one XML file. This action can be performed in the list view or on a single record.


Unfortunately this only exports the records on one single table, in this example the incident table. For other purposes, for example use the XML file to convert it to another format (Word) or use this file for an initial load to an external system this is not usable. For this solution a complete export from different tables is needed. For example a change request with all the affected CI’s and approvals related to the change and all the individual change tasks.

In the out of the box SNC implementation it is possible to export a complete update set into one XML file, question is, is this usable?
Yes it is!
J
In this article I will explain how to configure this and which components are needed.

 


The export of the update set is triggered using a link (UI Action), called Export to XML.

The export uses three types of functionality in SNC.

  • Script include
  • Processor
  • UI Action or another trigger (UI Action, Module)


The following steps have to be performed to export a custom XML file. For the explanation the example will be used to export a change request with the related approvals, Affected CI’s and the related change tasks.

    1. Create a new “Script Include”, with the following values.

Name: “ExportChangeRequest”.

Script:

var ExportChangeRequest = Class.create();

ExportChangeRequest.prototype = {
initialize: function(parent_table, sys_id){
this.parent_table = parent_table;
this.sys_id = sys_id;
this.related_lists = [];
this.query_sets = [];

},

/**
* Add related records
* @param String child_table – table to query
* @param String reference_field – field to query on
*/
addRelatedList: function(child_table, reference_field){
var related_list = [child_table, reference_field];
this.related_lists.push(related_list);

},

/**
* Add a query definition to define a collection of records to
* export, allows for custom queries that cannot be met with
* addRelatedList
* @param String table
* @param String query – encoded query string
*/
addQuerySet: function(table, query){
var set = [table, query];
this.query_sets.push(set);

},

getFileName: function(){
return (this.parent_table + ‘_’ + this.sys_id + ‘.xml’);
},

/**
* Enable attachment support for all records being exported
* @param boolean b
*/
setAttachments: function(b){
this.includeAttachments = b;
},     

/**
* Required XML Headers
*/
setHeaders: function(response){
response.setHeader(‘Pragma’, ‘public’);
response.addHeader(‘Cache-Control’, ‘max-age=0’);
// setting the content type
response.setContentType(‘application/octet-stream’);
response.addHeader(‘Content-Disposition’,
“attachment;filename=\”” + this.getFileName() + “\””);
},

/**
* Process the request
* @param {Object} response
*/
exportRecords: function(response){
this.setHeaders(response);
var outputStream = response.getOutputStream();
this.hd = this.beginExport(outputStream);

var gr = new GlideRecord(this.parent_table);
gr.get(this.sys_id);

this.exportRecord(gr);
this.exportChildren();
this._exportQuerySets();
this.endExport(outputStream);
},

/**
* Export an individual GlideRecord
* @param GlideRecord record
*/
exportRecord: function(record, hd){
var recordSerializer = new
Packages.com.glide.script.GlideRecordXMLSerializer();
recordSerializer.setApplySecurity(true);
recordSerializer.serialize(record, hd, new
Packages.java.lang.String(‘INSERT_OR_UPDATE’));
if (this.includeAttachments && record.getTableName().substring(0,
14) != “sys_attachment”) {
this.exportAttachments(record);
}
},

/**
* Export attachments for a given GlideRecord
* @param GlideRecord target
*/
exportAttachments: function(target){
var sa = new Packages.com.glide.ui.SysAttachment();
//get sys_attachments
var attach = new GlideRecord(“sys_attachment”);
attach.addQuery(“table_name”, target.getTableName());
attach.addQuery(“table_sys_id”, target.getUniqueValue());
attach.query();
while (attach.next()) {
this.exportRecord(attach);
var parts = sa.getAttachmentParts(attach.getUniqueValue());
while (parts.next()) {
this._exportRecord(parts);
}
}
},

/**
* Get attachment document parts
* @param GlideRecord attach – sys_attachment record
*/
_exportAttachDoc: function(attach){
var doc = new GlideRecord(“sys_attachment_doc”);
doc.addQuery(“sys_attachment”, attach.getUniqueValue());
doc.query();
},

/**
* Process related lists
*/
exportChildren: function(){
for (var key = 0; key < this.related_lists.length; key++) {
var table = this.related_lists[key];
this.exportTableChildren(table);
}
},

/**
* Get the records from the related list entries
* @param Array table – array of tableName, fieldname
*/
exportTableChildren: function(table){
var child = new GlideRecord(table[0]);
child.addQuery(table[1], this.sys_id);
child.query();

while (child.next()) {
this.exportRecord(child);
}
},

/**
* Process query sets and export results
*/
_exportQuerySets: function(){
for (var i = this.query_sets.length – 1; i >= 0; i–) {
var querySet = this.query_sets[i];
var table = querySet[0];
var query = querySet[1];
var gr = new GlideRecord(table);
gr.addEncodedQuery(query);
gr.query();
while (gr.next()) {
this.exportRecord(gr);
}
}
},

/**
* Initialize the result
* @param {Object} outputStream
*/
beginExport: function(outputStream){
var streamResult = new
Packages.javax.xml.transform.stream.StreamResult(outputStream);
var tf =
Packages.javax.xml.transform.sax.SAXTransformerFactory.
newInstance();
var hd = tf.newTransformerHandler();
var serializer = hd.getTransformer();
serializer.setOutputProperty(
Packages.javax.xml.transform.OutputKeys.ENCODING, ‘UTF-8’);
serializer.setOutputProperty(Packages.javax.xml.transform.
OutputKeys.INDENT, ‘yes’);
hd.setResult(streamResult);
hd.startDocument();
var attr = new
Packages.com.glidesoft.util.xml.GlideAttributesImpl();
attr.addAttribute(“unload_date”,
Packages.com.glide.sys.util.SysDateUtil.getUMTDateTimeString());
hd.startElement(“”, “”, ‘unload’, attr);
return hd;
},

endExport: function(outputStream, hd){
hd.endElement(“”, “”, ‘unload’);
hd.endDocument();
outputStream.close();
},
}

    2. Create a new “Processor” called “ExportChangeRequest”. Fill the fields using the following values.

Name: ExportChangeRequest

Type: Script

Path: export_change_request

(NOTE: this is the same as the URL in the UI Action which is marked in bold or else it will not work).

Script:

    // Declare variables
var sysid = g_request.getParameter(‘sysparm_sys_id’);
var exporter = new ExportChangeRequest(‘change_request’, sysid);

// Set the header
exporter.setHeaders(g_response);
var outputStream = g_response.getOutputStream();

// Begin export
var hd = exporter.beginExport(outputStream);

// Export the change request
var grchgreq = new GlideRecord(‘change_request’);
grchgreq.get(sysid);
exporter.exportRecord(grchgreq, hd);

// Export the related change tasks
var grchgtsk = new GlideRecord(‘change_task’);
grchgtsk.addQuery(‘change_request’, sysid);
grchgtsk.query();
while(grchgtsk.next()) {
exporter.exportRecord(grchgtsk, hd);
}

// Export the many 2 many relationship which contains the approval
records and the affected CI’s
exportm2mtable(‘sys_user’, ‘sysapproval_approver’, ‘sysapproval’,
‘approver’, sysid, hd);
exportm2mtable(‘cmdb_ci’, ‘task_ci’, ‘task’, ‘ci_item’, sysid, hd);

// End export
exporter.endExport(outputStream, hd);

/* The function uses some standard variables to export m2m tables.
* tablename = The table which is used as output.
* m2mtablename = The m2m table which creates the relation between the
tablename and the current exported table.
* input = The sys_id which points to the current table.
* output = the sys_id which points to the tablename.
* sysid = The current sys_id of the export record.
* hd = Just give the hd variable to this function.
*/
function exportm2mtable(tablename, m2mtablename, input, output, sysid,
hd) {
var gr;
var grm2m;

grm2m = new GlideRecord(m2mtablename);
grm2m.addQuery(input, sysid);
grm2m.query();

// Export the m2m records in the many 2 many table.
while(grm2m.next()) {
exporter.exportRecord(grm2m, hd);
gr = new GlideRecord(tablename);
gr.get(grm2m.getValue(output));
exporter.exportRecord(gr, hd);
}
}

    3. Create an “UI Action” on the change table called “Export change to XML”. Fill the fields using the following values

Condition: current.isValidRecord() == true

Script: action.setRedirectURL(“export_change_request.do?sysparm_sys_id=” + current.sys_id);


If you would like to export more tables you have to define them in the processor. In the Processor you can also define the order in which the tables have to be exported. Simply change the order of the call of the exportRecord() function.

Now let’s see how to produce the export in SNC using the scripts we have created.

    1. Open a change request


    2. Click on the link “Export change to XML”.


    3. A dialog box appears in which state if you want to “Open” or “Save” the XML file. Please choose one.


    4. The XML file contains the records which are defined in the “Processor”.


Voila! You now have one XML file which contains the records we wanted to export.

Please let me know if you have any questions.
You can contact me by mail kelvin.lim@2e2.nl.

NOTE: The SNCGuru website previously posted an article on this topic. You can find the article HERE.

2 Responses to “Export different record(s) from different tables into one XML”

  1. John Roberts Says:

    I don’t think you have to go through that much work. There’s already a ExportWithRelatedList script include, and I see that you’ve already found the enhancements to it for including attachments and custom query sets. These two features will be in the next release. So you should be able to put all of the customizations you need into the processor and not worry about managing another script include that is mostly a duplicate of an out-of-box class. You can reference the updated ExportProject processor in demo or in the next stable release for an example.

  2. Kelvin Lim Says:

    Hi John,

    Thanks for your comment and explanation! I had a look at the ExportProject processor.

    From what i can see in the script i could use two functions instead, one for the 1-n (“addRelatedList(tablename, foreignkey)”) and another for the m-n relationships the function (“addQuerySet(foreigntable, encodedquery)”).

    The explanation i have given in the article gives me some more flexibility to the records i want to export, also when they do not have any relation to each-other.

    Still it is handy to know these functions when you want to do some basic export from one table with their relationships.

Leave a Reply