RE: RE: [xsl] Malformed (??) XML and XML 2 SQL XSLT transformation

[cknell@xxxxxxxxxx]

Yes, I didn't read you post carefully. This stylesheet will do exactly what you want with these exceptions. I have left in some line breaks for readability, you may have to remove them, or you may not, depending on the requirements of the engine executing the SQL. Also, I have UTF-8 encoding and that is different from yours. Given the structure of your XML, a more general solution is difficult without first re-structuring the document to add the hierarchy that would make it easy. This one is specfic for this INSERT given the numbers of columns you specify in the INSERT statement.

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform";>
	<xsl:output method="text" indent="yes" encoding="UTF-8" />
	<xsl:strip-space elements="*" />

	<xsl:template match="/Orders/Customer/Template">
<xsl:for-each select="Fields[@FieldName='User Login ID']">INSERT INTO orders("<xsl:value-of select="@FieldName" />,
"<xsl:value-of select="normalize-space(following-sibling::*[1]/@FieldName)"/>",
"<xsl:value-of select="normalize-space(following-sibling::*[2]/@FieldName)"/>",
"<xsl:value-of select="normalize-space(following-sibling::*[3]/@FieldName)"/>",
"<xsl:value-of select="normalize-space(following-sibling::*[4]/@FieldName)"/>")
VALUES("<xsl:value-of select="*" />",
"<xsl:value-of select="normalize-space(following-sibling::*[1])"/>",
"<xsl:value-of select="normalize-space(following-sibling::*[2])"/>",
"<xsl:value-of select="normalize-space(following-sibling::*[3])"/>",
"<xsl:value-of select="normalize-space(following-sibling::*[4])"/>");
</xsl:for-each>
	</xsl:template>

</xsl:stylesheet>
--
Charles Knell
cknell@xxxxxxxxxx - email



-----Original Message-----
From:     Guillaume LECERF <guillaume.lecerf@xxxxxxxxxxxxxx>
Sent:     Fri, 14 Nov 2003 16:07:57 +0100
To:       xsl-list@xxxxxxxxxxxxxxxxxxxxxx
Subject:  RE: [xsl] Malformed (??) XML and XML 2 SQL XSLT transformation

Thanks for your answer, but my problem is that I need to generate TWO 
different insert instructions, each orders begginng with the <Fields 
FieldName="User Login ID"> tag.

At 09:58 14/11/2003 -0500, you wrote:
> This stylesheet will do it. The lines are longer than I would like, but 
> were necessary in order to eliminate unwanted whitespace in the output.
>
> <?xml version="1.0" encoding="UTF-8" ?>
> <xsl:stylesheet version="1.0" 
> xmlns:xsl="http://www.w3.org/1999/XSL/Transform";>
>         <xsl:output method="text" indent="no" encoding="UTF-8" />
>         <xsl:strip-space elements="*" />
>
>         <xsl:template match="/"><xsl:apply-templates 
> mode="columns"/><xsl:apply-templates mode="values"/></xsl:template>
>         <xsl:template match="Orders" mode="columns">INSERT INTO 
> orders(<xsl:for-each select="Customer/Template/Fields">"<xsl:value-of 
> select="@FieldName" />"<xsl:if test="position() != 
> last()">,</xsl:if></xsl:for-each>)</xsl:template>
>         <xsl:template match="Orders" mode="values"> values(<xsl:for-each 
> select="Customer/Template/Fields">"<xsl:value-of select="FieldValue" 
> />"<xsl:if test="position() != 
> last()">,</xsl:if></xsl:for-each>);</xsl:template>
>
> </xsl:stylesheet>
>
> --
> Charles Knell
> cknell@xxxxxxxxxx - email
>
>
>
> -----Original Message-----
> From:     Guillaume LECERF <guillaume.lecerf@xxxxxxxxxxxxxx>
> Sent:     Fri, 14 Nov 2003 14:42:01 +0100
> To:       xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> Subject:  [xsl] Malformed (??) XML and XML 2 SQL XSLT transformation
>
>
>
> Hi all,
>
> I'd like to have your opinion about my problem :
>
> I've got the following XML file :
>
> <?xml version="1.0" encoding="iso8859-1" ?>
> <Orders>
>         <Customer cust-id="C6272">
>                 <Template Template-id="T37141">
>                         <Fields FieldName="User Login ID">
>                                 <FieldValue>roger.moore</FieldValue>
>                         </Fields>
>                         <Fields FieldName="External Client ID">
>                                 <FieldValue>105</FieldValue>
>                         </Fields>
>                         <Fields FieldName="Job Docket">
>                                 <FieldValue>P377609</FieldValue>
>                         </Fields>
>                         <Fields FieldName="Order Date">
>                                 <FieldValue>10/14/2003</FieldValue>
>                         </Fields>
>                         <Fields FieldName="Order">
>                                 <FieldValue>O1084281</FieldValue>
>                         </Fields>
>                         <Fields FieldName="User Login ID">
>                                 <FieldValue>bernard</FieldValue>
>                         </Fields>
>                         <Fields FieldName="External Client ID">
>                                 <FieldValue>104</FieldValue>
>                         </Fields>
>                         <Fields FieldName="Job Docket">
>                                 <FieldValue>P377610</FieldValue>
>                         </Fields>
>                         <Fields FieldName="Order Date">
>                                 <FieldValue>10/16/2003</FieldValue>
>                         </Fields>
>                         <Fields FieldName="Order">
>                                 <FieldValue>O1084283</FieldValue>
>                         </Fields>
>                 </Template>
>         </Customer>
> </Orders>
>
> from what I want to generate :
>
> INSERT INTO orders("User Login ID","External Client ID","Job 
> Docket","Order Date","Order") 
> values("roger.moore","105","P377609","10/14/2003","O1084281");
> INSERT INTO orders("User Login ID","External Client ID","Job 
> Docket","Order Date","Order") 
> values("bernard","104","P377610","10/16/2003","O1084283");
>
> with a XSLT transformer stylesheet.
>
>
> Any idea ??
>
> Thanks in advance,
>
> Regards
> --
> Guillaume LECERF
> Ingenieur Systemes d'Information / Responsable Production - Gage Interactive
> http://www.activprint.com - +33 (0)2.31.06.67.62
>
> XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
>
>
>
>
> XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list

--
Guillaume LECERF
Ingenieur Systemes d'Information / Responsable Production - Gage Interactive
http://www.activprint.com - +33 (0)2.31.06.67.62 


XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list




XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list