<

A MySQL Tidbit: Dynamic Export To_XML Stored Procedure

Published on
17,806 Points
7,805 Views
5 Endorsements
Last Modified:
Awarded
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

Dynamic
Export
To_XML Stored Procedure

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

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

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

Open in new window

(for example)
SET @sqlstring = 'SELECT * FROM `xmltest`.`cities`';
PREPARE dySQL FROM @sqlstring;

Open in new window


(2) Execute -- runs the prepared SQL statement using any parameters passed.
EXECUTE stmt_name [USING @var_name [, @var_name] ...]

Open in new window

(for example)
EXECUTE dySQL;

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

Open in new window

(for example)
DEALLOCATE PREPARE dySQL;

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

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;

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`;

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

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

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>

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 ; 

Open in new window


The code should speak for itself; however, here are some highlights:
 
Using the INFORMATION_SCHEMA columns table and the power of CONCAT()/GROUP_CONCAT(), we first build the portion of our dynamic SQL string that matches the standard UNION query shown earlier.
Using a simple SET command, the generated UNION from above is placed into a derived table with outer SELECT using INTO OUTFILE syntax.
Using prepared statements, the final dynamic string is executed and our file is created.  Note that OUTFILE requires FILE permissions on the host server as well as limits output to non-existing files; therefore, you will NOT be able to overwrite the same file which aside from flexibility is the reason behind passing file name as a parameter.
Lastly, through a feature of MySQL, SQL_CALC_FOUND_ROWS, that mimics @@Rowcount in T-SQL, we return to the caller the number of exported data rows.

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

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;

Open in new window

««Performance Metrics»»
If you are curious on performance, this typically runs less than 1 second.  To benchmark and put things in perspective for you, I used a table with 150,010 rows of data and its 19,319,323 characters / 19,619,341 bytes (i.e., 18 megabytes) were written to its XML file in 1.217 seconds.

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 ;

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;

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;

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>

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;

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 ;

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; 

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; 

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>

Open in new window

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

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; 

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>

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

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>

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; 

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> 

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; 

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) > http://www.experts-exchange.com/Database/MySQL/A_3622.html
A MySQL Tidbit: In-line XML Parsing > http://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!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
5
Comment
Author:Kevin Cross
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 46

Expert Comment

by:aikimark
@Kevin

Excellent.  I can't wait to see what option 5 might be.
0
 
LVL 60

Author Comment

by:Kevin Cross
Thanks, Mark!  

I am hoping this is a good start for everyone and it will be worth the wait while I finalize my thoughts on some additional items along the line of this subject matter.  Let's just say, there may be a follow-up article having to do with importing and an "option 5" (more advanced XML processing) sometime in the future! *smile*

I appreciate the votes from you and all who feel so inclined.

Definitely good to know that others actually read my dribble...

Regards,

Kevin
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Join & Write a Comment

In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month