Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Should whitespace be preserved by default?

GaffJun 26 2009 — edited Jul 29 2011
Hi:

I'm using xmltype.transform() to apply a stylesheet to an XML document and it seems to be ignoring whatever attempts I make to have spaces in certain places. Is the default to strip spaces out? I've even tried adding preserve-whitespace in the stylesheet though I think it may be deprecated. This didn't fix things either.

Here's the procedure that loads the data and applies the transform.
PROCEDURE GetSQLQueryFromXML(XMLClob in CLOB, XSLStylesheet in CLOB,
            SQLQuery out CLOB) IS

  
    -- Define the local variables
  xmldata               sys.XMLType ;  -- The XMLType format of the XML to transform
  xsldata               sys.XMLType ;  -- The XMLType format of the stylesheet to apply
  sqlQuery_XMLType      sys.XMLType ;  -- The XMLType format of the SQL query.
  --v_SQLQuery            Clob;     -- Holds XML Clob 
  

BEGIN

 
  -- Get the XML document using the getXML() function defined in the database.
  -- Since XMLType.transform() method takes XML data as XMLType instance,
  -- use the XMLType.createXML method to convert the XML content received 
  -- as CLOB into an XMLType instance.
  
  xmldata := XMLType.createXML(XMLClob);
    
  
  -- Since XMLType.transform() method takes an XSL stylesheet as XMLType instance,
  -- use the XMLType.createXML method to convert the XSL content received as CLOB
  -- into an XMLType instance.
  
  xsldata := XMLType.createXML(XSLStylesheet);
    
  -- Use the XMLtype.transform() function to get the transformed XML instance.
  -- This function applies the stylesheet to the XML document and returns a transformed
  -- XML instance.
  -- DBMS_XMLGEN.CONVERT() is being used as a work around to an Oracle bug that
  -- prevented us from being able to surround text strings with apostrophes for
  -- searches.  dbms_xlmgen.convert returns a Clob which is what we want anyway.
  -- The bug is supposedly fixed in Oracle 11g.

  
  if(xmldata is null) then
    dbms_output.put_line('*** xmldata is null!!!');
  end if;
  
  if(xsldata is null) then
    dbms_output.put_line('*** xsldata is null!!!');
  end if;
  
  
  SQLQuery := dbms_xmlgen.convert(xmldata.transform(xsldata).getStringVal(),
                dbms_xmlgen.ENTITY_DECODE);
                     
  

END GetSQLQueryFromXML;
What I'm trying to do is transform some XML into a SQL query string. I'm losing whitespace though so in certain cases (using LIKE or NOT LIKE) it generates improper SQL. This isn't an Oracle XML thing because I get the same error when working in a "playground" site. ( http://www.paulchaplin.com/lab/xslt/ pretty handy!)

