Re: [xsl] Copying XML nodes into SQL file

Subject: Re: [xsl] Copying XML nodes into SQL file
From: "Jay Bryant" <jay@xxxxxxxxxxxx>
Date: Mon, 31 Mar 2008 14:38:10 -0500
Hi, Alan,

I cleaned up the issues in your input (it wasn't well-formed), so that I had the following for input:

<?xml version="1.0" encoding="UTF-8"?>
<content>
<p class="news-content-heading">Headline Goes here </p>
<p>November 25, 2006</p>
<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua</p>
<p>Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat</p>
<p>
<ul>
<li>item 1</li>
<li>item 2</li>
</ul>
</p>
<p>Excepteur sint occaecat cupidatat non proident, <a href="../contact/index.html">sunt in culpa qui</a> officia deserunt mollit anim id est laborum</p>
</content>


Then I ran it through the following transform (using Saxin 9):

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xsl:transform version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform";>


<xsl:output method="html" indent="no"/>

<xsl:template match="/"><xsl:apply-templates/></xsl:template>

<xsl:template match="content">
<xsl:text>INSERT INTO whatsNew (approved, title, description, publishDate) VALUES </xsl:text><xsl:text>(1, </xsl:text><xsl:value-of select="//p[@class='news-content-heading']"/><xsl:text>, </xsl:text>
<xsl:copy-of select="node()|@*"/>
<xsl:text>, </xsl:text>TODAY<xsl:text>,1, 1)</xsl:text>
</xsl:template>


</xsl:transform>

and got the following output:

INSERT INTO whatsNew (approved, title, description, publishDate) VALUES (1, Headline Goes here ,
<p class="news-content-heading">Headline Goes here </p>
<p>November 25, 2006</p>
<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua</p>
<p>Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat</p>
<p>
<ul>
<li>item 1</li>
<li>item 2</li>
</ul>
</p>
<p>Excepteur sint occaecat cupidatat non proident, <a href="../contact/index.html">sunt in culpa qui</a> officia deserunt mollit anim id est laborum</p>
, TODAY,1, 1)


I didn't have your date template, so I just stuck in "TODAY".

I bet you were trying to set the output method to text, but that kills the markup. Setting the output to HTML and the indent to no lets your HTML markup come through.

HTH

Jay Bryant
Bryant Communication Services
http://www.bryantcs.com/

----- Original Message ----- From: "Alan Gardner" <alan@xxxxxxxxxxxxxxxxxxx>
To: "XSL Mulberry list" <xsl-list@xxxxxxxxxxxxxxxxxxxxxx>
Sent: Monday, March 31, 2008 2:01 PM
Subject: [xsl] Copying XML nodes into SQL file



I have a couple hundred xml files that contain html content that I need to transform into a sql file (INSERT statement) to be imported into a database. I'm using a simple xsl:copy element to copy all of the xml's contents (including all html tags and it's attributes, values) into a single insert line, but the problem arises that I'm getting an insert line for each node in the file. If I make the template match value to be "/content" then I get one insert line (which is desired), but I lose all the html tags/attributes/values.

My xsl:
<xsl:template match="/content/node()|@*">
<xsl:text>INSERT INTO whatsNew (approved, title, description, publishDate) VALUES </xsl:text><xsl:text>(1, </xsl:text><xsl:value-of select="//p[@class='news-content-heading']"/><xsl:text>, </xsl:text>
<xsl:copy>
<xsl:apply-templates select="node()|@*"/>
</xsl:copy>
<xsl:text>, </xsl:text><xsl:call-template name="date"><xsl:with-param name="filename"><xsl:value-of select="$fileName"/></xsl:with-param></ xsl:call-template><xsl:text>,1, 1)</xsl:text>
</xsl:template>


My sample XML file:
<?xml version="1.0" encoding="UTF-8"?>
<content>
<p class="news-content-heading">Headline Goes here </p>
<p>November 25, 2006<p/>
<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua</p>
<p>Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat</p>
<p>
<ul>
<li>item 1</li>
<li>item 2</li>
</ul>
</p>
<p>Excepteur sint occaecat cupidatat non proident, <a href="../ contact/index.html">sunt in culpa qui</a> officia deserunt mollit anim id est laborum</p>
</content>


Suggestions?

Alan


Alan Gardner Senior Web Designer

Utah Interactive, LLC
30 East Broadway, Suite 300
Salt Lake City, UT 84111

801-983-8424 (office)
801-698-0499 (mobile)
alan@xxxxxxxxxxxxxxxxxxx

Current Thread