[XSL-LIST Mailing List Archive Home]
[By Thread]
[By Date]
[Recent Entries]
[Reply To This Message]
RE: RE: Malformed (??) XML and XML 2 SQL XSLT transfor
Subject: RE: RE: Malformed (??) XML and XML 2 SQL XSLT transformation
From: Guillaume LECERF <guillaume.lecerf@xxxxxxxxxxxxxx>
Date: Fri, 14 Nov 2003 16:59:29 +0100
|
I will be a little lamer ;)
I'd like not ot have the number of fields hardcoded (it's not necessary the
five first fields)
If you have a two pass solution that could be more elegant, don't hesitate !
Thanks for your efforts mates ;)
At 10:41 14/11/2003 -0500, you wrote:
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: 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: 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
--
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
|
PURCHASE STYLUS STUDIO ONLINE TODAY!
Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!
Download The World's Best XML IDE!
Accelerate XML development with our award-winning XML IDE - Download a free trial today!
Subscribe in XML format
RSS 2.0 |
|
Atom 0.3 |
|
|