cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

SQL to Flat File For-Each Issues

Go to solution
BattleshipCobra
Contributor

on ‎06-29-2016 10:46 PM

0 Kudos

Hello,

I'm making a scenario where I take an SQL query then I transform it into a specific format and output it to CSV.

I have everything working fine for the query and the output file and formatting.  However, when I output the final results the rows are identical.  IE, my query results in ten different items but my CSV output is just ten rows replicating the first row.

Here is my transformation coding.



<?xml version="1.0" encoding="UTF-8"?>


<xsl:stylesheet xmlns:b1e="urn:com.sap.b1i.sim:b1event" xmlns:b1ie="urn:com.sap.b1i.sim:b1ievent" xmlns:b1im="urn:com.sap.b1i.sim:b1imessage" xmlns:bfa="urn:com.sap.b1i.bizprocessor:bizatoms" xmlns:jdbc="urn:com.sap.b1i.adapter:jdbcadapter" xmlns:rfc="urn:sap-com:document:sap:rfc:functions" xmlns:sim="urn:com.sap.b1i.sim:entity" xmlns:utils2="com.sap.b1i.bpc_tools.Utilities" xmlns:vpf="urn:com.sap.b1i.vplatform:entity" xmlns:xci="urn:com.sap.b1i.xcellerator:intdoc" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" bfa:force="" vpf:force="" jdbc:force="" rfc:force="" b1ie:force="" b1e:force="" xci:force="" sim:force="" utils2:force="" b1im:force="">


    <xsl:output method="xml" encoding="UTF-8" indent="yes"></xsl:output>


    <xsl:param name="atom"></xsl:param>


    <xsl:param name="sessionid"></xsl:param>


    <xsl:variable name="msg" select="/vpf:Msg/vpf:Body/vpf:Payload[./@Role=&apos;S&apos;]"></xsl:variable>


    <xsl:variable name="vpSender" select="/vpf:Msg/vpf:Header/vpf:Sender/@Id"></xsl:variable>


    <xsl:variable name="vpObject" select="/vpf:Msg/vpf:Header/vpf:Sender/@ObjId"></xsl:variable>


    <xsl:variable name="vpReceiver" select="/vpf:Msg/vpf:Header/vpf:ReceiverList/vpf:Receiver[./@handover=&apos;P&apos;]/@Id"></xsl:variable>


    <xsl:variable name="sqlresult" select="/vpf:Msg/vpf:Body/vpf:Payload[./@id=&apos;atom1&apos;]"></xsl:variable>


    <xsl:template match="/">


        <Msg xmlns="urn:com.sap.b1i.vplatform:entity">


            <xsl:copy-of select="/vpf:Msg/@*"></xsl:copy-of>


            <xsl:copy-of select="/vpf:Msg/vpf:Header"></xsl:copy-of>


            <Body>


                <xsl:copy-of select="/vpf:Msg/vpf:Body/*"></xsl:copy-of>


                <Payload Role="X" id="{$atom}">


                    <xsl:call-template name="transform"></xsl:call-template>


                </Payload>


            </Body>


        </Msg>


    </xsl:template>


    <xsl:template name="transform">


        <xsl:attribute name="deli">,</xsl:attribute>


        <bfa:io>


            <bfa:row>


                <bfa:col>Supplier ID</bfa:col>


                <bfa:col>Item Number</bfa:col>


                <bfa:col>Qty On Hand</bfa:col>


                <bfa:col>Qty Backordered</bfa:col>


                <bfa:col>Qty On Order</bfa:col>


                <bfa:col>Item Next Availability</bfa:col>


                <bfa:col>Item Discontinued</bfa:col>


                <bfa:col>Item Description</bfa:col>


            </bfa:row>


            <xsl:for-each select="$sqlresult/jdbc:ResultSet/jdbc:Row">


                <bfa:row>


                    <bfa:col>


                        <xsl:value-of select="$sqlresult/jdbc:ResultSet/jdbc:Row/jdbc:SuppID"></xsl:value-of>


                    </bfa:col>


                    <bfa:col>


                        <xsl:value-of select="$sqlresult/jdbc:ResultSet/jdbc:Row/jdbc:ItemCode"></xsl:value-of>


                    </bfa:col>


                    <bfa:col>


                        <xsl:value-of select="$sqlresult/jdbc:ResultSet/jdbc:Row/jdbc:QtyOnHand"></xsl:value-of>


                    </bfa:col>


                    <bfa:col>


                        <xsl:value-of select="$sqlresult/jdbc:ResultSet/jdbc:Row/jdbc:QtyBackordered"></xsl:value-of>


                    </bfa:col>


                    <bfa:col>


                        <xsl:value-of select="$sqlresult/jdbc:ResultSet/jdbc:Row/jdbc:QtyOnOrder"></xsl:value-of>


                    </bfa:col>


                    <bfa:col>


                        <xsl:value-of select="$sqlresult/jdbc:ResultSet/jdbc:Row/jdbc:ItemNextAvail"></xsl:value-of>


                    </bfa:col>


                    <bfa:col>


                        <xsl:value-of select="$sqlresult/jdbc:ResultSet/jdbc:Row/jdbc:ItemDisc"></xsl:value-of>


                    </bfa:col>


                    <bfa:col>


                        <xsl:value-of select="$sqlresult/jdbc:ResultSet/jdbc:Row/jdbc:ItemName"></xsl:value-of>


                    </bfa:col>


                </bfa:row>


            </xsl:for-each>


        </bfa:io>


    </xsl:template>




</xsl:stylesheet>

