A MySQL Tidbit: Dynamic Export To_XML Stored Procedure

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL

Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XML, importing and exporting XML files, etcetera using SQL are pretty commonplace here at Experts Exchange.  

Consequently, in two of my previous MySQL tidbits, I covered some real questions from EE's Q&A forum:


While writing those articles and participating in the mentioned questions, I was urged by a fellow database expert to write some tips on the importing and exporting of XML, specifically in SQL using MySQL database server.  As a result, this particular installment of my SQL tidbits will cover: (1) dynamically constructing XML elements from a given table structure; and (2) exporting a result set to XML file.

The end product will be a technique you can customize and test for your own XML needs.  This technique will be shown in a stored procedure.  For beginning MySQL readers, I will be providing some background tips -- explanations on some of the inner workings of the SQL code in the procedure; however, I will lay out the sections here, so that those more experienced or otherwise love to jump around may do so freely.

Table of Contents

[step="" title=""]««Setup»»
If you have been learning about XML and MySQL,  you have likely read "Using XML in MySQL 5.1 and 6.0" by now.  To build on that documentation, let's use the cities example for our purposes here.  Below are the scripts to create the data so you can follow along in the article.

(table structure -- create statement)

CREATE SCHEMA IF NOT EXISTS xmltest;
                      DROP TABLE IF EXISTS xmltest.cities;
                      CREATE TABLE xmltest.cities (    
                          name CHAR(35) NOT NULL,
                          country CHAR(52) NOT NULL,
                          population INT(11) NOT NULL DEFAULT '0'
                      );

Select all Open in new window

(sample data -- insert statement)

INSERT INTO cities VALUES ('Mumbai (Bombay)','India',10500000);
                      INSERT INTO cities VALUES ('Seoul','South Korea',9981619);
                      INSERT INTO cities VALUES ('São Paulo','Brazil',9968485); 
                      INSERT INTO cities VALUES ('Shanghai','China',9696300);
                      INSERT INTO cities VALUES ('Jakarta','Indonesia',9604900);
                      INSERT INTO cities VALUES ('Karachi','Pakistan',9269265);
                      INSERT INTO cities VALUES ('Istanbul','Turkey',8787958);
                      INSERT INTO cities VALUES ('Ciudad de México','Mexico',8591309); 
                      INSERT INTO cities VALUES ('Moscow','Russian Federation',8389200);
                      INSERT INTO cities VALUES ('New York','United States',8008278);

Select all Open in new window

[/step]
Let's get started !

1. Dynamic

The main part of our technique is this: It has to be dynamic.  The afore-mentioned XML article for MySQL shows how to export XML via command line shell quite nicely using mysql and mysqldump's --xml option.  It also shows how to generate an XML string from a table using CONCAT() and GROUP_CONCAT() functions.  So why are we here again?  The two shell options are just that "shell options" and the latter technique requires hard coding of your table structure.  What we will do here is dynamically create an XML string using SQL code.

In order to do so, we will need to utilize three special statements in MySQL:
[step="" title=""]
(1) Prepare -- takes a single dynamic SQL statement, readies it for execution and assigns a name for future referencing of this new "prepared statement".

PREPARE stmt_name FROM preparable_stmt

Select all Open in new window

(for example)

SET @sqlstring = 'SELECT * FROM `xmltest`.`cities`';
                      PREPARE dySQL FROM @sqlstring;

Select all Open in new window

(2) Execute -- runs the prepared SQL statement using any parameters passed.

EXECUTE stmt_name [USING @var_name [, @var_name] ...]

Select all Open in new window

(for example)

EXECUTE dySQL;

Select all Open in new window

(3) Deallocate Prepare -- cleans up the assigned prepared statement, making it no longer available for use.

{DEALLOCATE | DROP} PREPARE stmt_name

Select all Open in new window

(for example)

DEALLOCATE PREPARE dySQL;

Select all Open in new window

[/step]
This is, of course, a very simplistic example, but we will make this a little more complex in due time.

Remember the point here is we will need to dynamically construct a statement similar to this one from the MySQL XML reference (linked above):

mysql> SELECT CONCAT(
                          ->           '\n<cities>',
                          ->           GROUP_CONCAT(
                          -> '\n\t<city name="', name, '" population="', population, '"/>'
                          -> SEPARATOR ''
                          ->           ),
                          ->           '\n</cities>'
                          ->         ) AS xmldoc
                          ->   FROM cities\G

