2 Replies Latest reply: Oct 2, 2013 8:28 PM by eduardoEloy RSS

    XMLTYPE Transform works differently in Oracle 10.1 and 11.2

    eduardoEloy

      We are migrating our applications from Oracle 10.1g to 11.2g. We have a number of PL/SQL routines that transform incoming/outgoing XML using the XMLType Transform method. The XSLTs that we use do not seem to work in 11.2. For example, the code below is a simplification of what we're doing. This sample code uses hard-coded input and xslt to illustrate the point. The same code has very different outputs when run in 10.1 and 11.2. In 10.1, the transformation works. It also works using files in an xml tool. In 11.2, none of the input XML is transformed. The only output is the extra <sd:StandardBusinessDocument... that is hard-coded into the xslt as a wrapper.

       

      E.g.

      {code}

      declare

      out_sbd xmltype := xmltype('<OT_SBD_HEADER>

          <HEADER_VERSION>1.0</HEADER_VERSION>

          <SENDERS>

              <OT_SENDER_RECEIVER>

                  <IDENTIFIER>COGSD</IDENTIFIER>

                  <IDENTIFIER_AUTHORITY>urn:olgr.qld.gov.au:cogs</IDENTIFIER_AUTHORITY>

              </OT_SENDER_RECEIVER>

          </SENDERS>

          <RECEIVERS>

              <OT_SENDER_RECEIVER>

                  <IDENTIFIER>PORTAL</IDENTIFIER>

                  <IDENTIFIER_AUTHORITY>urn:olgr.qld.gov.au:portal</IDENTIFIER_AUTHORITY>

              </OT_SENDER_RECEIVER>

          </RECEIVERS>

          <DOCUMENT_INDENTIFICATION>

              <STANDARD>urn:olgr.qld.gov.au</STANDARD>

              <TYPE_VERSION>1.1</TYPE_VERSION>

              <INSTANCE_IDENTIFIER>c11ab0c6-22e7-4132-88f8-f81e88cde75c</INSTANCE_IDENTIFIER>

              <TYPE>licenceInformation</TYPE>

              <CREATION_DATE>2012-05-30T13:55:41.167125+10:00</CREATION_DATE>

          </DOCUMENT_INDENTIFICATION>

          <BUSINESS_SCOPE>

              <OT_SBD_HEADER_SCOPE>

                  <SCOPE_TYPE>get</SCOPE_TYPE>

                  <SCOPE_INSTANCE_IDENTIFIER>GET_GAMING_STATISTICS_DATA</SCOPE_INSTANCE_IDENTIFIER>

              </OT_SBD_HEADER_SCOPE>

          </BUSINESS_SCOPE>

      </OT_SBD_HEADER>');

      --

      transformed_out_sbd xmltype;

      --

      sbd_header_out_xslt xmltype := xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>

      <!--

      This file was generated by Altova MapForce 2011r2sp1

       

      YOU SHOULD NOT MODIFY THIS FILE, BECAUSE IT WILL BE

      OVERWRITTEN WHEN YOU RE-RUN CODE GENERATION.

       

      Refer to the Altova MapForce Documentation for further details.

      http://www.altova.com/mapforce

      -->

      <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="xs">

        <xsl:output method="xml" encoding="UTF-8" indent="yes"/>

        <xsl:template match="/">

          <sd:StandardBusinessDocument xsi:schemaLocation="http://www.unece.org/cefact/namespaces/StandardBusinessDocumentHeader sbd.xsd" xmlns:sd="http://www.unece.org/cefact/namespaces/StandardBusinessDocumentHeader" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

            <xsl:for-each select="*[local-name()=&apos;OT_SBD_HEADER&apos; and namespace-uri()=&apos;&apos;]">

              <xsl:variable name="var1_DOCUMENTINDENTIFICATION" select="*[local-name()=&apos;DOCUMENT_INDENTIFICATION&apos; and namespace-uri()=&apos;&apos;]"/>

              <xsl:variable name="var2_MANIFEST" select="*[local-name()=&apos;MANIFEST&apos; and namespace-uri()=&apos;&apos;]"/>

              <xsl:variable name="var3_resultof_cast" select="string($var1_DOCUMENTINDENTIFICATION/*[local-name()=&apos;MULTIPLE_TYPE&apos; and namespace-uri()=&apos;&apos;])"/>

              <StandardBusinessDocumentHeader>

                <HeaderVersion>

                  <xsl:value-of select="string(HEADER_VERSION)"/>

                </HeaderVersion>

                <xsl:for-each select="SENDERS">

                  <xsl:variable name="var4_OTSENDERRECEIVER" select="OT_SENDER_RECEIVER"/>

                  <Sender>

                    <Identifier>

                      <xsl:attribute name="Authority">

                        <xsl:value-of select="string($var4_OTSENDERRECEIVER/IDENTIFIER_AUTHORITY)"/>

                      </xsl:attribute>

                      <xsl:value-of select="string($var4_OTSENDERRECEIVER/IDENTIFIER)"/>

                    </Identifier>

                  </Sender>

                </xsl:for-each>

                <xsl:for-each select="RECEIVERS">

                  <xsl:variable name="var5_OTSENDERRECEIVER" select="OT_SENDER_RECEIVER"/>

                  <Receiver>

                    <Identifier>

                      <xsl:attribute name="Authority">

                        <xsl:value-of select="string($var5_OTSENDERRECEIVER/IDENTIFIER_AUTHORITY)"/>

                      </xsl:attribute>

                      <xsl:value-of select="string($var5_OTSENDERRECEIVER/IDENTIFIER)"/>

                    </Identifier>

                  </Receiver>

                </xsl:for-each>

                <DocumentIdentification>

                  <Standard>

                    <xsl:value-of select="string($var1_DOCUMENTINDENTIFICATION/STANDARD)"/>

                  </Standard>

                  <TypeVersion>

                    <xsl:value-of select="string($var1_DOCUMENTINDENTIFICATION/TYPE_VERSION)"/>

                  </TypeVersion>

                  <InstanceIdentifier>

                    <xsl:value-of select="string($var1_DOCUMENTINDENTIFICATION/INSTANCE_IDENTIFIER)"/>

                  </InstanceIdentifier>

                  <Type>

                    <xsl:value-of select="string($var1_DOCUMENTINDENTIFICATION/TYPE)"/>

                  </Type>

                  <MultipleType>

                    <xsl:value-of select="string(((normalize-space($var3_resultof_cast) = &apos;true&apos;) or (normalize-space($var3_resultof_cast) = &apos;1&apos;)))"/>

                  </MultipleType>

                  <CreationDateAndTime>

                    <xsl:value-of select="string($var1_DOCUMENTINDENTIFICATION/CREATION_DATE)"/>

                  </CreationDateAndTime>

                </DocumentIdentification>

                <Manifest>

                  <NumberOfItems>

                    <xsl:value-of select="string((string($var2_MANIFEST/NUMBER_OF_ITEMS)))"/>

                  </NumberOfItems>

                  <xsl:for-each select="$var2_MANIFEST/MANIFEST_ITEMS">

                    <xsl:variable name="var6_OTMANIFESTITEM" select="OT_MANIFEST_ITEM"/>

                    <ManifestItem>

                      <MimeTypeQualifierCode>

                        <xsl:value-of select="string($var6_OTMANIFESTITEM/MIME_TYPE)"/>

                      </MimeTypeQualifierCode>

                      <UniformResourceIdentifier>

                        <xsl:value-of select="string($var6_OTMANIFESTITEM/RESOURCE_IDENTIFIER)"/>

                      </UniformResourceIdentifier>

                      <Description>

                        <xsl:value-of select="string($var6_OTMANIFESTITEM/DESCRIPTION)"/>

                      </Description>

                    </ManifestItem>

                  </xsl:for-each>

                </Manifest>

                <xsl:for-each select="BUSINESS_SCOPE">

                  <xsl:variable name="var7_OTSBDHEADERSCOPE" select="OT_SBD_HEADER_SCOPE"/>

                  <BusinessScope>

                    <Scope>

                      <Type>

                        <xsl:value-of select="string($var7_OTSBDHEADERSCOPE/SCOPE_TYPE)"/>

                      </Type>

                      <InstanceIdentifier>

                        <xsl:value-of select="string($var7_OTSBDHEADERSCOPE/SCOPE_INSTANCE_IDENTIFIER)"/>

                      </InstanceIdentifier>

                      <Identifier>

                        <xsl:value-of select="string($var7_OTSBDHEADERSCOPE/SCOPE_IDENTIFIER)"/>

                      </Identifier>

                    </Scope>

                  </BusinessScope>

                </xsl:for-each>

              </StandardBusinessDocumentHeader>

            </xsl:for-each>

          </sd:StandardBusinessDocument>

        </xsl:template>

      </xsl:stylesheet>

      ');

      --

      --

      BEGIN

      transformed_out_sbd := out_sbd.transform (sbd_header_out_xslt); -- transform out_sbd using the xslt hard-coded above

      --

      INSERT INTO z_clob_log (id, clob_name, clob_desc, clob_doc)  --- log the output to a clob column in a table

      SELECT (select nvl(max(id),0)+1 from _clob_log),

                     to_char(sysdate,'yyyymmdd hh24:mi:ss')||' - TRANSFORMED OUT_SBD',

                     transformed_out_sbd.getClobVal

      FROM dual;

      COMMIT;

      END;

      {code}

       

      In 10.1, the output is as expected, the same as doing the transformation using files in an xml tool.

       

      {code}

      <sd:StandardBusinessDocument xsi:schemaLocation="http://www.unece.org/cefact/namespaces/StandardBusinessDocumentHeader sbd.xsd" xmlns:sd="http://www.unece.org/cefact/namespaces/StandardBusinessDocumentHeader" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

        <StandardBusinessDocumentHeader>

          <HeaderVersion>1.0</HeaderVersion>

          <Sender>

            <Identifier Authority="urn:olgr.qld.gov.au:cogs">COGSD</Identifier>

          </Sender>

          <Receiver>

            <Identifier Authority="urn:olgr.qld.gov.au:portal">PORTAL</Identifier>

          </Receiver>

          <DocumentIdentification>

            <Standard>urn:olgr.qld.gov.au</Standard>

            <TypeVersion>1.1</TypeVersion>

            <InstanceIdentifier>c11ab0c6-22e7-4132-88f8-f81e88cde75c</InstanceIdentifier>

            <Type>licenceInformation</Type>

            <MultipleType>false</MultipleType>

            <CreationDateAndTime>2012-05-30T13:55:41.167125+10:00</CreationDateAndTime>

          </DocumentIdentification>

          <Manifest>

            <NumberOfItems/>

          </Manifest>

          <BusinessScope>

            <Scope>

              <Type>get</Type>

              <InstanceIdentifier>GET_GAMING_STATISTICS_DATA</InstanceIdentifier>

              <Identifier/>

            </Scope>

          </BusinessScope>

        </StandardBusinessDocumentHeader>

      </sd:StandardBusinessDocument>

      {code}

       

      In 11.2, none of the original xml document is included in the output, only the <sd: StandardBusinessDocument ..../>

      {code}

      <sd:StandardBusinessDocument xsi:schemaLocation="http://www.unece.org/cefact/namespaces/StandardBusinessDocumentHeader sbd.xsd" xmlns:sd="http://www.unece.org/cefact/namespaces/StandardBusinessDocumentHeader" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>

      {code}

      The same occurs using the SQL XMLTransform function, e.g. applying the following with the above xml and xslt, except it outputs a separate closing tag </sd:Standard...>

      select xmltransform(out_sbd, sbd_header_out_xslt) into transformed_out_sbd from dual;

       

      Q: Any ideas? Are there any changes in 11g 11.2 that require major changes to xslt?

       

      We have a lot of rewriting to do if we cannot get this to work!

        • 1. Re: XMLTYPE Transform works differently in Oracle 10.1 and 11.2
          odie_63

          Hi Eduardo,

           

          Report this as a bug to Oracle Support.

          The problem lies in the namespace-uri() function : the comparison with an empty string is not resolved correctly so no node is ever selected in the first xsl:for-each.

           

          If you need a workaround, there are a few you can test :

           

          1) If input documents actually are in no namespace (such as in your sample), remove every namespace-uri() occurrences from the stylesheet.

           

          2) The other way around, you can give your input document a dummy namespace and now reference it in the stylesheet. The namespace-uri() test will work in this case.

           

          3) Replace occurrences of namespace-uri() with a slightly more elaborate test that'll handle empty namespace specifically, for example :

          <xsl:variable name="nsuri"></xsl:variable>
          ( namespace-uri()=$nsuri or (not($nsuri) and not(namespace-uri())) )

           

           

          Since you're working with generated stylesheets, I understand that none of the above options are really satisfactory.

          If I had to workaround the issue, I'd probably go with the 2nd option which involves a smaller amount of refactoring.

          • 2. Re: XMLTYPE Transform works differently in Oracle 10.1 and 11.2
            eduardoEloy

            Thanks very much odie. I've just tried (1) and (3) with good results. (2) isn't an option for us as in many places we don't have the ability to give the input doc a dummy namespace (as far as I know). Even though we're using an XSLT generator, we can and have modified the generated XSLT.