Posts Tagged ‘XML’

Create XML Document with Google Spreadsheet, Gadget and HTML Tidy

May 4, 2010

WHO:  The computer user operates a wireless phone business.
WHAT:  The user lists uploaded videos on an external, business web page.
WHERE:  The videos are streamed via YouTube.  But, the XML document is on the user’s shared web server.
WHEN:  There are time saving RSS feed tools already in place at YouTube.  The method described in this post could be outfitted to match the XML output from YouTube.
HOW:  Here is a three (3) step method to convert a Google Spreadsheet to XML…

  1. Create a new spreadsheet in Google Docs.  Fill in one list item per row.
  2. Insert the XML Google Gadget.  Select XML from the dropdown list.  Adjust the cell ranges in the gadget.
  3. Add “<?xml version=”1.0″ encoding=”UTF-8″ ?>” (without quotes) at the beginning of the new XML document.  Use HTML Tidy on the resulting XML, if desired.

WHY:  You can upload documents (including spreadsheets and CSV files) to Google Docs.  Using the method described above, a Microsoft Excel spreadsheet (for example) can be converted into an XML file for an RSS feed (with SimpleXML).

MySQL Data to PHP Array; Save to XML File

April 14, 2010

RE: CMS, mySQL, PHP, RSS, Wakaba, XML

<?php
// Connect to database.
$host = "localhost";
$user = "username";
$pass = "password";
$database = "database";
$connection = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $connection) or die("Could not find database.");
// Query data.
$result = mysql_query("SELECT columnX, columnY FROM table");
// Create new XML document.
// [Source: Read and write XML with PHP]
$document = new DOMDocument();
$documen-->formatOutput = true;
// Define root (primary, topmost, initial) node of document.
$root = $document->createElement( "feed" );
$document->appendChild( $root );
// Generate *messages* array from data within specified, table column(s).
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
$feed = array("item" => array(columnX => $row['columnX'], columnY => $row['columnY']));
// Process *columnX* data (as 'text') for problematic characters.
// [Source: Output mySQL data as XML with PHP]
$row['columnX'] = strip_tags($row['columnX']);
$row['columnX'] = str_replace("&", "&", $row['columnX']);
$row['columnX'] = str_replace("<", "<", $row['columnX']);
$row['columnX'] = str_replace(">", ">", $row['columnX']);
$row['columnX'] = str_replace("\"", """, $row['columnX']);
$text = $row['columnX'];
// Uncomment the two(2) lines below to send feed to browser.
// echo $feed["item"][columnX] . "";
// echo $feed["item"][columnY] . "";
// Define branch (secondary, nested, subsequent) node(s) of document.
$branch = $document->createElement( "item" );
$columnX = $document->createElement( "columnX" );
$columnX->appendChild(  $document->createTextNode( $feed["item"][columnX] )  );
$branch->appendChild( $columnX );
$columnY = $document->createElement( "columnY" );
$columnY->appendChild(  $document->createTextNode( $text )  );
$branch->appendChild( $columnY );
$root->appendChild( $branch );  }
mysql_close($connection);
// Uncomment the following one(1) line below to output raw data of this script to browser.
// echo $document->saveXML();
// Confirm process completion.
echo "An XML file has been generated from your query.";
// Write XML data to file.  $document->save("index.xml")  ?>

Legend:

username, password, database, table: Connect to mySQL database.
columnXcolumnY: Select tables from database.
feed, item: Form XML document structure.
text: Strip HTML tags and replace special characters.