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'
);
(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);
[/step]
PREPARE stmt_name FROM preparable_stmt
(for example)
SET @sqlstring = 'SELECT * FROM `xmltest`.`cities`';
PREPARE dySQL FROM @sqlstring;
EXECUTE stmt_name [USING @var_name [, @var_name] ...]
(for example)
EXECUTE dySQL;
{DEALLOCATE | DROP} PREPARE stmt_name
(for example)
DEALLOCATE PREPARE dySQL;
[/step]
mysql> SELECT CONCAT(
-> '\n<cities>',
-> GROUP_CONCAT(
-> '\n\t<city name="', name, '" population="', population, '"/>'
-> SEPARATOR ''
-> ),
-> '\n</cities>'
-> ) AS xmldoc
-> FROM cities\G
SELECT COLUMN_NAME
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'xmltest'
AND TABLE_NAME = 'cities'
ORDER BY ORDINAL_POSITION;
SELECT CONCAT('"', `name`, '", "', `country`, '", ', `population`)
INTO OUTFILE 'c:\\temp\\cities.txt' LINES TERMINATED BY '\r\n'
FROM `xmltest`.`cities`;
(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 '<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>';
<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>
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 ;
CALL util.to_xml( 'xmltest', 'cities', 'c:\\temp\\cities.xml', @affected_rows );
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;
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 ;
CALL util.to_xml( 'xmltest',
'cities',
'c:\\temp\\cities-elem.xml',
1,
@affected_rows );
SELECT @affected_rows;
results in 10 rows affected with a cities-elem.xml file like our original element-based to_xml implementation above.
CALL util.to_xml( 'xmltest',
'cities',
'c:\\temp\\cities-attr.xml',
2,
@affected_rows );
SELECT @affected_rows;
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>
hey we have attributes!
CALL util.to_xml( 'xmltest',
'cities',
'c:\\temp\\cities-none.xml',
3,
@affected_rows );
SELECT @affected_rows;
results in 0 rows affected and no xml file created.
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 ;
CALL util.to_xml( 'xmltest',
'cities',
'c:\\temp\\cities-1.xml',
1,
null,
@affected_rows );
SELECT @affected_rows;
results in 10 rows affected with a cities-1.xml file that is exactly the same as cities-elem.xml file.
CALL util.to_xml( 'xmltest',
'cities',
'c:\\temp\\cities-2.xml',
2,
null,
@affected_rows );
SELECT @affected_rows;
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>
(instead of)
<r name="Mumbai (Bombay)" country="India" population="10500000" />
CALL util.to_xml( 'xmltest',
'cities',
'c:\\temp\\cities-3.xml',
3,
'name',
@affected_rows );
SELECT @affected_rows;
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>
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.
CALL util.to_xml('xmltest',
'cities',
'c:\\temp\\cities-3b.xml',
3,
'country,name',
@affected_rows );
(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>
CALL util.to_xml( 'xmltest',
'cities',
'c:\\temp\\cities-4.xml',
4,
'name',
@affected_rows );
SELECT @affected_rows;
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>
CALL util.to_xml( 'xmltest',
'cities',
'c:\\temp\\cities-5.xml',
5,
'name',
@affected_rows );
SELECT @affected_rows;
results in 0 rows affected and no xml file created.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Commented:
Excellent. I can't wait to see what option 5 might be.
Author
Commented: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