My resulting file is similar to (i didn't recreate the entire file, just to illustrate that I'm getting only the top row of my SQL query results):

Item Number

Item 1

Item 1

Item 1

Item 1

Item 1

Item 1

Item 1

Item 1

Item 1

Item 1

My query results payload is perfect when I do the test step.  I just can't get the for-each to work properly.  I have heard that I need a jdbc:ResultSets (with an S) but this doesn't seem to make any difference.

How do I make it properly increment the rows in the output??

Thanks!

Mike

Show replies
Show replies
Answer
View Entire Topic
BattleshipCobra
Contributor
‎06-30-2016 12:18 AM
0 Kudos

I actually solved my own issue.

Turns out my xPath for the child items was incorrect:



<?xml version="1.0" encoding="UTF-8"?>


<xsl:stylesheet xmlns:b1e="urn:com.sap.b1i.sim:b1event" xmlns:b1ie="urn:com.sap.b1i.sim:b1ievent" xmlns:b1im="urn:com.sap.b1i.sim:b1imessage" xmlns:bfa="urn:com.sap.b1i.bizprocessor:bizatoms" xmlns:jdbc="urn:com.sap.b1i.adapter:jdbcadapter" xmlns:rfc="urn:sap-com:document:sap:rfc:functions" xmlns:sim="urn:com.sap.b1i.sim:entity" xmlns:utils2="com.sap.b1i.bpc_tools.Utilities" xmlns:vpf="urn:com.sap.b1i.vplatform:entity" xmlns:xci="urn:com.sap.b1i.xcellerator:intdoc" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" bfa:force="" vpf:force="" jdbc:force="" rfc:force="" b1ie:force="" b1e:force="" xci:force="" sim:force="" utils2:force="" b1im:force="">


    <xsl:output method="xml" encoding="UTF-8" indent="yes"></xsl:output>


    <xsl:param name="atom"></xsl:param>


    <xsl:param name="sessionid"></xsl:param>


    <xsl:variable name="msg" select="/vpf:Msg/vpf:Body/vpf:Payload[./@Role=&apos;S&apos;]"></xsl:variable>


    <xsl:variable name="vpSender" select="/vpf:Msg/vpf:Header/vpf:Sender/@Id"></xsl:variable>


    <xsl:variable name="vpObject" select="/vpf:Msg/vpf:Header/vpf:Sender/@ObjId"></xsl:variable>


    <xsl:variable name="vpReceiver" select="/vpf:Msg/vpf:Header/vpf:ReceiverList/vpf:Receiver[./@handover=&apos;P&apos;]/@Id"></xsl:variable>


    <xsl:template match="/">


        <Msg xmlns="urn:com.sap.b1i.vplatform:entity">


            <xsl:copy-of select="/vpf:Msg/@*"></xsl:copy-of>


            <xsl:copy-of select="/vpf:Msg/vpf:Header"></xsl:copy-of>


            <Body>


                <xsl:copy-of select="/vpf:Msg/vpf:Body/*"></xsl:copy-of>


                <Payload Role="X" id="{$atom}">


                    <xsl:call-template name="transformDSV"></xsl:call-template>


                </Payload>


            </Body>


        </Msg>


    </xsl:template>


    <xsl:template name="transformDSV">


        <xsl:attribute name="deli">,</xsl:attribute>


        <xsl:variable name="sqlResults" select="/vpf:Msg/vpf:Body/vpf:Payload[./@id=&apos;atom1&apos;]"></xsl:variable>


        <bfa:io>


            <bfa:row>


                <bfa:col>Supplier ID</bfa:col>


                <bfa:col>Item Number</bfa:col>


                <bfa:col>Qty On Hand</bfa:col>


                <bfa:col>Qty Backordered</bfa:col>


                <bfa:col>Qty On Order</bfa:col>


                <bfa:col>Item Next Availability</bfa:col>


                <bfa:col>Item Discontinued</bfa:col>


                <bfa:col>Item Description</bfa:col>


            </bfa:row>


            <xsl:for-each select="$sqlResults/jdbc:ResultSet/jdbc:Row">


                <bfa:row>


                    <bfa:col>


                        <xsl:value-of select="jdbc:SuppID"></xsl:value-of>


                    </bfa:col>


                    <bfa:col>


                        <xsl:value-of select="jdbc:ItemCode"></xsl:value-of>


                    </bfa:col>


                    <bfa:col>


                        <xsl:value-of select="jdbc:QtyOnHand"></xsl:value-of>


                    </bfa:col>


                    <bfa:col>


                        <xsl:value-of select="jdbc:QtyBackordered"></xsl:value-of>


                    </bfa:col>


                    <bfa:col>


                        <xsl:value-of select="jdbc:QtyOnOrder"></xsl:value-of>


                    </bfa:col>


                    <bfa:col>


                        <xsl:value-of select="jdbc:ItemNextAvail"></xsl:value-of>


                    </bfa:col>


                    <bfa:col>


                        <xsl:value-of select="jdbc:ItemDisc"></xsl:value-of>


                    </bfa:col>


                    <bfa:col>


                        <xsl:value-of select="jdbc:ItemName"></xsl:value-of>


                    </bfa:col>


                </bfa:row>


            </xsl:for-each>


        </bfa:io>


    </xsl:template>


</xsl:stylesheet>

If you notice I have just the "jdbc:ItemCode" for example in the columns now.  Before I had the full xPath which I guess was redundant.  Like "$sqlresult/jdbc:ResultSet/jdbc:Row/jdbc:ItemCode" which is incorrect.

Good luck!

Show replies
Show replies
Ask a Question