Select all Open in new window

Therefore, we will need a way to generate a listing of columns "auto-magically"!  

Introducing...

Information_Schema.
These metadata tables hold lots of useful information on our schema structure (data on our data) and so for a given table, we can get a listing of column names on the fly using a query such as:

SELECT COLUMN_NAME
                      FROM information_schema.columns
                      WHERE TABLE_SCHEMA = 'xmltest'
                      AND TABLE_NAME = 'cities'
                      ORDER BY ORDINAL_POSITION;

Select all Open in new window

Now what ?

2. Export

Well, before we can get to our final destination, we have to figure out how we are going to get there; therefore, let's take some time to talk about the export vehicle:

Select {column list} Into Outfile ...

SELECT CONCAT('"', `name`, '", "', `country`, '", ', `population`) 
                      INTO OUTFILE 'c:\\temp\\cities.txt' LINES TERMINATED BY '\r\n'
                      FROM `xmltest`.`cities`;

Select all Open in new window

(results)

"Mumbai (Bombay)", "India", 10500000
                      "Seoul", "South Korea", 9981619
                      "São Paulo", "Brazil", 9968485
                      "Shanghai", "China", 9696300
                      "Jakarta", "Indonesia", 9604900
                      "Karachi", "Pakistan", 9269265
                      "Istanbul", "Turkey", 8787958
                      "Ciudad de México", "Mexico", 8591309
                      "Moscow", "Russian Federation", 8389200
                      "New York", "United States", 8008278

Select all Open in new window

As you can see from the results above, the OUTFILE syntax takes our cities data concatenated to Comma Separated Value (CSV) format and outputs the rows to file with each line terminated by a carriage return and line feed ("\r\n").  Note this was just an example that fits the scope of how we will use OUTFILE in this article, so for a deeper understanding you can/should look at the manual for MySQL online as there are other nice options that could have been used here if our end goal was truly CSV.

3. To_XML Stored Procedure

Now, we know the transportation method will be OUTFILE.  Good!  We are almost there then.  We just need to refresh our memories on where we are headed.  To do that, let's look at the following standard SQL:

SELECT '<s name="xmltest">' AS xml_fragment
                      UNION ALL SELECT '  <t name="cities">'
                      UNION ALL SELECT CONCAT('    <r>', 
                                              '<c name="name">', `name`,'</c>', 
                                              '<c name="country">',`country`,'</c>',
                                              '<c name="population">',`population`,'</c>',
                                              '</r>') 
                      FROM `xmltest`.`cities` 
                      UNION ALL SELECT '  </t>'
                      UNION ALL SELECT '</s>';

Select all Open in new window

Its output will be rows of data to match the lines we want to get in our output XML file:

<s name="xmltest">
                        <t name="cities">
                          <r><c name="name">Mumbai (Bombay)</c>
                      	   <c name="country">India</c>
                      	   <c name="population">10500000</c></r>
                          <r><c name="name">Seoul</c>
                      	   <c name="country">South Korea</c>
                      	   <c name="population">9981619</c></r>
                          <r><c name="name">São Paulo</c>
                      	   <c name="country">Brazil</c>
                      	   <c name="population">9968485</c></r>
                          <r><c name="name">Shanghai</c>
                      	   <c name="country">China</c>
                      	   <c name="population">9696300</c></r>
                          <r><c name="name">Jakarta</c>
                      	   <c name="country">Indonesia</c>
                      	   <c name="population">9604900</c></r>
                          <r><c name="name">Karachi</c>
                      	   <c name="country">Pakistan</c>
                      	   <c name="population">9269265</c></r>
                          <r><c name="name">Istanbul</c>
                      	   <c name="country">Turkey</c>
                      	   <c name="population">8787958</c></r>
                          <r><c name="name">Ciudad de México</c>
                      	   <c name="country">Mexico</c>
                      	   <c name="population">8591309</c></r>
                          <r><c name="name">Moscow</c>
                      	   <c name="country">Russian Federation</c>
                      	   <c name="population">8389200</c></r>
                          <r><c name="name">New York</c>
                      	   <c name="country">United States</c>
                      	   <c name="population">8008278</c></r>
                        </t>
                      </s>

