Generate XML with NodeJS and MySQL using XML builder

How to generate XML with NodeJS

XML is a popular information exchange and storage format on internet. XML is used in web services, RSS Feeds and many other technologies. In a previous tutorial we discussed How to parse XML using NodeJS, this article explains how to generate XML with NodeJS and MySQL.

generate xml using nodejs

To access data from MySQL Node ORM and to generate XML xml-builder modules are used. Suppose there is a Book Store application where we need to share books information with other users, so we generate XML with NodeJS containing books data. So in this tutorial we are going to:

1. Create a books database and table using MySQL

2. Insert sample data into the table

3. Create a NodeJS, express application

4. Install necessary plugins such as Node ORM and XML Builder.

5. Fetch data from database, generate XML using nodejs and save to a file.

Create a database and table for books

First you are going to create a MySQL database and a table for storing books information. To use MySQL install WAMP or XAMPP. After installation, Open PHPMyAdmin. Click MySQL tab, copy and paste following SQL queries and execute.

-- Database: `dbbookstore`


CREATE DATABASE IF NOT EXISTS `dbbookstore` DEFAULT 

CHARACTER SET latin1 COLLATE latin1_swedish_ci;

USE `dbbookstore`;


CREATE TABLE `tbl_books` (

  `id` int(11) NOT NULL,

  `name` varchar(500) NOT NULL,

  `price` float(8,2) NOT NULL,

  `author` varchar(300) NOT NULL,

  `category` varchar(250) NOT NULL,

  `language` varchar(100) NOT NULL,

  `ISBN` varchar(40) NOT NULL,

  `publish_date` date NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `tbl_books` (`id`, `name`, `price`, `author`, `category`, `language`, `ISBN`, `publish_date`) VALUES

(1, 'Linux: The Complete Reference', 27.22, 'Richard Petersen', 'Computer', 'en', '978-0071492478', '2016-12-14'),

(2, 'Design Patterns: Elements of Reusable Object-Oriented Software ', 15.11, 'Ralph Johnson, John Vlissides, Grady Booch', 'Computer Science', 'en', '978-0201633610', '2016-03-01'),

(3, 'Machine Learning for Absolute Beginners\r\n', 10.36, 'Oliver Theobald', 'Computer Science', 'en', '123-58679-654', '2016-08-01'),

(4, 'Python Crash Course: A Hands-On, Project-Based Introduction to Programming', 21.58, ' Eric Matthes', 'Programming', 'en', '659-8546-324', '2015-11-30'),

(5, 'Data Structures and Algorithms in Java', 102.65, 'Michael T. Goodrich, Roberto Tamassia, Michael H. Goldwasser', 'Computer Science', 'en', ' 978-1118777788', '2014-06-23'),

(6, 'Star Wars: Darth Vader Vol. 1: Vader', 26.54, 'Kieron Gillen', 'Comic Novels', 'en', '485-6582-658', '2015-09-16'),

(7, 'Star Wars Vol. 1: Skywalker Strikes', 16.23, 'Jason Aron', 'Novels', 'en', '159-7539-985', '2011-04-11'),
(8, 'Phonics for Kindergarten, Grade K ', 6.32, 'Carson-Dellosa Publishing ', 'Education', 'en', '412-6548-7854', '2016-08-10'),

(9, 'Astrophysics for People in a Hurry ', 9.95, 'Astrophysics for People in a Hurry ', 'Science', 'en', '654-71235-654', '2010-10-02'),

(10, 'Let''s Review Algebra I', 8.54, 'Gary Rubinstein (Author) ', 'Science', 'en', '978-1438009854', '2006-03-24');




ALTER TABLE `tbl_books`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `tbl_books`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

Queries above creates a database dbbookstore and a table tbl_books. Then sample data is inserted into the table.

generate XML with NodeJS

Create a NodeJS and Express app to generate xml using nodejs

If you have not installed NodeJS yet, then visit nodejs.org website and download NodeJS. Install it according to you system. Using NPM Install express generator. On command line, type following command.

express --view=pug generate-xml-using-nodejs

This command generates a NodeJS and express application to generate xml using nodejs.

generate XML with NodeJS

After generating application type command below.

cd generate-xml-using-nodejs && npm install

This command changes directory to project folder, installs all required modules. To run this app, type

SET DEBUG=generate-xml-using-nodejs:* & npm start

To see running app, open browser and type following address.

http://localhost:3000

By default a NodeJS application runs on port 3000.

Install Node ORM module for database access to generate xml with nodejs

To connect MYSQL database and fetch table data you have to install an ORM or Object relational Mapper. Node ORM module is used to perform database related queries. You can create models, validations or run queries easily using an ORM. Type command.

npm install orm

Install xmlbuilder module to generate XML with nodejs

After data is fetched from database, XML file containing all books information is generated. For this purpose xmlbuilder module need to be installed.

npm install xmlbuilder

This installs XML builder module to convert books data to XML file.

Generate XML with NodeJS

Open index.js file in routes directory, remove all code and add the code below

generate XML with NodeJS

var express = require('express');

var router  = express.Router();

var orm     = require('orm');

var builder = require('xmlbuilder');

var fs     = require('fs');

var dirPath = __dirname + "/../public/xmlfiles/booksxml.xml";

router.use(orm.express("mysql://root:@localhost:/dbbookstore", {

  define: function (db, models, next) {

    models.book = db.define("tbl_books", { 

    name      : String,                

    price     : Number,

    author    : String, 

    category  : String,

    language  : String,

    ISBN      : String, 

    publish_date : Date 

              });

      next();

  }

}));

/* GET home page. */

router.get('/', function(req, res, next) {

     var xml = builder.create('bookstore');
     
     var result = req.models.book.find({

      }, function(error, books){
    
        if(error) throw error;

            for(var i=0; i< books.length; i++){

                xml.ele('book')

                .ele('name', {'lang': books[i]['language']}, books[i]['name']).up()

                .ele('price', books[i]['price']).up()

                .ele('category', books[i]['category']).up()

                .ele('author', books[i]['author']).up()

                .ele('ISBN', books[i]['ISBN']).up()

                .ele('publish_date', books[i]['publish_date']).end();

            }
             
            var xmldoc = xml.toString({ pretty: true }); 
           
            fs.writeFile(dirPath, xmldoc, function(err) {

                if(err) { return console.log(err); } 

                console.log("The file was saved!");

                res.render('index', { title: 'Generate XML using NodeJS' });
          
              }); 
         
         });
    
  });


module.exports = router;

To access database Node ORM module is included.

var orm     = require('orm');

Next to include xmlbuilder.

var builder = require('xmlbuilder');

To save generated XML to a file fs module is used.

var fs     = require('fs');

Connect to books database using ORM to generate xml with nodejs

To connect to database we pass orm.express inside router.use method with host, usernamepassword and database name.

router.use(orm.express("mysql://root:@localhost:/dbbookstore", {

  ......

}));

The code above connects to database.

Create a model using Node ORM to generate xml

In order to fetch data from table, an ORM for tbl_books table is created using Node ORM.

router.use(orm.express("mysql://root:@localhost:/dbbookstore", {

 	define: function (db, models, next) {

 		models.book = db.define("tbl_books", { 
               
                name      : String,                
 		
                price     : Number,
 		
                author    : String, 
 		
                category  : String,
 		
                language  : String,
 		
                ISBN      : String, 
 		
                publish_date : Date 
               
              });
 	   
               next();
        }

}));

After connection to database a model is defined for table. An anonymous function accepts 3 parameters such as database object db, models and next. Inside define a new model for book table is created.

Using db.define method, table name tbl_book is passed and second parameters are table column names with their data types such as name : String, price: Number etc. After model is defined inside ‘/’ route, data is fetched from database using find method.

Create XML builder object with root element

Inside ‘/’ route xmlbuilder object is created and a root element is passed. Every XML document have a root element, in this case it is bookstore.

var xml = builder.create('bookstore');

Next all books information is fetched from database using request.models.find method. Inside find method two arguments are passed, first parameter is criteria and second is the anonymous function.

Fetch data to generate XML using nodejs

Inside req.models.book.find method, first argument is the criteria. Criteria in this case is empty as we want to fetch all records. Second parameter is a function that accepts an error and books variable that contains information of all books. Find method returns all the  data from database table. Inside function we use create XML file.

var result = req.models.book.find({
      
      }, function(error, books){

         if(error) throw error;

         ......     


});

Add elements and attributes to XML document

In order to add each book information to XML document a loop is performed. XML object’s method .ele accepts book as main element to embed other book information. Then other elements are chained using .ele method with name element and inside name element, an attribute named lang is added as an object with language value.

Other elements such as price, category, author, isbn and publish_date are added using .ele method. up method is used to close the tags. Lastly .end method is used to close book element.

for(var i=0; i< books.length; i++){

     xml.ele('book')
                
          .ele('name', {'lang': books[i]['language']}, books[i]['name']).up()

                .ele('price', books[i]['price']).up()

                .ele('category', books[i]['category']).up()

                .ele('author', books[i]['author']).up()

                .ele('ISBN', books[i]['ISBN']).up()

                .ele('publish_date', books[i]['publish_date']).end();

            }

Convert XML document to a string

xml object is converted to a string using .toString method. A parameter {pretty : true} is also passed.

var xmldoc = xml.toString({ pretty: true }); 

Save generated XML to a file

To save generated XML to a file, we use fs module’s writeFile method and pass destination file path, XML and a callback function. Call back function provides err, if err occurs it throws an error. This generates xml using nodejs.

fs.writeFile(dirPath, xmldoc, function(err) {

    if(err) { return console.log(err); } 

       console.log("The file was saved!");
       
       res.render('index');

 });
index.js file is rendered and a message is displayed to user that XML file is generate successfully.

generate XML with nodejs generate xml with nodejs generate xml with nodejs

Summary

To finalize, in this tutorial you have learned about how to generate XML with NodeJS. Node ORM is used to fetch books data and xmlbuilder to generate XML from this data. Source code used in tutorial  can be downloaded by clicking on this link.

Please leave your feedback and comments below. Follow us on twitter to get informed about upcoming tutorials.

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save