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.
Original Message:
Sent: Thu February 16, 2023 12:05 PM
From: Steve Linn
Subject: Handling response data coming from Database
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
Original Message:
Sent: Wed February 15, 2023 10:32 AM
From: Vyasavardhan Ramagiri
Subject: Handling response data coming from Database
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
Original Message:
Sent: Mon January 30, 2023 09:22 AM
From: Steve Linn
Subject: Handling response data coming from Database
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
Original Message:
Sent: Mon January 30, 2023 05:58 AM
From: Vyasavardhan Ramagiri
Subject: Handling response data coming from Database
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
Original Message:
Sent: Fri January 27, 2023 09:34 AM
From: Steve Linn
Subject: Handling response data coming from Database
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
Original Message:
Sent: Tue January 24, 2023 09:17 AM
From: Vyasavardhan Ramagiri
Subject: Handling response data coming from Database