Select all Open in new window

Perfect !

Well, close to perfect.  This at least shows exactly what we want to get, but we need to apply what we spoke about in the Dynamic and Export sections, so without further ado...

To_XML Stored Procedure:

CREATE SCHEMA IF NOT EXISTS util;
                      DROP PROCEDURE IF EXISTS util.to_xml;
                      DELIMITER $$
                      CREATE PROCEDURE util.to_xml(IN schemaname VARCHAR(255), 
                                                   IN tablename VARCHAR(255), 
                                                   IN filename VARCHAR(255), 
                                                   OUT affected_rows INT(11)) 
                      BEGIN
                      
                      -- Build dynamic SQL string using information schema.
                      SELECT CONCAT("SELECT '<s name=\"", TABLE_SCHEMA, "\">'", 
                                    " UNION ALL SELECT '  <t name=\"", TABLE_NAME, "\">'",
                                    " UNION ALL SELECT CONCAT('    <r>",
                                    GROUP_CONCAT('<c name=\"', COLUMN_NAME, "\">',`", 
                                                 COLUMN_NAME, 
                                                 "`,'</c>" SEPARATOR ''),
                                    "</r>') FROM `", TABLE_SCHEMA, "`.`", TABLE_NAME, "`",
                                    " UNION ALL SELECT '  </t>'",
                                    " UNION ALL SELECT '</s>'"
                                    )
                      FROM information_schema.columns
                      WHERE (schemaname IS NULL OR TABLE_SCHEMA = schemaname)
                      AND TABLE_NAME = tablename
                      ORDER BY ORDINAL_POSITION
                      INTO @sqlstring;
                      
                      -- If no table found, skip processing.
                      IF (NOT @sqlstring IS NULL) THEN
                         -- Add the OUTFILE code to SQL string.
                         SET @sqlstring = CONCAT("SELECT SQL_CALC_FOUND_ROWS * INTO OUTFILE '", 
                                                 REPLACE(filename, '\\', '\\\\'), 
                                                 "' LINES TERMINATED BY '\r\n' FROM (", 
                                                 @sqlstring, 
                                                 ") derived");
                      
                         -- Execute dynamic SQL string.
                         PREPARE dySQL FROM @sqlstring;
                         EXECUTE dySQL;
                         DEALLOCATE PREPARE dySQL;
                         
                         -- Save number of rows found;
                         -- minus 4 for root element rows added on.
                         SET affected_rows = FOUND_ROWS() - 4; 
                      ELSE
                         SET affected_rows = 0;
                      END IF;
                      
                      END$$
                      DELIMITER ; 

Select all Open in new window

The code should speak for itself; however, here are some highlights:
 

Test it for yourself:

CALL util.to_xml( 'xmltest', 'cities', 'c:\\temp\\cities.xml', @affected_rows );

Select all Open in new window

Notice, we have a user-defined variable @affected_rows in the call.  This variable will receive the output parameter value, making this subsequent statement valid to get the number of rows inserted into our file.

SELECT @affected_rows;

Select all Open in new window

That's it !  But wait…

4. Bonus: To_XML+

Handling attributes would be nice! (output as elements or attributes)Here is an extension of the original to_xml function in the article, allowing us to output columnar data as attributes instead of elements.  

