Subject: Re: [xsl] Malformed (??) XML and XML 2 SQL XSLT transformation From: "J.Pietschmann" <j3322ptm@xxxxxxxx> Date: Fri, 14 Nov 2003 20:14:53 +0100 |
<?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.
This seems to be a sort of grouping problem. It would have been much easier if the groups where already marked up in the source like <Template Template-id="T37141"> <Record> <Fields FieldName="User Login ID"> <FieldValue>roger.moore</FieldValue> </Fields> <Fields FieldName="External Client ID"> ...81 </Fields> </Record> <Record> <Fields FieldName="User Login ID"> ...
Well, the structure is not too bad as it is. One somewhat generic approach is to select the Fields with the same as the first field and then select the fields between them in an inner for-each. Unfortunately, I can't remember the most elegant way to do the latter, but it is surely in the list archive: <xsl:template match="Template"> <xsl:variable name="first-field-name" select="Fields[1]/@FieldName"/> <xsl:for-each select="Fields[FieldName=$first-field-name]"> <xsl:variable name="this-id" select="generate-id()"/> <xsl:variable name="fields" select="following-sibling::Fields [generate-id(previous-sibling::Fields[1])=$this-id]"/> <xsl:text>INSERT INTO orders(</xsl:text> <xsl:for-each select="$fields"> <xsl:text>"</xsl:text> <xsl:value-of select="@FieldName"/> <xsl:text>"</xsl:text> <xsl:if test="position()!=last()"> <xsl:text>,</xsl:text> </xsl:if> </xsl:for-each> <xsl:text>)
values(</xsl:text> <xsl:for-each select="$fields"> <xsl:text>"</xsl:text> <xsl:value-of select="FieldValue"/> <xsl:text>"</xsl:text> <xsl:if test="position()!=last()"> <xsl:text>,</xsl:text> </xsl:if> </xsl:for-each> <xsl:text>);
</xsl:text> </xsl:for-each> </xsl:template> Beware: untested.
Current Thread |
---|
|
<- Previous | Index | Next -> |
---|---|---|
[xsl] Malformed (??) XML and XML 2 , Guillaume LECERF | Thread | RE: [xsl] Sablotron and PHP, Michael Kay |
RE: RE: RE: [xsl] Malformed (??) XM, cknell | Date | [xsl] Re: Generating a 2 column tab, Dimitre Novatchev |
Month |