How to create script to export list of records in xml and create attachments

khogan · ‎12-29-2023

I need a script to back up several tables, all records/fields including sysid as an xml and attach to a catalog request, or at the least get it created in the sys_attachment, and I should be able to link the request to the attachments from there. There should be an attachment for each table listed.

@Riya Verma Riya's solution, in this post is very close to what I need but I am a scripting novice and can't seem to modify it to my needs. Solved: Re: Use Script to export records to XML - ServiceNow Community

// Define the tables you want to export records from
var tablesToExport = ['em_match_rule', 'em_mapping_pair', 'em_compose_field', 'em_match_field', 'em_mapping_rule'];

// Loop through each table
for (var i = 0; i < tablesToExport.length; i++) {
var tableName = tablesToExport[i];

// Query records from the table
var gr = new GlideRecord(tableName);
gr.query();

// Create XML document
var xmlDoc = new XMLDocument();
xmlDoc.createElement(tableName + 's'); // Root element

// Loop through the records
while (gr.next()) {
var recordNode = xmlDoc.createElement(tableName);

// Add fields as child elements
var fields = gr.getFields();
for (var j = 0; j < fields.size(); j++) {
var field = fields.get(j);
var fieldName = field.getName();
var fieldValue = gr.getValue(fieldName);

var fieldNode = xmlDoc.createElement(fieldName);
fieldNode.setTextContent(fieldValue);
recordNode.appendChild(fieldNode);
}

xmlDoc.appendChild(recordNode);
}

// Export XML to a file
var xmlContent = xmlDoc.toString();

// Save the xmlContent to a file using appropriate methods for your environment. This is the part I cannot figure out.

// Log the export details
gs.info('Exported ' + gr.getRowCount() + ' records from ' + tableName + ' to XML.');
}

Aniket Chavan · ‎12-29-2023

Hello @khogan ,

Give a try to the script below and let me know how it works for you.

// Define the tables you want to export records from
var tablesToExport = ['em_match_rule', 'em_mapping_pair', 'em_compose_field', 'em_match_field', 'em_mapping_rule'];

// Replace 'catalog_request_sys_id' with the actual Sys ID of the catalog request
var catalogRequestSysId = 'catalog_request_sys_id';

// Loop through each table
for (var i = 0; i < tablesToExport.length; i++) {
    var tableName = tablesToExport[i];

    // Query records from the table
    var gr = new GlideRecord(tableName);
    gr.query();

    // Create XML document
    var xmlDoc = new XMLDocument();
    xmlDoc.createElement(tableName + 's'); // Root element

    // Loop through the records
    while (gr.next()) {
        var recordNode = xmlDoc.createElement(tableName);

        // Add fields as child elements
        var fields = gr.getFields();
        for (var j = 0; j < fields.size(); j++) {
            var field = fields.get(j);
            var fieldName = field.getName();
            var fieldValue = gr.getValue(fieldName);

            var fieldNode = xmlDoc.createElement(fieldName);
            fieldNode.setTextContent(fieldValue);
            recordNode.appendChild(fieldNode);
        }

        xmlDoc.appendChild(recordNode);
    }

    // Export XML to a file
    var xmlContent = xmlDoc.toString();
    var fileName = tableName + '_export.xml';

    // Save the xmlContent to a file using appropriate methods for your environment
    // For example, if you are running this script on the ServiceNow server, you can use the following:
    var file = new GlideRecord('sys_attachment');
    file.initialize();
    file.table_name = tableName;
    file.file_name = fileName;
    file.file = xmlContent;
    file.sys_id = file.insert();

    // Attach the file to the catalog request
    var attachment = new GlideRecord('sys_attachment');
    attachment.initialize();
    attachment.file = file.sys_id.toString();
    attachment.table_sys_id = catalogRequestSysId; // Attach to catalog request
    attachment.table_name = 'sc_request';
    attachment.insert();

    // Log the export details
    gs.info('Exported ' + gr.getRowCount() + ' records from ' + tableName + ' to XML.');
}

Let me know your views on this and Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks,

Aniket

khogan · ‎12-29-2023

Hello Aniket, thanks for the reply. It is closer, it is creating attachments without data. 5 records get related to the requests, but are all named 'sys_attachment.do'. It also created 5 correct xml file names but without data.

Aniket Chavan · ‎12-29-2023

Hello @khogan ,

Okay then Please refer the below revised version of the script and let me know if you still faces any issue.

// Define the tables you want to export records from
var tablesToExport = ['em_match_rule', 'em_mapping_pair', 'em_compose_field', 'em_match_field', 'em_mapping_rule'];

// Replace 'catalog_request_sys_id' with the actual Sys ID of the catalog request
var catalogRequestSysId = 'catalog_request_sys_id';

// Loop through each table
for (var i = 0; i < tablesToExport.length; i++) {
    var tableName = tablesToExport[i];

    // Query records from the table
    var gr = new GlideRecord(tableName);
    gr.query();

    // Create XML document
    var xmlDoc = new XMLDocument();
    xmlDoc.createElement(tableName + 's'); // Root element

    // Loop through the records
    while (gr.next()) {
        var recordNode = xmlDoc.createElement(tableName);

        // Add fields as child elements
        var fields = gr.getFields();
        for (var j = 0; j < fields.size(); j++) {
            var field = fields.get(j);
            var fieldName = field.getName();
            var fieldValue = gr.getValue(fieldName);

            var fieldNode = xmlDoc.createElement(fieldName);
            fieldNode.setTextContent(fieldValue);
            recordNode.appendChild(fieldNode);
        }

        xmlDoc.getDocumentElement().appendChild(recordNode);
    }

    // Export XML to a file
    var xmlContent = xmlDoc.toString();
    var fileName = tableName + '_export.xml';

    // Save the xmlContent to a file using appropriate methods for your environment
    // For example, if you are running this script on the ServiceNow server, you can use the following:
    var attachment = new GlideRecord('sys_attachment');
    attachment.initialize();
    attachment.table_name = 'sys_user';
    attachment.file_name = fileName;
    attachment.file = xmlContent;
    var attachmentSysId = attachment.insert();

    // Attach the file to the catalog request
    var attachmentToRequest = new GlideRecord('sys_attachment');
    attachmentToRequest.initialize();
    attachmentToRequest.file = attachmentSysId.toString();
    attachmentToRequest.table_sys_id = catalogRequestSysId; // Attach to catalog request
    attachmentToRequest.table_name = 'sc_request';
    attachmentToRequest.insert();

    // Log the export details
    gs.info('Exported ' + gr.getRowCount() + ' records from ' + tableName + ' to XML.');
}

khogan · ‎12-29-2023

The same results, I did not notice a correction on line 46. You have sys_user. Should be sc_request.

attachment.table_name = 'sc_request';

When you test it does it work for you?