DROP PROCEDURE IF EXISTS to_xml;
                      DELIMITER $$
                      CREATE PROCEDURE `to_xml`(IN schemaname VARCHAR(255), 
                                                IN tablename VARCHAR(255), 
                                                IN filename VARCHAR(255),
                                                IN xmltype TINYINT(1), 
                                                OUT affected_rows INT(11))
                      BEGIN
                      
                      IF (xmltype = 1) THEN -- columns as elements
                         -- Build dynamic SQL string using information schema.
                         SELECT CONCAT("SELECT '<s name=\"", TABLE_SCHEMA, "\">'", 
                                       " UNION ALL SELECT '  <t name=\"", TABLE_NAME, "\">'",
                                       " UNION ALL SELECT CONCAT('    <r>",
                                       GROUP_CONCAT('<c name=\"', COLUMN_NAME, "\">',`", 
                                                    COLUMN_NAME, 
                                                    "`,'</c>" SEPARATOR ''),
                                       "</r>') FROM `", TABLE_SCHEMA, "`.`", TABLE_NAME, "`",
                                       " UNION ALL SELECT '  </t>'",
                                       " UNION ALL SELECT '</s>'"
                                       )
                         FROM information_schema.columns
                         WHERE (schemaname IS NULL OR TABLE_SCHEMA = schemaname)
                         AND TABLE_NAME = tablename
                         ORDER BY ORDINAL_POSITION
                         INTO @sqlstring;
                      ELSEIF (xmltype = 2) THEN -- columns as attributes
                         -- Build dynamic SQL string using information schema.
                         SELECT CONCAT("SELECT '<s name=\"", TABLE_SCHEMA, "\">'", 
                                       " UNION ALL SELECT '  <t name=\"", TABLE_NAME, "\">'",
                                       " UNION ALL SELECT CONCAT('    <r ",
                                       GROUP_CONCAT(REPLACE(COLUMN_NAME, ' ', '_'), "=\"',`", 
                                                    COLUMN_NAME, 
                                                    "`,'\"" SEPARATOR ' '),
                                       " />') FROM `", TABLE_SCHEMA, "`.`", TABLE_NAME, "`",
                                       " UNION ALL SELECT '  </t>'",
                                       " UNION ALL SELECT '</s>'"
                                       )
                         FROM information_schema.columns
                         WHERE (schemaname IS NULL OR TABLE_SCHEMA = schemaname)
                         AND TABLE_NAME = tablename
                         ORDER BY ORDINAL_POSITION
                         INTO @sqlstring;
                      ELSE -- set SQL string to NULL, which escapes execution
                         SET @sqlstring = NULL;
                      END IF;
                      
                      -- If no table found, skip processing.
                      IF (NOT @sqlstring IS NULL) THEN
                         -- Add the OUTFILE code to SQL string.
                         SET @sqlstring = CONCAT("SELECT SQL_CALC_FOUND_ROWS * INTO OUTFILE '", 
                                                 REPLACE(filename, '\\', '\\\\'), 
                                                 "' LINES TERMINATED BY '\r\n' FROM (", 
                                                 @sqlstring, 
                                                 ") derived");
                      
                         -- Execute dynamic SQL string.
                         PREPARE s1 FROM @sqlstring;
                         EXECUTE s1;
                         DEALLOCATE PREPARE s1;
                         
                         -- Save number of rows found;
                         -- minus 4 for root element rows added on.
                         SET affected_rows = FOUND_ROWS() - 4; 
                      ELSE
                         SET affected_rows = 0;
                      END IF;
                      
                      END $$
                      DELIMITER ;

Select all Open in new window

Note: I have left this open to add other options by utilizing another value for xmltype parameter, which you will notice has been added to the to_xml procedure's signature.  Probably not the most elegant, but I have used a simple IF/ELSEIF/ELSE structure here with a default where @sqlstring = NULL.  Since our code will handle no table being found in the information schema which results in NULL string, I used that here to signify unimplemented options.  Without this, the default value of @sqlstring will likely be empty string or an unexpected value since this is a user-defined variable we had not established using declare.  If we had, we could have set default there and foregone the ELSE portion of the code.

So what is the difference.  Run the following commands and inspect the results:
[step="" title=""]
(1) column data as elements

CALL util.to_xml( 'xmltest', 
                                        'cities', 
                                        'c:\\temp\\cities-elem.xml', 
                                        1, 
                                        @affected_rows );
                      SELECT @affected_rows;

Select all Open in new window

results in 10 rows affected with a cities-elem.xml file like our original element-based to_xml implementation above.

(2) column data as attributes

CALL util.to_xml( 'xmltest', 
                                        'cities', 
                                        'c:\\temp\\cities-attr.xml', 
                                        2, 
                                        @affected_rows );
                      SELECT @affected_rows;

Select all Open in new window

results in 10 rows affected with a cities-attr.xml file like:

<s name="xmltest">
                        <t name="cities">
                          <r name="Mumbai (Bombay)" country="India" population="10500000" />
                          <r name="Seoul" country="South Korea" population="9981619" />
                          <r name="São Paulo" country="Brazil" population="9968485" />
                          <r name="Shanghai" country="China" population="9696300" />
                          <r name="Jakarta" country="Indonesia" population="9604900" />
                          <r name="Karachi" country="Pakistan" population="9269265" />
                          <r name="Istanbul" country="Turkey" population="8787958" />
                          <r name="Ciudad de México" country="Mexico" population="8591309" />
                          <r name="Moscow" country="Russian Federation" population="8389200" />
                          <r name="New York" country="United States" population="8008278" />
                        </t>
                      </s>

