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).