|
Subject: RE: RE: RE: [xsl] Malformed (??) XML and XML 2 SQL XSLTtransformation From: cknell@xxxxxxxxxx Date: Fri, 14 Nov 2003 13:51:02 -0500 |
<?xml version="1.0" encoding="UTF-8" ?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform";> <xsl:output method="xml" indent="yes" encoding="UTF-8" />
<xsl:template match="/">
<xsl:for-each select="Orders/Customer/Template/Fields[@FieldName='User Login ID']">
<xsl:text>INSERT INTO ORDERS(</xsl:text>"<xsl:value-of select="normalize-space(@FieldName)"/>"
<xsl:choose>
<xsl:when test="following-sibling::*[1][@FieldName != 'User Login ID']">
<xsl:variable name="fs-number" select="2" />
<xsl:call-template name="column-name">
<xsl:with-param name="fs1" select="$fs-number" />
</xsl:call-template>
<xsl:text>VALUES(</xsl:text>"<xsl:value-of select="normalize-space(.)"/>"
<xsl:call-template name="column-value">
<xsl:with-param name="fs1" select="$fs-number" />
</xsl:call-template>
</xsl:when>
</xsl:choose>
</xsl:for-each>
</xsl:template> <xsl:template name="column-name">
<xsl:param name="fs1" />
<xsl:variable name="fs-number" select="$fs1 + 1" />
,"<xsl:value-of select="normalize-space(following-sibling::*[$fs1]/@FieldName)" />"
<xsl:choose>
<xsl:when test="following-sibling::*[$fs-number][@FieldName != 'User Login ID']">
<xsl:call-template name="column-name">
<xsl:with-param name="fs1" select="$fs-number" />
</xsl:call-template>
</xsl:when>
<xsl:otherwise><xsl:text>)</xsl:text></xsl:otherwise>
</xsl:choose>
</xsl:template> <xsl:template name="column-value">
<xsl:param name="fs1" />
<xsl:variable name="fs-number" select="$fs1 + 1" />
,"<xsl:value-of select="normalize-space(following-sibling::*[$fs1])" />"
<xsl:choose>
<xsl:when test="following-sibling::*[$fs-number][@FieldName != 'User Login ID']">
<xsl:call-template name="column-value">
<xsl:with-param name="fs1" select="$fs-number" />
</xsl:call-template>
</xsl:when>
<xsl:otherwise><xsl:text>);</xsl:text></xsl:otherwise>
</xsl:choose>
</xsl:template></xsl:stylesheet> -- Charles Knell cknell@xxxxxxxxxx - email
-----Original Message----- From: Guillaume LECERF <guillaume.lecerf@xxxxxxxxxxxxxx> Sent: Fri, 14 Nov 2003 16:59:29 +0100 To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx Subject: RE: RE: [xsl] Malformed (??) XML and XML 2 SQL XSLTtransformation
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
| Current Thread |
|---|
|
| <- Previous | Index | Next -> |
|---|---|---|
| RE: [xsl] Can anyone point me in th, Andreas L. Delmelle | Thread | RE: RE: RE: RE: [xsl] Malformed (??, cknell |
| [xsl] Encoding of DB distorts speci, Pramodh Peddi | Date | Re: [xsl] Malformed (??) XML and XM, J.Pietschmann |
| Month |