How to transform xml to sql query using XSLT?

Question

I have an xml file and i want to create sql query from it. like below

OUTPUT NEEDED: 

INSERT INTO 'table' values
('2','0','SUGAR_QTY','2','2')
('2','0','OIL_QTY','5','1')

<recConfig>
<default_rec>
    <recid>
    2               
    </recid>

    <recname>                   
        WHITE_BREAD
    </recname>

    <description>           
    </description>

    <accesslevel>
    0
    </accesslevel>  

    <parameter>     
        <parameterCode>
        SUGAR_QTY       
        </parameterCode>
        <parameterValue>            
        2
        </parameterValue>
        <ordinal>
        2
        </ordinal>
    </parameter>
    <parameter>
        <parameterCode>
        OIL_QTY     
        </parameterCode>
        <parameterValue>    
        5
        </parameterValue>
        <ordinal>
        1
        </ordinal>
    </parameter>
</default_rec>

</recConfig>

THIS IS MY XSLT FILE

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="text" encoding="UTF-8" indent="yes" />
    <xsl:template match="/">
 <xsl:text>"INSERT INTO 'recipe_parametrs'</xsl:text>
     <xsl:text> values</xsl:text>

        <xsl:for-each select="recConfig/default_rec">
        <xsl:text>&#xa;</xsl:text>
      <xsl:text>  ('</xsl:text>

     <xsl:value-of select="normalize-space(recid)"/>
    <xsl:text>','</xsl:text>

     <xsl:value-of select="normalize-space(accesslevel)"/>
    <xsl:text>','</xsl:text>
    <xsl:for-each select="parameter"> 

     <xsl:value-of select="normalize-space(parameterCode)"/>
    <xsl:text>','</xsl:text>
         <xsl:value-of select="normalize-space(parameterValue)"/>
        <xsl:text>','</xsl:text>
         <xsl:value-of select="normalize-space(ordinal)"/>
    <xsl:text>','</xsl:text>
    </xsl:for-each>   

    <xsl:text>')</xsl:text>     

      </xsl:for-each>    




      <xsl:text>;</xsl:text>
    </xsl:template>
</xsl:transform>

The output that I get is this

"INSERT INTO 'recipe_parametrs' values
  ('2','0','SUGAR_QTY','2','2','OIL_QTY','5','1','');

I just started learning xslt yesterday. I dont know how separate SUGAR_QTY and OIL_QTY to different queries like shown in OUTPUT NEEDED: . I understand that nested for loop is not the right way to do it. I know that the inside for loop should be replaced with some other logic. I am not sure what to do .

Can anyone please guide me on how to do it.

Thanks.

The requested output has an opening double quote, but no closing one. Also, I don't see the parameterValue of 5 in the given input. — michael.hor257k, Jan 07 '20 at 18:52
Note that if you're using Saxon then you can take advantage of extension instructions such as `` to update the database directly: http://www.saxonica.com/documentation/index.html#!sql-extension — Michael Kay, Jan 08 '20 at 08:36

michael.hor257k · Accepted Answer · 2020-01-07T21:21:22.053

3

How about something like:

XSLT 1.0

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

<xsl:template match="/recConfig">
    <xsl:text>INSERT INTO 'table' values&#10;</xsl:text>
    <xsl:for-each select="default_rec">
        <xsl:variable name="recid" select="recid" />
        <xsl:variable name="accesslevel" select="accesslevel" />
        <xsl:for-each select="parameter">
            <xsl:text>(</xsl:text>
            <xsl:for-each select="$recid | $accesslevel | parameterCode | parameterValue | ordinal">
                <xsl:text>'</xsl:text>
                <xsl:value-of select="normalize-space(.)"/>
                <xsl:text>'</xsl:text>
                <xsl:if test="position() != last()">
                    <xsl:text>,</xsl:text>
                </xsl:if>
            </xsl:for-each>   
            <xsl:text>)&#10;</xsl:text>
        </xsl:for-each>   
  </xsl:for-each>    
</xsl:template>

</xsl:stylesheet>

edited Jan 07 '20 at 21:21

answered Jan 07 '20 at 18:47

michael.hor257k

113,275
6
33
51

Yes it works , thanks. :) But I dont understand the operation of '|' in sl:for-each select="$recid | $accesslevel | parameterCode | parameterValue | ordinal"> if you can explain. Thanks in advance . – madhu madi Jan 07 '20 at 21:25
hey @michael.hor257k thanks, I dont understand the operation of '|' in sl:for-each select="$recid | $accesslevel | parameterCode | parameterValue | ordinal"> if you can explain. – madhu madi Jan 07 '20 at 21:36
1

`|` is the union operator. The expression selects all the listed nodes and applies the contained template to them. -- Note that the nodes will be processed in document order, not in the order they are listed. – michael.hor257k Jan 07 '20 at 21:45

How to transform xml to sql query using XSLT?

1 Answers1