My test XML file is:
<?xml version="1.0" encoding="ISO-8859-1"?>

    
<variable type="QueryContent">
  <queryType><![CDATA[PERSON]]></queryType>
  <tableName><![CDATA[PERSON]]></tableName>
  <queryItem>
  <item>
    <distinct><![CDATA[false]]></distinct>
    <columnName><![CDATA[PERSON_KEY]]></columnName>
    <fieldName><![CDATA[PERSON_KEY]]></fieldName>
    <criteria><![CDATA[=]]></criteria>
    <fieldType><![CDATA[INTEGER]]></fieldType>
    <value><![CDATA[8901234]]></value>
  </item>
  <item>
    <distinct><![CDATA[true]]></distinct> 
    <columnName><![CDATA[LAST_NAME]]></columnName>
    <fieldName><![CDATA[LAST_NAME]]></fieldName>
    <criteria><![CDATA[=]]></criteria>
    <fieldType><![CDATA[STRING]]></fieldType>
    <value><![CDATA[SMITH]]></value>
  </item>
  <item>
    <distinct><![CDATA[false]]></distinct> 
    <columnName><![CDATA[FIRST_NAME]]></columnName>
    <fieldName><![CDATA[FIRST_NAME]]></fieldName>
    <criteria><![CDATA[=]]></criteria>
    <fieldType><![CDATA[STRING]]></fieldType>
    <value><![CDATA[JANE]]></value>
  </item>
  <item>
    <distinct><![CDATA[false]]></distinct> 
    <columnName><![CDATA[MIDDLE_NAME]]></columnName>
    <fieldName><![CDATA[MIDDLE_NAME]]></fieldName>
    <criteria><![CDATA[=]]></criteria>
    <fieldType><![CDATA[STRING]]></fieldType>
    <value><![CDATA[MIDNAME]]></value>
  </item>
  <item>
    <distinct><![CDATA[false]]></distinct> 
    <columnName><![CDATA[SUFFIX_NAME]]></columnName>
    <fieldName><![CDATA[SUFFIX_NAME]]></fieldName>
    <criteria><![CDATA[=]]></criteria>
    <fieldType><![CDATA[STRING]]></fieldType>
    <value><![CDATA[MS]]></value>
  </item> 
  <item>
    <distinct><![CDATA[false]]></distinct> 
    <columnName><![CDATA[FULL_NAME]]></columnName>
    <fieldName><![CDATA[FULL_NAME]]></fieldName>
    <criteria><![CDATA[=]]></criteria>
    <fieldType><![CDATA[STRING]]></fieldType>
    <value><![CDATA[SMITH, JANE]]></value>
  </item>
  <item>
    <distinct><![CDATA[false]]></distinct> 
    <columnName><![CDATA[EMPLOYER_ORG_CODE]]></columnName>
    <fieldName><![CDATA[EMPLOYER_ORG_CODE]]></fieldName>
    <criteria></criteria>
    <fieldType><![CDATA[STRING]]></fieldType>
    <value />
  </item>
  <item>
    <distinct><![CDATA[true]]></distinct> 
    <columnName><![CDATA[SSN]]></columnName>
    <fieldName><![CDATA[SSN]]></fieldName>
    <criteria><![CDATA[=]]></criteria>
    <fieldType><![CDATA[STRING]]></fieldType>
    <value><![CDATA[222222222]]></value>
  </item> 
  <item>
    <distinct><![CDATA[false]]></distinct> 
    <columnName><![CDATA[GRADE_CODE]]></columnName>
    <fieldName><![CDATA[GRADE_CODE]]></fieldName>
    <criteria></criteria>
    <fieldType><![CDATA[PICKLIST]]></fieldType>
    <value/>
  </item>
  <item>
    <distinct><![CDATA[false]]></distinct> 
    <columnName><![CDATA[POB_CITY]]></columnName>
    <fieldName><![CDATA[POB_CITY]]></fieldName>
    <criteria><![CDATA[=]]></criteria>
    <fieldType><![CDATA[STRING]]></fieldType>
    <value><![CDATA[PASCAGOULA]]></value>
  </item>
  <item>
    <distinct><![CDATA[false]]></distinct> 
    <columnName><![CDATA[POB_COUNTY]]></columnName>
    <fieldName><![CDATA[POB_COUNTY]]></fieldName>
    <criteria><![CDATA[=]]></criteria>
    <fieldType><![CDATA[STRING]]></fieldType>
    <value><![CDATA[JACKSON]]></value>
  </item>  
  <item>
    <distinct><![CDATA[false]]></distinct> 
    <columnName><![CDATA[POB_STATE_CODE]]></columnName>
    <fieldName><![CDATA[POB_STATE_CODE]]></fieldName>
    <criteria><![CDATA[=]]></criteria>
    <fieldType><![CDATA[PICKLIST]]></fieldType>
    <value><![CDATA[MS]]></value>
  </item>
  <item>
    <distinct><![CDATA[false]]></distinct> 
    <columnName><![CDATA[POB_COUNTRY_CODE]]></columnName>
    <fieldName><![CDATA[POB_COUNTRY_CODE]]></fieldName>
    <criteria><![CDATA[=]]></criteria>
    <fieldType><![CDATA[STRING]]></fieldType>
    <value><![CDATA[USA]]></value>
  </item>
  <item>
    <distinct><![CDATA[false]]></distinct> 
    <columnName><![CDATA[BIRTH_DATE]]></columnName>
    <fieldName><![CDATA[BIRTH_DATE]]></fieldName>
    <criteria><![CDATA[=]]></criteria>
    <fieldType><![CDATA[DATE]]></fieldType>
    <fieldFormat>DD-MON-YYYY</fieldFormat>
    <value><![CDATA[11-NOV-1911]]></value>
  </item>
  <item>
    <distinct><![CDATA[false]]></distinct> 
    <columnName><![CDATA[SX]]></columnName>
    <fieldName><![CDATA[SX]]></fieldName>
    <criteria><![CDATA[=]]></criteria>
    <fieldType><![CDATA[STRING]]></fieldType>
    <value><![CDATA[F]]></value>
  </item>
  <item>
    <distinct><![CDATA[true]]></distinct> 
    <columnName><![CDATA[SITE_CODE]]></columnName>
    <fieldName><![CDATA[SITE_CODE]]></fieldName>
    <criteria><![CDATA[NOT LIKE]]></criteria>
    <fieldType><![CDATA[STRING]]></fieldType>
    <value><![CDATA]></value>

</item>

<item>

<distinct><![CDATA[false]]></distinct> 

<columnName><![CDATA[JOB_TITLE]]></columnName>

<fieldName><![CDATA[JOB_TITLE]]></fieldName>

