Resolved Going from XML to SQL

TheLostOne · 12-02-2012, 08:37 AM

I have been trying my hand lately at trying to convert an xml file to SQL complient text file. I was told about XSLT and have been trying very hard but, as my username reveals, I am lost

I started researching and tried modifying the code found here but to no avail. Here is what my modification came out to look like right now:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" 
	xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
	xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xsl:template match="/">
<html>
	<head />
	<body title="The Holy Bible">
		<xsl:for-each select="bible">
			<p>
				<xsl:for-each select="book">
					<xsl:if test="position( )=1">
						<table border="1">
							<thead>
								<tr>
										<td>Book</td>
										<td>Chapter</td>
										<td>Verse</td>
								</tr>
							</thead>
							<tbody>
								<xsl:for-each select="../book">
									<tr>
										<td>
											<xsl:for-each select="@name">
												<xsl:value-of select="." />
											</xsl:for-each>
										</td>
									</tr>
								</xsl:for-each>
							</tbody>
						</table>
					</xsl:if>
				</xsl:for-each>
			</p>
		</xsl:for-each>
	</body>
</html>
</xsl:template>
</xsl:stylesheet>

I really do not know what to do about this for the code is not working as I would like. I want to output to look something like this:

Code:

INSERT INTO "bibletable" ("book", "chapter", "verse", "text") VALUES ('Genesis', 1, 1, 'In the beginning . . .');
INSERT INTO "bibletable" ("book", "chapter", "verse", "text") VALUES ('Genesis', 1, 2, 'And the earth was without form . . .');
...
...
INSERT INTO "bibletable" ("book", "chapter", "verse", "text") VALUES ('Revelation', 22, 20, 'He which testifieth these things . . .');
INSERT INTO "bibletable" ("book", "chapter", "verse", "text") VALUES ('Revelation', 22, 21, 'The grace of our Lord . . .');

Now if I cannot get it exactly like this I at least want to get as close as possible and I will use regex to do the rest.

Can you please offer me some advice? I would really like it.

If you want more info just say so. But, I hope this is sufficient to deliver the message.

sunfighter · 12-03-2012, 05:52 PM

PHP Code:


			
<?php
$xml = simplexml_load_file("bible.xml");
$book = $xml->book;

for ($i=0; $i<count($book); $i++)
{
    $chapter = $xml->book[$i]->chapter;
    for($j = 0; $j < count($chapter); $j++)
    {
        $verse = $xml->book[$i]->chapter[$j]->verse;
        for($l = 0; $l < count($verse); $l++)
        {
            echo "INSERT INTO \"bibletable\" (\"book\", \"chapter\", \"verse\", \"text\") VALUES ('";
            echo $book[$i]->attributes()->name . "', '";
            echo $book[$i]->chapter[$j]->attributes()->name . "', '";
            echo $book[$i]->chapter[$j]->verse[$l]->attributes()->name . "', '";
            echo $book[$i]->chapter[$j]->verse[$l]. "');";
            echo "<br />";
        }
    }
}
?>

TheLostOne · 12-03-2012, 11:05 PM

Thank you for helping! It works.

TheLostOne · 12-03-2012, 11:08 PM

Thanks again for that, I needed the help.

sunfighter · 12-04-2012, 03:50 PM

No problem, glad to be of service.