Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have written a script which is fetching data from database.

Now I want to enhance that script to create a xml file with data which is return by database.

How may i convert that string to xml file in shell script.

script.sh

#!/bin/sh
echo 'SELECT TABLE_NAME AS "Table Name", table_rows AS "Quant of Rows", ROUND((data_length + index_length)/1024/1024,2) AS "Total Size Mb" FROM information_schema.TABLES WHERE information_schema.TABLES.table_schema="database_name";' | mysql -u root -pmysql

data getting from database

"Table Name\tQuant of Rows\tTotal Size Mb\ntable_1\t2\t0.02\ntable_2\t1\t0.02\ntable_3\t142\t0.02\ntable_4\t50839\t5.03\ntable_5\t16573\t5.13\ndlr\t0\t0.02\ntable_6\t6\t0.02\ntable_7\t0\t0.03\ntable_8\t2\t0.08\ntable_9\t4\t0.02\n"

I want to write some logic into script which convert this string to xml file.

How may i do that ? What are the preferred way to handle this scenario ?

share|improve this question
    
While it would certainly be possible to do this with a shell script, I would recommend using another scripting language for this, like Perl, Python or Ruby, as all of these have libraries that make constructing XML much easier. –  Sven Mar 22 '11 at 12:12
    
Please do not cross-post. –  Dennis Williamson Mar 23 '11 at 2:50

2 Answers 2

You can do this with awk. awk -F\t will tell awk to use the tab character as the field separator. $1 would become Table Name, $2 would become Quant of Rows, and so on. Also, $0 refers to the entire line, while $NF is always the last field.

cat data | awk -F\t '{printf("<xml-tag-1>%s</xml-tag-1>\n<xml-tag-2>%s</xml-tag2>\n", $1, $2 )}'

This will create xml by hand for the first 2 fields.

share|improve this answer
up vote 1 down vote accepted
#!/bin/sh
mysql -u root -pmysql --xml > /home/test/Desktop/temp.xml << eof
SELECT TABLE_NAME AS "Table Name", table_rows AS "Quant of Rows", ROUND((data_length + index_length)/1024/1024,2) AS "Total Size Mb"  FROM information_schema.TABLES WHERE information_schema.TABLES.table_schema='database_name';
eof
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.