Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Dec 2012
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Going from XML to SQL

    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.
    Last edited by TheLostOne; 12-04-2012 at 12:06 AM. Reason: Solved!

  2. #2
    Master Coder sunfighter's Avatar
    Join Date
    Jan 2011
    Location
    Washington
    Posts
    6,064
    Thanks
    30
    Thanked 833 Times in 831 Posts
    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 />";
            }
        }
    }
    ?>

  3. Users who have thanked sunfighter for this post:

    TheLostOne (12-04-2012)

  4. #3
    New to the CF scene
    Join Date
    Dec 2012
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Now That is What I Call Service

    Thank you for helping! It works.

  5. #4
    New to the CF scene
    Join Date
    Dec 2012
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks again for that, I needed the help.
    Last edited by TheLostOne; 12-04-2012 at 03:41 AM.

  6. #5
    Master Coder sunfighter's Avatar
    Join Date
    Jan 2011
    Location
    Washington
    Posts
    6,064
    Thanks
    30
    Thanked 833 Times in 831 Posts
    No problem, glad to be of service.


 

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •