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

[khogan]

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]

Hello @khogan ,

Yes, I apologize for the oversight, please lets give a try for below one

// 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 = tableName; // Corrected to use the current table name
    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]

Hi Aniket,

Still no noticeable change, it creates the same 10 records without data as in my earlier screen shot. I can see data is returned in xmlContent. I appended to the bottom of script to view.

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

[Aniket Chavan]

Hello @khogan ,

Since I'm not able to test in my system please let me know if you still facing the same 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';

    // Create an attachment record
    var attachment = new GlideSysAttachment();
    attachment.setFileName(fileName);
    attachment.setContentType('application/xml');
    attachment.write(xmlContent);
    var attachmentSysId = attachment.store();

    // Attach the file to the catalog request
    var attachmentToRequest = new GlideRecord('sys_attachment');
    attachmentToRequest.initialize();
    attachmentToRequest.file = attachmentSysId;
    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.');
    gs.info('XML data returned ' + xmlContent);
}

[khogan]

Its creating an attachment with with the sysid, related to the request but the xml files are blank.

// 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 = '6eed229047801200e0ef563dbb9a71c2';

// 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.content_type = 'text/XML'; //Trying to set contentType

    file.file = xmlContent;

    file.table_sys_id = catalogRequestSysId; // Attach to catalog request#

    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.');

gs.info('XML data returned ' + xmlContent);

}

[Ankur Bawiskar]

@khogan 

why not use OOB Export set feature and export table data to Mid Server?

it supports XML file as well