<criteria><![CDATA[LIKE]]></criteria>

<fieldType><![CDATA[STRING]]></fieldType>

<value><![CDATA[CLERK]]></value>

</item>

</queryItem>

</variable>

My XSL file is:
<?xml version="1.0" encoding="ISO-8859-1"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">





<xsl:output method="text" omit-xml-declaration="yes" indent="no"/>





<!-- IF there are any column flagged as distinct, we'll be doing selecting -->

<!-- just those columns with DISTINCT from the general query. -->



<!-- Generate the DISTINCT outer query first, if there is one. Then generate -->

<!-- the query that will always be generated. Then close the outer query if -->

<!-- there is one. -->





<xsl:preserve-space elements="*" />

<xsl:variable name="aspace"> </xsl:variable>



<xsl:template match="/">

<xsl:apply-templates select="." mode="distincts"/>

<xsl:apply-templates select="." mode="normal"/>

<xsl:apply-templates select="." mode="closeDistincts"/>

</xsl:template>





<!-- If there are any column flagged with DISTINCT we'll create an outer query -->



<xsl:template match="/" mode="distincts">

<xsl:for-each select="/variable/queryItem/item/distinct[text() = 'true']">

<xsl:variable name="distinctCount" select="position()" />

<xsl:if test="$distinctCount = 1"> 

<xsl:text>SELECT DISTINCT </xsl:text> 

</xsl:if>

<xsl:if test="$distinctCount &gt; 1"> 

<xsl:text>, </xsl:text>

</xsl:if>

<xsl:value-of select="../columnName"/>

</xsl:for-each>



<!-- If we had any DISTINCT elements, generate the "FROM (" clause. -->

<xsl:if test="count(/variable/queryItem/item/distinct[text() = 'true']) > 0">

<xsl:text> FROM (</xsl:text>

</xsl:if>

</xsl:template>



<!-- If we did a DISTINCT outer query then we need to close it with a ')' -->

<xsl:template match="/" mode="closeDistincts">

<xsl:for-each select="/variable/queryItem/item/distinct[text() = 'true']">

<xsl:variable name="distinctCount" select="position()" />

<xsl:if test="$distinctCount &lt; 2"> 

<xsl:text>) </xsl:text>

</xsl:if>

</xsl:for-each>

</xsl:template>



<!-- This will generate a query. It may or not be an inner query. -->

<xsl:template match="/" mode="normal">

<xsl:variable name="apos">'</xsl:variable>





select * from 

<xsl:value-of select="/variable/tableName"/>

where 1=1

<xsl:for-each select="variable/queryItem/item">

<xsl:choose>

<!-- if the value node is not null... -->

<!-- <xsl:when test="./value/text()[normalize-space(.)]"> -->

<xsl:when test="./value/text()">



AND

<xsl:value-of select="./fieldName"/> 

<xsl:copy-of select="$aspace"/><xsl:value-of select="./criteria"/><xsl:copy-of select="$aspace"/>

<xsl:choose>

<xsl:when test="string(./fieldType)='NUMBER'">

<xsl:value-of select="./value"/>

</xsl:when>

<xsl:when test="string(./fieldType)='INTEGER'">

<xsl:value-of select="./value"/>

</xsl:when>

<xsl:otherwise>

<!-- Type is something that we treat as a string compare. -->

<!-- Currently those types are String, CLOB, RADIO and PICKLIST -->

<xsl:copy-of select="$apos"/>

<xsl:value-of select="./value"/>

<xsl:copy-of select="$apos"/>

</xsl:otherwise>

</xsl:choose>

</xsl:when>

</xsl:choose>

</xsl:for-each>

</xsl:template>

</xsl:stylesheet>

Not how the last two predicates need spaces around the operators.  It doesn't matter when the operator is "=" or ">" but "LIKE" and "NOT LIKE" mess things up when trying to run the generated SQL.
SELECT DISTINCT LAST_NAME, SSN, SITE_CODE FROM (





select * from 

PERSON

where 1=1





AND

PERSON_KEY=8901234



AND

LAST_NAME='SMITH'



AND

FIRST_NAME='JANE'



AND

MIDDLE_NAME='MIDNAME'



AND

SUFFIX_NAME='MS'



AND

FULL_NAME='SMITH, JANE'



AND

SSN='222222222'



AND

POB_CITY='PASCAGOULA'



AND

POB_COUNTY='JACKSON'



AND

POB_STATE_CODE='MS'



AND

POB_COUNTRY_CODE='USA'



AND

BIRTH_DATE='11-NOV-1911'



AND

SX='F'



AND

SITE_CODENOT LIKE'B'



AND

JOB_TITLELIKE'CLERK') 

Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 26 2011
Added on Jun 26 2009
#general-xml, #xml
2 comments
8,596 views