Select all Open in new window

hey we have attributes!

(3) unimplemented xmltype -- maybe a mixture (keep reading…hint, hint)

CALL util.to_xml( 'xmltest', 
                                        'cities', 
                                        'c:\\temp\\cities-none.xml', 
                                        3, 
                                        @affected_rows );
                      SELECT @affected_rows;

Select all Open in new window

results in 0 rows affected and no xml file created.
[/step]
While we are intrigued, let's explore some more …

5. Bonus: To_XML++

Even more robust solution! (output both elements and attributes)Yet another extension of our to_xml procedure.  This newly improved version will allow us to take our tabular data and building a mixed XML document containing both elements and attributes.

DROP PROCEDURE IF EXISTS util.to_xml;
                      DELIMITER $$
                      CREATE PROCEDURE util.`to_xml`(IN schemaname VARCHAR(255), 
                                                IN tablename VARCHAR(255), 
                                                IN filename VARCHAR(255),
                                                IN xmltype TINYINT(1),
                                                IN xmlexcpts VARCHAR(255),
                                                OUT affected_rows INT(11))
                      BEGIN
                      
                      -- build xml structure (test for valid table)
                      SELECT CONCAT("SELECT '<s name=\"", TABLE_SCHEMA, "\">'", 
                                   " UNION ALL SELECT '  <t name=\"", TABLE_NAME, "\">'",
                                   " UNION ALL SELECT CONCAT('    <r{{RPLWAttrs}}>", 
                                   "{{RPLWElems}}",
                                   "</r>') FROM `", TABLE_SCHEMA, "`.`", TABLE_NAME, "`",
                                   " UNION ALL SELECT '  </t>'",
                                   " UNION ALL SELECT '</s>'"
                                   )
                      FROM information_schema.columns
                      WHERE (schemaname IS NULL OR TABLE_SCHEMA = schemaname)
                      AND TABLE_NAME = tablename
                      LIMIT 1
                      INTO @sqlstring;
                      
                      -- If no table found, skip processing.
                      IF (NOT @sqlstring IS NULL) THEN 
                         -- switch xmltype as we know table is valid now
                         IF (xmltype = 1) THEN -- columns as elements
                            SELECT GROUP_CONCAT('<c name=\"', COLUMN_NAME, "\">',`", 
                                                COLUMN_NAME, 
                                                "`,'</c>" SEPARATOR '')
                            FROM information_schema.columns
                            WHERE (schemaname IS NULL OR TABLE_SCHEMA = schemaname)
                            AND TABLE_NAME = tablename
                            ORDER BY ORDINAL_POSITION
                            INTO @elements;
                            
                            SET @attributes = NULL;
                         ELSEIF (xmltype = 2) THEN -- columns as attributes
                            SELECT GROUP_CONCAT(REPLACE(COLUMN_NAME, ' ', '_'), "=\"',`", 
                                                COLUMN_NAME, 
                                                "`,'\"" SEPARATOR ' ')
                            FROM information_schema.columns
                            WHERE (schemaname IS NULL OR TABLE_SCHEMA = schemaname)
                            AND TABLE_NAME = tablename
                            ORDER BY ORDINAL_POSITION
                            INTO @attributes;
                            
                            SET @elements = NULL;
                         ELSEIF (xmltype = 3) THEN -- mixed mode, unspecified as elements
                            -- select exceptions as attributes
                            SELECT GROUP_CONCAT(REPLACE(COLUMN_NAME, ' ', '_'), "=\"',`", 
                                                COLUMN_NAME, 
                                                "`,'\"" SEPARATOR ' ')
                            FROM information_schema.columns
                            WHERE (schemaname IS NULL OR TABLE_SCHEMA = schemaname)
                            AND TABLE_NAME = tablename
                            AND FIND_IN_SET(COLUMN_NAME, COALESCE(xmlexcpts, '')) > 0
                            ORDER BY ORDINAL_POSITION
                            INTO @attributes;
                            
                            -- select non-exceptions as elements
                            SELECT GROUP_CONCAT('<c name=\"', COLUMN_NAME, "\">',`", 
                                                COLUMN_NAME, 
                                                "`,'</c>" SEPARATOR '')
                            FROM information_schema.columns
                            WHERE (schemaname IS NULL OR TABLE_SCHEMA = schemaname)
                            AND TABLE_NAME = tablename
                            AND FIND_IN_SET(COLUMN_NAME, COALESCE(xmlexcpts, '')) = 0
                            ORDER BY ORDINAL_POSITION
                            INTO @elements;
                         ELSEIF (xmltype = 4) THEN -- mixed mode, unspecified as attributes
                            -- select exceptions as elements
                            SELECT GROUP_CONCAT('<c name=\"', COLUMN_NAME, "\">',`", 
                                                COLUMN_NAME, 
                                                "`,'</c>" SEPARATOR '')
                            FROM information_schema.columns
                            WHERE (schemaname IS NULL OR TABLE_SCHEMA = schemaname)
                            AND TABLE_NAME = tablename
                            AND FIND_IN_SET(COLUMN_NAME, COALESCE(xmlexcpts, '')) > 0
                            ORDER BY ORDINAL_POSITION
                            INTO @elements;
                            
                            -- select non-exceptions as attributes
                            SELECT GROUP_CONCAT(REPLACE(COLUMN_NAME, ' ', '_'), "=\"',`", 
                                                COLUMN_NAME, 
                                                "`,'\"" SEPARATOR ' ')
                            FROM information_schema.columns
                            WHERE (schemaname IS NULL OR TABLE_SCHEMA = schemaname)
                            AND TABLE_NAME = tablename
                            AND FIND_IN_SET(COLUMN_NAME, COALESCE(xmlexcpts, '')) = 0
                            ORDER BY ORDINAL_POSITION
                            INTO @attributes;
                         END IF;
                      
                         IF (xmltype BETWEEN 1 AND 4) THEN       
                            -- Add the OUTFILE code to SQL string.
                            -- Replace placeholders with @elements | @attributes
                            SET @sqlstring = CONCAT("SELECT SQL_CALC_FOUND_ROWS * INTO OUTFILE '", 
                                                    REPLACE(filename, '\\', '\\\\'), 
                                                    "' LINES TERMINATED BY '\r\n' FROM (", 
                                                    REPLACE(
                                                       REPLACE(@sqlstring, 
                                                               '{{RPLWAttrs}}', 
                                                               COALESCE(CONCAT(' ', @attributes), '')
                                                       ), '{{RPLWElems}}',
                                                       COALESCE(@elements, '')
                                                    ), ") derived");
                      
                            -- Execute dynamic SQL string.
                            PREPARE s1 FROM @sqlstring;
                            EXECUTE s1;
                            DEALLOCATE PREPARE s1;
                         
                            -- Save number of rows found;
                            -- minus 4 for root element rows added on.
                            SET affected_rows = FOUND_ROWS() - 4;
                         END IF;
                      END IF;
                      
                      SET affected_rows = COALESCE(affected_rows, 0);
                      
                      END $$
                      DELIMITER ;

