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-03-2012 at 11:06 PM. Reason: Solved!

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,096
    Thanks
    23
    Thanked 594 Times in 593 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 />";
            }
        }
    }
    ?>

  • Users who have thanked sunfighter for this post:

    TheLostOne (12-04-2012)

  • #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.

  • #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 02:41 AM.

  • #5
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,096
    Thanks
    23
    Thanked 594 Times in 593 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
    •