XMLType/XMLTransform and parameters

While spending time in the OTN XML DB forums, I ran across yet another thread where I learned something new. The thread was Add Namespaces via XQuery to an XML Instance . Besides relearning I still have a lot to learn about XQuery, I learned that the XMLTransform SQL operator accepts a third parm. This makes sense given the two operations, the PL/SQL .transform and SQL XMLTransform, perform the same task. If you look at the 11.2 documentation for XMLTransformation, it still shows it accepts only two parms. The XMLType.Transform function shows a third parm, parammap, which is a VARCHAR2.

Given my lack of understanding of the third parm in general, I decided to research it. From what little I was able to turn up, it is described in more detail in Metalink. I am currently without Metalink access so I'm left to wonder as well what is said. I was able to piece together the following examples of a few ways to send in multiple parms.

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
 
SQL> set serveroutput on;
SQL> 
SQL> DECLARE
  2    l_xml      XMLTYPE := XMLTYPE('<root/>');
  3    l_xsd      XMLTYPE;
  4    l_new_xml  XMLTYPE;
  5    l_val1     VARCHAR2(5) := 'val1';
  6    l_val2     VARCHAR2(5) := 'val 2';
  7  BEGIN
  8    l_xsd := XMLTYPE('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                                            xmlns:fo="http://www.w3.org/1999/XSL/Format" 
                                            exclude-result-prefixes="fo">
  9      <xsl:output method="xml"/>
 10  
 11      <xsl:param name="par1"/>
 12      <xsl:param name="par2"/>
 13  
 14      <xsl:template match="/">
 15          <output>
 16              <param1><xsl:value-of select="$par1"/></param1>
 17              <param2><xsl:value-of select="$par2"/></param2>
 18          </output>
 19      </xsl:template>
 20  </xsl:stylesheet>');
 21  
 22    dbms_output.put_line('#1');  -- hard coded strings
 23    l_new_xml := l_xml.transform(l_xsd, 'par1="''val1''"  par2="''val 2''"');
 24    dbms_output.put_line(l_new_xml.getStringVal());
 25  
 26    dbms_output.put_line('#2');  -- hard coded strings
 27    l_new_xml := l_xml.transform(l_xsd, q'{par1="'val1'" par2="'val 2'"}');
 28    dbms_output.put_line(l_new_xml.getStringVal());
 29  
 30    dbms_output.put_line('#3');  -- passing in variables
 31    l_new_xml := l_xml.transform(l_xsd, 'par1="''' || l_val1 || '''" par2="''' || l_val2 || '''"');
 32    dbms_output.put_line(l_new_xml.getStringVal());
 33  
 34    dbms_output.put_line('#4');  -- hard coded string/number
 35    l_new_xml := l_xml.transform(l_xsd, 'par1="''val1''"  par2="2"');
 36    dbms_output.put_line(l_new_xml.getStringVal());
 37  
 38    dbms_output.put_line('#5');  -- hard coded string/number
 39    l_new_xml := l_xml.transform(l_xsd, q'{par1="'val1'" par2="2"}');
 40    dbms_output.put_line(l_new_xml.getStringVal());
 41  END;
 42  /
 
#1
<output>
<param1>val1</param1>
<param2>val 2</param2>
</output>

#2
<output>
<param1>val1</param1>
<param2>val 2</param2>
</output>

#3
<output>
<param1>val1</param1>
<param2>val 2</param2>
</output>

#4
<output>
<param1>val1</param1>
<param2>2</param2>
</output>

#5
<output>
<param1>val1</param1>
<param2>2</param2>
</output>
 
PL/SQL procedure successfully completed

As the Oracle documentation says, the parameters are a "string of name=value pairs". What the online documentation leaves out are examples regarding usage.

So what can we learn from the working examples above?

• If the value is a string, the right side formatted is as "'value'". Note it is wrapped with a leading/trailing '. Depending upon how you setup your parameter, this may mean the use of two single quotes to insert one single quote in the resolved string.
  
• If the value is a number, the right side formatted is as "value". No leading/trailing ' is needed. You can treat the number as a string but then the above rule applies.
  
• Spaces are allowed within a value.
  
• Everything can be treated as a string or number that is being passed into the .xsl
  
• A space, one or more, is used to separate pairs.
  
• The common error reported when not following the above noticed rules was an "ORA-31020: The operation is not allowed, Reason: Invalid XSL Parameter or its Value". I did see a few others, including killing my session as the OTN forum thread noted when randomly throwing data in the third parm.
  

I'm not sure where the q'{}' structure comes from but it means no need for putting double single quotes around the string values as shown in #2 and #5.

As that forum post shows, and the below example as well, you can pass the third parameter into XMLTransform and it will work appropriately.

SQL> SELECT XMLSerialize(DOCUMENT
  2           XMLTransform(XMLTYPE('<root/>'),
  3                        XMLTYPE('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  4                                              xmlns:fo="http://www.w3.org/1999/XSL/Format"
  5                                              exclude-result-prefixes="fo">
  6      <xsl:output method="xml"/>
  7  
  8     <xsl:param name="par1"/>
  9     <xsl:param name="par2"/>
 10  
 11     <xsl:template match="/">
 12        <output>
 13           <param1><xsl:value-of select="$par1"/></param1>
 14           <param2><xsl:value-of select="$par2"/></param2>
 15          </output>
 16      </xsl:template>
 17  </xsl:stylesheet>'),
 18         'par1="''val1''"  par2="2"')
 19           AS CLOB INDENT) xsl_output
 20    FROM dual;
 
XSL_OUTPUT
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?>
<output>
  <param1>val1</param1>
  <param2>2</param2>
</output>

You can replace the third parameter with any of the examples shown above, assuming you setup variable for #3, and the results will be identical to that shown above. Note: I simply used XMLSerialize to format the output similar to how the PL/SQL code formatted it.

So now we know the basics for how to pass parameters to stylesheet and that you can do it with XMLTransform as well, even though the documentation doesn't specify it.