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