Create a SQL Query from SAP MII XML structure

Rowsets/Rowset/Row is a very common XPath in MII world. We used this format for several UI implementation. In this article we are going to use the same XML for generating dynamic SQL statement for different scenarios.

MII is a very easy tool so our solution should also be easy. So, lets see how can we design the solution generic and easy.

Please copy the XSL below

<?xml version=”1.0″ encoding=”UTF-8″?>
<xsl:stylesheet version=”1.0″ xmlns:xsl=”http://www.w3.org/1999/XSL/Transform” xmlns:java=”http://xml.apache.org/xslt/java” exclude-result-prefixes=”java”>
<xsl:output method=”xml” media-type=”text/xml” encoding=”UTF-8″/>
<xsl:param name=”RowDelim”>(</xsl:param>
<xsl:param name=”RowLast”>)</xsl:param>
<xsl:param name=”colStart”>[</xsl:param>
<xsl:param name=”colEnd”>]</xsl:param>
<xsl:param name=”FieldDelim”>,</xsl:param>
<xsl:param name=”StringDelim”>'</xsl:param>
<xsl:param name=”DateFormat”>yyyy-MM-dd HH:mm:ss</xsl:param>

<xsl:template match=”/”>
<Doc>
<xsl:for-each select=”Rowsets”>
<xsl:call-template name=”PrintFatalError”/>
<xsl:call-template name=”PrintMessage”/>
<xsl:for-each select=”Rowset”>
<Table>
<Name><xsl:value-of select=”Table”/></Name>
<Where><xsl:value-of select=”Where”/></Where>
<xsl:for-each select=”Columns”>
<Columns>

<xsl:value-of select=”$RowDelim”/><xsl:for-each select=”Column”>
<xsl:value-of select=”$colStart”/>
<xsl:value-of select=”@Name”/>
<xsl:value-of select=”$colEnd”/>
<xsl:if test=”not(position() = last())”>
<xsl:value-of select=”$FieldDelim”/>
</xsl:if>
</xsl:for-each>
<xsl:value-of select=”$RowLast”/>
</Columns>
</xsl:for-each>
<Data>
<xsl:for-each select=”Row”>
<Row>
<xsl:value-of select=”$RowDelim”/>
<xsl:for-each select=”*”>

<xsl:value-of select=”$StringDelim”/>

<xsl:value-of select=”.”/>

<xsl:value-of select=”$StringDelim”/>

<xsl:if test=”not(position() = last())”>
<xsl:value-of select=”$FieldDelim”/>
</xsl:if>
</xsl:for-each>
<xsl:if test=”not(position() = last())”>

</xsl:if>
<xsl:if test=”position() = last()”>

</xsl:if>
<xsl:value-of select=”$RowLast”/>
</Row>
</xsl:for-each>
</Data>
</Table>
</xsl:for-each>
</xsl:for-each>
</Doc>
</xsl:template>

<xsl:template name=”PrintFatalError”>
<xsl:for-each select=”FatalError”>

<xsl:text>Fatal Error – </xsl:text>

<xsl:value-of select=”.”/>

</xsl:for-each>
</xsl:template>
<xsl:template name=”PrintMessage”>
<xsl:for-each select=”Messages”>
<xsl:for-each select=”Message”>

<xsl:text>Fatal Error – </xsl:text>
<xsl:value-of select=”.”/>

</xsl:for-each>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

This XSL file will give you every thing you want to build further expression.

Step 1.

Create a MII XML document which will have the format

<Rowsets>
<Rowset>
<Columns>
<Column Name=”OrderNumber”/> <— I am not adding other attributes to make the XML look and feel easy
<Column Name=”OrderID”/>
</Columns>
<Row>
<OrderNumber>1234</OrderNumber>
<OrderID>10</OrderID>
</Row>
</Rowset>
</Rowsets>

When you transform this XML using the XSL attached, you get a response as follows:

<Doc>
<Table>
<Name></Name>
<Where></Where>
<Columns>([OrderNumber],[OrderID])</Columns>
<Data>
<Row>(‘1234′,’10’)</Row>
</Data>
</Table>
</Doc>

Now look at the information in the XML file. The format is good to insert.

<Columns>([OrderNumber],[OrderID])</Columns>

<Row>(‘1234′,’10’)</Row>

But we are missing the table name where we have to insert.

So, now you should append some XML tags in the MII XML document, I am also adding a value which is the table name.

<Table>ProductionOrderHeader</Table>

Now the output will look as below after transformation.

<Doc>
<Table>
<Name>ProductionOrderHeader</Name>
<Where></Where>
<Columns>([OrderNumber],[OrderID])</Columns>
<Data>
<Row>(‘1234′,’10’)</Row>
</Data>
</Table>
</Doc>

Now you can use this XML for inserting values in the Database.

If you need any Where clause, then you can also add a tag

<Where>OrderNumber = ‘1234’</Where>

This will help you to construct the dynamic select and update queries.

Example XML.

<Rowsets>
<Rowset>
<Table>ProductionOrderHeader</Table>
<Where>OrderNumber = ‘1234’</Where>
<Columns>
<Column Name=”OrderNumber”/>
<Column Name=”OrderID”/>
</Columns>
<Row>
<OrderNumber>1234</OrderNumber>
<OrderID>10</OrderID>
</Row>
</Rowset>
</Rowsets>

Result after transformation:

<Doc>
<Table>
<Name>ProductionOrderHeader</Name>
<Where>OrderNumber = ‘1234’</Where>
<Columns>([OrderNumber],[OrderID])</Columns>
<Data>
<Row>(‘1234′,’10’)</Row>
</Data>
</Table>
</Doc>

Note: You can use the Union action block to Union multiple MII XMLs and transform those using this XSL.