Select all Open in new window

Again, I have commented the code to the point, it should speak well enough for itself; however, (as usual) please feel free to comment below if more explanation is necessary.

So what is the difference.  Run the following commands and inspect the results:
[step="" title=""]
(1) column data as elements

CALL util.to_xml( 'xmltest', 
                                        'cities', 
                                        'c:\\temp\\cities-1.xml', 
                                        1, 
                                        null,
                                        @affected_rows );
                      SELECT @affected_rows; 

Select all Open in new window

results in 10 rows affected with a cities-1.xml file that is exactly the same as cities-elem.xml file.

(2) column data as attributes

CALL util.to_xml( 'xmltest', 
                                        'cities', 
                                        'c:\\temp\\cities-2.xml', 
                                        2, 
                                        null,
                                        @affected_rows );
                      SELECT @affected_rows; 

Select all Open in new window

results in 10 rows affected with a cities-2.xml file that is similar to cities-attr.xml.

<r name="Mumbai (Bombay)" country="India" population="10500000"></r>

Select all Open in new window

(instead of)

<r name="Mumbai (Bombay)" country="India" population="10500000" />

Select all Open in new window

(3) column data as elements by default with exceptions as attributes

CALL util.to_xml( 'xmltest', 
                                        'cities', 
                                        'c:\\temp\\cities-3.xml', 
                                        3, 
                                        'name',
                                        @affected_rows );
                      SELECT @affected_rows; 

