API Connect

 View Only
Expand all | Collapse all

Handling response data coming from Database

Stefen Salvatore

Stefen SalvatoreTue January 24, 2023 09:17 AM

  • 1.  Handling response data coming from Database

    Posted Tue January 24, 2023 09:17 AM
    Edited by Stefen Salvatore Wed January 25, 2023 09:23 AM
      |   view attached

    Attachment(s)

    xml
    sqldata handling.xml   2 KB 1 version


  • 2.  RE: Handling response data coming from Database
    Best Answer

    Posted Fri January 27, 2023 09:35 AM

    Hi Vyasavardhan,

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:template match="@*|node()">
        <xsl:copy>
          <xsl:apply-templates select="node()|@*" />
        </xsl:copy>
      </xsl:template>
    
      <xsl:template match="//url-open/response/sql/row">
    
        <xsl:copy>
          <xsl:variable name="columnNameSvr" select="./column/name[text() = 'SERVER_NAME']/text()" />
          <xsl:element name="{$columnNameSvr}">
              <xsl:value-of select="./column/name[text() = 'SERVER_NAME']/../value/text()" />
          </xsl:element>
          <xsl:variable name="columnNameTimeStamp" select="./column/name[text() = 'TIMESTAMP']/text()" />
          <xsl:element name="{$columnNameTimeStamp}">
              <xsl:value-of select="./column/name[text() = 'TIMESTAMP']/../value/text()" />
          </xsl:element>
        </xsl:copy>
      </xsl:template>
    
    </xsl:stylesheet>

    Produces

    <?xml version="1.0" encoding="UTF-8"?><url-open>
        <response>
            <sql result="success">
                <row><SERVER_NAME>SVR</SERVER_NAME><TIMESTAMP>2022-08-12 13:00:18.301000</TIMESTAMP></row>
                <row><SERVER_NAME>SVR</SERVER_NAME><TIMESTAMP>2022-08-12 13:27:54.337000</TIMESTAMP></row>
            </sql>
        </response>
    </url-open>

    A couple points about your original xsl

    1. Your apply templates was not providing |@* to indicate that all attributes should also be provided.  Thus your output was missing the result="success" attribute on the sql element.

    2. Your template match was matching on //url-open/response/sql/row/column but what you were attempting to do was to change the child elements of the row.  By instead matching on //url-open/response/sql/row, the  xsl:copy will output the row element, and within the xsl:copy you can create the new xml elements from that row's children elements.

    3. Since the two columns are sibling elements, you have to find the proper element for both the SERVER_NAME and TIMESTAMP, but their values you must XPath to that element's parent to then get the value.

    Best Regards,

    Steve Linn



    ------------------------------
    Steve Linn
    Senior Consulting I/T Specialist
    IBM
    ------------------------------



  • 3.  RE: Handling response data coming from Database

    Posted Mon January 30, 2023 05:58 AM

    Hi Steeve,

    I'm Ok with the code you given but what if my sql data contains another column '<column><name>XXXXX</name><value>ASASA</value></column> I think we cannot handle it with the above code sice you've hardcoded the values.

    i've written another xslt:

    <xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="@*|node()">
    <xsl:copy>
    <xsl:apply-templates select="@*|node()" />
    </xsl:copy>
    </xsl:template>

    <xsl:template match="//url-open/response/sql/row/column">
    <xsl:variable name ="Column_Val">
    <xsl:for-each select="./value">
    <xsl:variable name ="Column_Value">
    <xsl:value-of select='.'/>
    </xsl:variable>
    <xsl:value-of select="$Column_Value"/>
    </xsl:for-each>
    </xsl:variable>
    <xsl:for-each select="name">
    <xsl:variable name ="Column_Name">
    <xsl:value-of select='.'/>
    </xsl:variable>
    <xsl:element name="{$Column_Name}">
    <xsl:value-of select="$Column_Val"/>
    </xsl:element>


    </xsl:for-each>

    </xsl:template>

    </xsl:stylesheet>
    ==================================================================
    Output Got:

    <?xml version="1.0" encoding="UTF-8"?>
    <url-open>
    <response>
    <sql result="success">
    <row>
    <SERVER_NAME>SVR</SERVER_NAME>
    <TIMESTAMP>2022-08-12 13:00:18.301000</TIMESTAMP>
    </row>
    <row>
    <SERVER_NAME>SVR</SERVER_NAME>
    <TIMESTAMP>2022-08-12 13:27:54.337000</TIMESTAMP>
    </row>
    </sql>
    </response>
    </url-open>

    =================================================================== 


    and problem no 2: 

    I want to create a user defined policy of 'aaa' processing action from datapower. I've gone through this doc https://www.ibm.com/docs/en/SSMNED_v10/com.ibm.apic.policy.doc/custpolicies_apigw_catalog.html

    but iam unable to upload the policy and i've doubt regarding preparing YAML for this processing rule."

    please help me!!!

    Thanks in Advance !!



    ------------------------------
    Vyasavardhan Ramagiri
    DP/APIC Developer
    ------------------------------



  • 4.  RE: Handling response data coming from Database

    Posted Mon January 30, 2023 09:23 AM
    Edited by Steve Linn Mon January 30, 2023 09:23 AM

    Hi Vyasavardhan,

    By matching  on //url-open/response/sql/row/column you'll match each column irrespective of the row that the column is in, so you still want to match on each row.  Here's how you would create the same output regardless of how many columns or what their values are, with the assumption being each column has a name and value child element.

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:template match="@*|node()">
        <xsl:copy>
          <xsl:apply-templates select="node()|@*" />
        </xsl:copy>
      </xsl:template>
    
      <xsl:template match="//url-open/response/sql/row">
    
        <xsl:copy>
          <xsl:for-each select="./column">
            <xsl:variable name="columnNameSvr" select="./name/text()" />
            <xsl:element name="{$columnNameSvr}">
                <xsl:value-of select="./value/text()" />
            </xsl:element>
          </xsl:for-each>
        </xsl:copy>
      </xsl:template>
    
    </xsl:stylesheet>

    with input

    <?xml version="1.0" encoding="UTF-8"?>
    <url-open>
        <response>
            <sql result="success">
                <row>
                    <column>
                        <name>SERVER_NAME</name>
                        <value>SVR</value>
                    </column>
                    <column>
                        <name>TIMESTAMP</name>
                        <value>2022-08-12 13:00:18.301000</value>
                    </column>
                    <column>
                        <name>ANOTHERCOLUMN</name>
                        <value>value1</value>
                    </column>
                </row>
                <row>
                    <column>
                        <name>SERVER_NAME</name>
                        <value>SVR</value>
                    </column>
                    <column>
                        <name>TIMESTAMP</name>
                        <value>2022-08-12 13:27:54.337000</value>
                    </column>
                    <column>
                        <name>ANOTHERCOLUMN</name>
                        <value>value2</value>
                    </column>
                </row>
            </sql>
        </response>
    </url-open>

    produces

    <?xml version="1.0" encoding="UTF-8"?><url-open>
        <response>
            <sql result="success">
                <row><SERVER_NAME>SVR</SERVER_NAME><TIMESTAMP>2022-08-12 13:00:18.301000</TIMESTAMP><ANOTHERCOLUMN>value1</ANOTHERCOLUMN></row>
                <row><SERVER_NAME>SVR</SERVER_NAME><TIMESTAMP>2022-08-12 13:27:54.337000</TIMESTAMP><ANOTHERCOLUMN>value2</ANOTHERCOLUMN></row>
            </sql>
        </response>
    </url-open>

    so the result still has two rows, but now the three columns are present, and no hardcoding of the specific column names you're looking for.

    Best Regards,

    Steve



    ------------------------------
    Steve Linn
    Senior Consulting I/T Specialist
    IBM
    ------------------------------



  • 5.  RE: Handling response data coming from Database

    Posted Mon January 30, 2023 12:19 PM
    Edited by Stefen Salvatore Mon January 30, 2023 12:22 PM
    Thanks a lot Steeve !!!

    and i need help in this: 

    I want to create a user defined policy of 'aaa' processing action from datapower. I've gone through this doc https://www.ibm.com/docs/en/SSMNED_v10/com.ibm.apic.policy.doc/custpolicies_apigw_catalog.html

    but iam unable to upload the policy and i've doubt regarding preparing YAML for this UDP. Especially naming conventions for the action(Processing action).

    Thanks in Advance !!


    ------------------------------
    Vyasavardhan Ramagiri
    DP/APIC Developer
    ------------------------------



  • 6.  RE: Handling response data coming from Database

    Posted Mon January 30, 2023 03:09 PM

    Hi Vyasavardhan,

    This question is outside of the scope of your original question so you should open a new thread for this and I can put more details there if needed, but in a nutshell, the API Gateway does not have a mechanism within its UDPs to use the DataPower AAA action as it only allows you to use <policyname> Assembly Actions, for example a GatewayScript assembly action.   What you would need to do is to create a v5 UDP where the implementation uses DataPower actions.  This v5 UDP zip would need to be added to a v10 Gateway Extension zip, an entry added into the files array of the manifest.json file of that zip file, and that .zip file associated with the gateway via CMC.
    Regards,

    Steve



    ------------------------------
    Steve Linn
    Senior Consulting I/T Specialist
    IBM
    ------------------------------



  • 7.  RE: Handling response data coming from Database

    Posted Tue January 31, 2023 12:53 AM

    yes steeve. I've already opened a thread here's the link

    https://community.ibm.com/community/user/integration/discussion/error-while-pushing-user-defined-policy-to-catalog-gateway-policies?ReturnUrl=%2fcommunity%2fuser%2fintegration%2fcommunities%2fcommunity-home%2fdigestviewer%3fcommunitykey%3d2106cca0-a9f9-45c6-9b28-01a28f4ce947



    ------------------------------
    Vyasavardhan Ramagiri
    DP/APIC Developer
    ------------------------------



  • 8.  RE: Handling response data coming from Database

    Posted Wed February 15, 2023 10:33 AM
      |   view attached

    Hi @Steve Linn 

    I've a xml coming from input i need to insert in the Database. So, I've wrote a XSLT, but it is only inserting one transaction details. Please help me in inserting all the details a file attached below with request and XSLT I used.

    Thanks in advance!!



    ------------------------------
    Vyasavardhan Ramagiri
    ------------------------------

    Attachment(s)

    xml
    bulk_payments.xml   3 KB 1 version


  • 9.  RE: Handling response data coming from Database

    Posted Thu February 16, 2023 12:05 PM

    Hi Vyasavardhan ,

    Your XML has many transaction elements, but not only are you processing  just one sql-execute, but I'd think your data in that sql insert is not an individual transaction's data, for example, 

    <xsl:variable name="id"  select="./transaction/id/text()"/>

    Since you have in your XML 3 transactions, your variable id would have a value of 123.  You need to wrap your processing with an xsl:for-each, something like

    <xsl:template match="//bulk_payments">
      <xsl:variable name="name"  select="./customer/name"/>
      <xsl:variable name="account_number"  select="./customer/account_number"/>
      <!-- iterate over each transaction element -->
      <xsl:for-each select="./transaction">
        <xsl:variable name="id" select="./id/text()"/>
        <xsl:variable name="beneficiary_name"  select="./beneficiary_name"/>
        <xsl:variable name="beneficiary_account"  select="./beneficiary_account"/>
        <xsl:variable name="amount"  select="./amount"/>
        <xsl:variable name="currency"  select="./currency"/>
        <xsl:variable name="description"  select="./description"/>
        <dp:sql-execute source="'MY-SQL'" statement="'insert into CustomerDetails3 values(?,?,?,?,?,?,?,?)'">
          <arguments>
            <argument type="SQL_VARCHAR" mode="INPUT">
              <xsl:value-of select="$id"/>
            </argument>
            <argument type="SQL_VARCHAR" mode="INPUT">
              <xsl:value-of select="$name"/>
            </argument>
            <argument type="SQL_VARCHAR" mode="INPUT">
              <xsl:value-of select="$account_number"/>
            </argument>
            <argument type="SQL_VARCHAR" mode="INPUT">
              <xsl:value-of select="$beneficiary_name"/>
            </argument>
            <argument type="SQL_VARCHAR" mode="INPUT">
              <xsl:value-of select="$beneficiary_account"/>
            </argument>
            <argument type="SQL_VARCHAR" mode="INPUT">
              <xsl:value-of select="$amount"/>
            </argument>
            <argument type="SQL_VARCHAR" mode="INPUT">
              <xsl:value-of select="$currency"/>
            </argument>
            <argument type="SQL_VARCHAR" mode="INPUT">
              <xsl:value-of select="$description"/>
            </argument>	 
          </arguments>
        </dp:sql-execute>
      </xsl:for-each>
    </xsl:template>
    

    Best Regards, 
    Steve Linn



    ------------------------------
    Steve Linn
    Senior Consulting I/T Specialist
    IBM
    ------------------------------



  • 10.  RE: Handling response data coming from Database

    Posted Fri February 17, 2023 12:22 AM

    Hi @Steve Linn

    Thanks for spending time and effort for giving this reply.
    I've wrote another XSLT which is working fine as my xml will have multiple customer transaction details.



    ------------------------------
    Vyasavardhan Ramagiri
    ------------------------------