Retrieve multiple column values using a single XSL query

Requirement: To retrieve multiple column values using a single XSL query.

If you use a query like below in your XSL only the first name will be populated in the field. All other values will be suppressed.

Select first_name , last_name , email , phone_number from Employee where employee_id =1001

So if you have a requirement to fetch multiple column values using XSL query, then you will have to go for a “||'::'|| ” connector.

Select first_name ||'::'|| last_name ||'::'|| email ||'::'|| phone_number from Employee where employee_id =1001

From SQL window, this query will fetch the values in one field, but in XSL --orcl:query-database() function it will throw OracleXMLSQLException

Error

oracle.xml.sql.OracleXMLSQLException: Character '|' is not allowed in an XML tag name

Workaround

Workaround is to add an alias name to query which will represent the concatenated value field.

Select first_name ||'::'|| last_name ||'::'|| email ||'::'|| phone_number emp_details

From Employee where employee_id =1001

This will solve the error due to the pipe character.

XSL Mapping doc

<xsl:stylesheet version="1.0"

……………………………..

………………………………..

">

<xsl:template match="/">

<ns1:XSLQueryDBProcessProcessResponse>

<ns1:result1>

<xsl:value-of select="orcl:query-database(concat(" Select first_name ||'::'|| last_name ||'::'|| email ||'::'|| phone_number emp_details from Employee where employee_id =1001 "),false(),false(),"jdbc/TestDB")"/>

</ns1:result1>

</ns1:XSLQueryDBProcessProcessResponse>

</xsl:template>

</xsl:stylesheet>

Output

<XSLQueryDBProcessProcessResponse xmlns:ns1="http://xmlns.oracle.com/XSLQueryDBProcess" xmlns="http://xmlns.oracle.com/XSLQueryDBProcess">

<ns1:result1>

GEO::THO::geo_tho@oracle.com::4086133000

</ns1:result1>

</XSLQueryDBProcessProcessResponse>

Once you have retrieved all the details into a field, you can use string functions to fetch each field and assign it to separate fields. The tokenizer token will be “::” (double colon).

Best practices on XSL Transformations-AIA PIP Customizations

In the recent past the hype on AIA has reached a new high than compared to the past few years when the idea was conceived and implemented. The AIA PIP’s have matured a lot coming from 2.1 to 2.5.

In today’s write-up, I will be focus more on the Transformations involved in the PIP flows. Since PIPs needs to be configured for each customer requirement, there will be some customizations involved like custom mapping to be done from ABM to EBM to ABM. To cater to this need AIA provides the Custom XSL feature which will be part of all the processes. This is achieved by embedding Call Custom Template tags under each category.

The PIPs developed till date may not have completely complied with all the standard practices. I will familiarize you with the practices Oracle have followed in couple of PIPs namely Design to Release, Order to Cash and MDM Product PIPs.

I will first go with my favorite one.

D2R PIP

On Agile Requestor side, the artifacts particularly the transformations (both main and Custom XSL) are accessed from the server repository.

In case of Ebiz Provider process, only the custom transformations are accessed from the server repository. The main XSL will be embedded with the process. The selection between main XSL and custom XSL is decided based on a property defined in AIA Configurations file.(code snippets below will give you a better idea)

The main difference between both sides is that on Agile side mappings on both XSL files will be used for mapping where as in the case of Ebiz there is always a option to decide whether to use either main XSL or custom XSL. Both XSL’s can also be used (similar to Agile) in conjunction as call template feature to custom templates are embedded in the Main XSL.

Ebiz Main XSL

<xsl:template match="/">

<xsl:choose>

<xsl:when test="aia:getServiceProperty('{http://xmlns.oracle.com/ABCSImpl/Ebiz/Core/CreateEngineeringChangeOrderListEbizProvABCSImpl/V1}CreateEngineeringChangeOrderListEbizProvABCSImpl','CUSTOM.TRANSFORMATIONS.EBM_TO_ABM',false())='true'">

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

</xsl:when>

<xsl:otherwise>

<db:InputParameters>

</xsl:otherwise>

</xsl:choose>

</xsl:template>

AIA configurations file

<ServiceConfiguration serviceName="{http://xmlns.oracle.com/ABCSImpl/Ebiz/Core/CreateEngineeringChangeOrderListEbizProvABCSImpl/V1}CreateEngineeringChangeOrderListEbizProvABCSImpl">

<Property name="CUSTOM.TRANSFORMATIONS.EBM_TO_ABM">false</Property>

<Property name="CUSTOM.TRANSFORMATIONS.ABM_TO_EBM">false</Property>

……………………….

……………………….

</ServiceConfiguration>

If the CUSTOM.TRANSFORMATIONS.EBM_TO_ABM parameter in AIA properties file is set to true it will completely skip the main XSL file and use the Custom mapping file. You will need to do all the mapping in the custom XSL as the main XSL is skipped.

O2C flow & MDM Product Flow

This looks like one of the earliest PIP’s developed as no standards for the transformations has been followed. All the XSL’s (main XSL and Custom XSL) are tightly couple to the process .So any customizations on these XSL’s have to be done inside the BPEL_HOME/domains/default/tmp folder.

Best Practices

• In AIA all the artifacts including ABO, EBO, ABM, EBM XSD’s and EBS WSDL’s are supposed to be placed in a common server location (ORACLE_HOME\ Apache\Apache\htdocs\AIAComponents) and accessed via the server host URL –http://serverhostname:port/AIAComponents.
• All the transformation files (mapping XSL files) should be uploaded in the Apache location and participating processes must be independent of any XSL files. The most common customizations that will be done on pre-integration packs will be adding new custom fields and new mappings. If all these objects are referred from a location outside the process the impact to the processes due to changes can be kept to minimum.
• A property in AIA configurations file should be used to decide whether to use either main XSL or custom XSL. It will help us in isolating all the mappings to a single file instead of looking around in both the XSL to figure out an issue.

That’s it for today. Special thanks to Sandhya, Nikhil, Anil and Rajesh for their inputs.