Select all Open in new window

results in 10 rows affected with a cities-3.xml file like:

<s name="xmltest">
                        <t name="cities">
                          <r name="Mumbai (Bombay)">
                             <c name="country">India</c>
                             <c name="population">10500000</c></r>
                          <r name="Seoul">
                             <c name="country">South Korea</c>
                             <c name="population">9981619</c></r>
                          <r name="São Paulo">
                             <c name="country">Brazil</c>
                             <c name="population">9968485</c></r>
                          <r name="Shanghai">
                             <c name="country">China</c>
                             <c name="population">9696300</c></r>
                          <r name="Jakarta">
                             <c name="country">Indonesia</c>
                             <c name="population">9604900</c></r>
                          <r name="Karachi">
                             <c name="country">Pakistan</c>
                             <c name="population">9269265</c></r>
                          <r name="Istanbul">
                             <c name="country">Turkey</c>
                             <c name="population">8787958</c></r>
                          <r name="Ciudad de México">
                             <c name="country">Mexico</c>
                             <c name="population">8591309</c></r>
                          <r name="Moscow">
                             <c name="country">Russian Federation</c>
                             <c name="population">8389200</c></r>
                          <r name="New York">
                             <c name="country">United States</c>
                             <c name="population">8008278</c></r>
                        </t>
                      </s>

Select all Open in new window

Note: xmlexcpts parameter, though only shown here as a singular value, expects a comma delimited string and makes good use of the FIND_IN_SET() function of MySQL.  This function works nicely here with the premise that column names do NOT contain commas.  If for some reason you need to support commas, the trick would be to replace commas that are part of column names in the xmlexcpts CSV passed with some character like underscore and use this same character to replace commas on the COLUMN_NAME values pulled from the information schema.

To help illustrate, give this call a try:

CALL util.to_xml('xmltest',
                                        'cities', 
                                        'c:\\temp\\cities-3b.xml', 
                                        3, 
                                        'country,name',
                                        @affected_rows );

Select all Open in new window

(look...what we just did!)

<s name="xmltest">
                        <t name="cities">
                          <r name="Mumbai (Bombay)" country="India"><c name="population">10500000</c></r>
                          <r name="Seoul" country="South Korea"><c name="population">9981619</c></r>
                          <r name="São Paulo" country="Brazil"><c name="population">9968485</c></r>
                          <r name="Shanghai" country="China"><c name="population">9696300</c></r>
                          <r name="Jakarta" country="Indonesia"><c name="population">9604900</c></r>
                          <r name="Karachi" country="Pakistan"><c name="population">9269265</c></r>
                          <r name="Istanbul" country="Turkey"><c name="population">8787958</c></r>
                          <r name="Ciudad de México" country="Mexico"><c name="population">8591309</c></r>
                          <r name="Moscow" country="Russian Federation"><c name="population">8389200</c></r>
                          <r name="New York" country="United States"><c name="population">8008278</c></r>
                        </t>
                      </s>

Select all Open in new window

(4) column data as attributes by default with exceptions as elements

CALL util.to_xml( 'xmltest', 
                                        'cities', 
                                        'c:\\temp\\cities-4.xml', 
                                        4, 
                                        'name',
                                        @affected_rows );
                      SELECT @affected_rows; 

Select all Open in new window

results in 10 rows affected with a cities-4.xml file like:

<s name="xmltest">
                        <t name="cities">
                          <r country="India" population="10500000"><c name="name">Mumbai (Bombay)</c></r>
                          <r country="South Korea" population="9981619"><c name="name">Seoul</c></r>
                          <r country="Brazil" population="9968485"><c name="name">São Paulo</c></r>
                          <r country="China" population="9696300"><c name="name">Shanghai</c></r>
                          <r country="Indonesia" population="9604900"><c name="name">Jakarta</c></r>
                          <r country="Pakistan" population="9269265"><c name="name">Karachi</c></r>
                          <r country="Turkey" population="8787958"><c name="name">Istanbul</c></r>
                          <r country="Mexico" population="8591309"><c name="name">Ciudad de México</c></r>
                          <r country="Russian Federation" population="8389200"><c name="name">Moscow</c></r>
                          <r country="United States" population="8008278"><c name="name">New York</c></r>
                        </t>
                      </s> 

