Hi, Is it possible to use XSLT to transform xml data into an insert statement with xslt? Many thanks for any ideas on this @nt
"Ant" wrote in message news:9B4CCC8D-8C8A-419D-BFAF-3B28435C3218@microsoft.com... > Hi, > Is it possible to use XSLT to transform xml data into an insert statement > with xslt? > > Many thanks for any ideas on this > > @nt Of course, you will need to use <xsl:output method="text" />. It's something I've done in the past and something similar to what SQL Server does anyway if you use the datagram update feature. If you show an example of the XML you receive we can help further. -- Joe Fawcett (MVP - XML) http://joe.fawcett.name
Many thanks Joe. Basically I need to Insert one row for every attribute value in each detail node. Please see the snippet below. If you need the rest of the xml, just let me know Much appreciate it. <subDetail> <subDetailHeader xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/" xfa:dataNode="dataGroup" /> <detail> <ItemDescription>March Maintenance</ItemDescription> <ItemPrice>8500.00000000</ItemPrice> <Currency>JPY</Currency> <ItemQuantaty>1.00000000</ItemQuantaty> <ItemCost>8500.00000000</ItemCost> <Currency>JPY</Currency> </detail> <detail> <ItemDescription>April Maintenance</ItemDescription> <ItemPrice>9000.00000000</ItemPrice> <Currency>JPY</Currency> <ItemQuantaty>1.00000000</ItemQuantaty> <ItemCost>9000.00000000</ItemCost> <Currency>JPY</Currency> </detail> <detail> <ItemDescription>May Maintenance</ItemDescription> <ItemPrice>8500.00000000</ItemPrice> <Currency>JPY</Currency> <ItemQuantaty>1.00000000</ItemQuantaty> <ItemCost>8500.00000000</ItemCost> <Currency>JPY</Currency> </detail> etc.... "Joe Fawcett" wrote: > > > "Ant" wrote in message > news:9B4CCC8D-8C8A-419D-BFAF-3B28435C3218@microsoft.com... > > Hi, > > Is it possible to use XSLT to transform xml data into an insert statement > > with xslt? > > > > Many thanks for any ideas on this > > > > @nt > Of course, you will need to use <xsl:output method="text" />. It's something > I've done in the past and something similar to what SQL Server does anyway > if you use the datagram update feature. > If you show an example of the XML you receive we can help further. > > -- > > Joe Fawcett (MVP - XML) > http://joe.fawcett.name >
Try this: <?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"/> <xsl:template match="/"> <xsl:apply-templates select="subDetail"/> </xsl:template> <xsl:template match="subDetail"> <xsl:text>insert </xsl:text> <xsl:value-of select="'table_name'"/><!-- What's the name of the destination table? --> <xsl:text> ( </xsl:text> <xsl:apply-templates select="detail[1]" mode="columnList"/> <xsl:text> )</xsl:text> <xsl:apply-templates select="detail" mode="selectStatement"/> </xsl:template> <xsl:template match="detail" mode="columnList"> <xsl:apply-templates select="*" mode="columnList"/> </xsl:template> <xsl:template match="*[parent::detail]" mode="columnList"> <xsl:if test="position() != 1"> <xsl:text> ,</xsl:text> </xsl:if> <xsl:value-of select="local-name()"/> </xsl:template> <xsl:template match="detail" mode="selectStatement"> <xsl:if test="position() != 1"> <xsl:text> union all</xsl:text> </xsl:if> <xsl:text> select </xsl:text> <xsl:apply-templates select="*" mode="selectStatement"/> </xsl:template> <xsl:template match="*[parent::detail]" mode="selectStatement"> <xsl:if test="position() != 1"> <xsl:text> ,</xsl:text> </xsl:if> <xsl:choose> <xsl:when test="not(number(.))"> <xsl:text>'</xsl:text> <xsl:value-of select="."/> <xsl:text>'</xsl:text> </xsl:when> <xsl:otherwise> <xsl:value-of select="."/> </xsl:otherwise> </xsl:choose> <xsl:text> as </xsl:text> <xsl:value-of select="local-name()"/> </xsl:template> </xsl:stylesheet> Note that you have duplicate colum names in there (Currency listed twice). ML --- Matija Lah, SQL Server MVP http://milambda.blogspot.com/
ML, Thank you very much for supplying this! I really appreciate your efforts in doing this. Many thanks Ant "ML" wrote: > Try this: > > <?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"/> > > <xsl:template match="/"> > <xsl:apply-templates select="subDetail"/> > </xsl:template> > > <xsl:template match="subDetail"> > <xsl:text>insert </xsl:text> > <xsl:value-of select="'table_name'"/><!-- What's the name of the > destination table? --> > <xsl:text> > ( > </xsl:text> > <xsl:apply-templates select="detail[1]" mode="columnList"/> > <xsl:text> > )</xsl:text> > <xsl:apply-templates select="detail" mode="selectStatement"/> > </xsl:template> > > <xsl:template match="detail" mode="columnList"> > <xsl:apply-templates select="*" mode="columnList"/> > </xsl:template> > > <xsl:template match="*[parent::detail]" mode="columnList"> > <xsl:if test="position() != 1"> > <xsl:text> > ,</xsl:text> > </xsl:if> > <xsl:value-of select="local-name()"/> > </xsl:template> > > <xsl:template match="detail" mode="selectStatement"> > <xsl:if test="position() != 1"> > <xsl:text> > union all</xsl:text> > </xsl:if> > <xsl:text> > select </xsl:text> > <xsl:apply-templates select="*" mode="selectStatement"/> > </xsl:template> > > <xsl:template match="*[parent::detail]" mode="selectStatement"> > <xsl:if test="position() != 1"> > <xsl:text> > ,</xsl:text> > </xsl:if> > <xsl:choose> > <xsl:when test="not(number(.))"> > <xsl:text>'</xsl:text> > <xsl:value-of select="."/> > <xsl:text>'</xsl:text> > </xsl:when> > <xsl:otherwise> > <xsl:value-of select="."/> > </xsl:otherwise> > </xsl:choose> > <xsl:text> as </xsl:text> > <xsl:value-of select="local-name()"/> > </xsl:template> > > </xsl:stylesheet> > > > Note that you have duplicate colum names in there (Currency listed twice). > > > ML > > --- > Matija Lah, SQL Server MVP > http://milambda.blogspot.com/
My pleasure. :) XSL = fun ML --- Matija Lah, SQL Server MVP http://milambda.blogspot.com/