Select all Open in new window

(5) unimplemented xmltype -- have fun!!

CALL util.to_xml( 'xmltest', 
                                        'cities', 
                                        'c:\\temp\\cities-5.xml', 
                                        5, 
                                        'name',
                                        @affected_rows );
                      SELECT @affected_rows; 

Select all Open in new window

results in 0 rows affected and no xml file created.
[/step]
The key improvement here comes from not only the additional implementation to handle 3 and 4 values of xmltype but also to receive in a listing of XML tags that should be treated differently (exceptions) from the others.  Therefore, the basic principle is that option 3 with no exceptions is option 1 and, likewise, 4 with no xmlexcpts equates to a 2.  Therefore, a streamlined implementation could be reduced back to only 2 options; however, would require "caller" to always be aware of sending NULL or '' for xmlexcpts when behavior of original 1|2 xmltype choice is desired.

Conclusion

Hopefully the above iterations through our to_xml procedure helps to drive home the flexibility and the benefit of why we would want to learn yet another method to export XML from MySQL.  I think you would agree that the command line tools are great, but being able to do this quickly with ability to customize from SQL syntax is very powerful and handy.  

With that said, there is definitely a lot more that can be done with this, so please feel free to play and customize to your liking.  The key value of this solution as I said above is that it allows you control over the structure.  During my testing for the article, one thought I had was to utilize the key column usage table from information schema to determine the primary key(s) and put those in attributes on the row (r) node and then add the other columns of data as column elements.

Therefore, you can play with this yourself and find all sorts of neat things to do.  As an exercise, think about how you would handle NULL values in a specific column.  Easy enough to use COALESCE() to replace NULLs with a default value like '', right?  But what if the datatype is numeric?  How do we figure that out with what I have already shown here?  Play around.  There are a lot of interesting capabilities you may find that are at your fingertips by searching the metadata contained in the information schema (oh -- hint, hint).

Have fun as the whole point of creating this procedure was to gain the power and convenience of the mysql and mysqldump commands XML export while retaining the flexibility and control of the hand written method, so please make this your own by exploring improvements like how XML Schema (XSD) and/or XML Stylesheets (XSL) stronger adherence to a specific XML contract is required.  For example, notice how compact the attribute version is in this simple case.  In another case where say, I want to use CDATA or have a mix, this may not be optimal.  But again, we have the control!

I have given you a lot of tools between the dynamic SQL, OUTFILE, et al.  Use your  new found control and flourish with this tool on your own.  

Acknowledgements
Before we go, I would like to thank the Page Editors as usually, but special thanks must be given to EE Expert, aikimark, for his questions and probing during the authoring of this article.  Without this input, the article may not have all the value you see here; however, please note any mistakes I may have missed in final review of this are all my own.

Additionally, thank you, all, for reading.  

Best regards and happy coding,

Kevin C. Cross, Sr. (mwvisa1)

References

Related Articles
A MySQL Tidbit: In-line CSV Parsing (Using XML) > https://www.experts-exchange.com/Database/MySQL/A_3622.html
A MySQL Tidbit: In-line XML Parsing > https://www.experts-exchange.com/Database/MySQL/A_3574.html
Using XML in MySQL 5.1 and 6.0 > http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html

Related MySQL Documentation
Group By Functions > http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html
Information_Schema Tables > http://dev.mysql.com/doc/refman/5.1/en/information-schema.html
Select … Into Outfile > http://dev.mysql.com/doc/refman/5.1/en/select.html
SQL Calc Found Rows > http://dev.mysql.com/doc/refman/5.1/en/information-functions.html
String Functions > http://dev.mysql.com/doc/refman/5.1/en/string-functions.html
User Variables > http://dev.mysql.com/doc/refman/5.1/en/user-variables.html
XML Functions > http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html


 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you found this article helpful, please click the Yes button after the question just below. This will give me (the author) a few points and might encourage me to write more articles.

If you didn't or otherwise feel the need to vote No, please first leave a comment to give me a chance to answer and perhaps to improve this article.

Thank you!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=