5 Replies Latest reply: Feb 12, 2013 10:17 AM by odie_63 RSS

    Preserve spaces in XMLTYPE from file

    988994
      Hi,

      I'm having trouble preserving spaces in xml tag values when reading from file as XmlType.

      Oracle version: 11.2.

      Table to store the xml:
      CREATE OR REPLACE GLOBAL TEMPORARY TABLE GPH_XML_TAB OF XMLType
      ON COMMIT PRESERVE ROWS
      XMLTYPE STORE AS BINARY XML
      XMLSCHEMA "PartsRequest.xsd" ELEMENT "Parts";

      Loading the table:
      INSERT INTO GPH_XML_TAB VALUES(XMLTYPE(bfilename('XML_DIR', 'parts.xml'), NLS_CHARSET_ID('WE8ISO8859P1')));

      parts.xml with only spaces in the FunctionGroup tag:
      <?xml version="1.0" encoding="Windows-1252"?>
      <Parts>
      <Part>
      <RecordType>002</RecordType>
      <FunctionGroup> </FunctionGroup>
      </Part>
      </ns0:Parts>

      This results in a CLOB object with an empty "<FunctionGroup/>" tag, the spaces are lost. This is my problem.

      A value like "<FunctionGroup> a</FunctionGroup>" preserves the spaces.

      I've tried setting an xsd restriction with no success:
      <xs:element minOccurs="0" maxOccurs="1" name="FunctionGroup">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:whiteSpace value="preserve"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>

      Any help on this would be appreciated. Thanks.

      Edited by: user2515287 on 2013-feb-04 23:10
      Added DB version.
        • 1. Re: Preserve spaces in XMLTYPE from file
          odie_63
          Hi,

          This should be posted in the {forum:id=34} forum.

          What's your db version?

          I confirm it doesn't work on 11.2.0.2 using a schema-based binary XML table.
          However, it works with Object-Relational storage :
          SQL> begin
            2  
            3    dbms_xmlschema.registerSchema(
            4      schemaURL => 'PartsRequest.xsd'
            5    , schemaDoc =>
            6  '<?xml version="1.0" encoding="UTF-8"?>
            7  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
            8    <xs:element name="Parts">
            9      <xs:complexType xdb:maintainDOM="false">
           10        <xs:sequence>
           11          <xs:element name="Part" maxOccurs="unbounded">
           12            <xs:complexType xdb:maintainDOM="false">
           13              <xs:sequence>
           14                <xs:element minOccurs="0" name="FunctionGroup" type="xs:string"/>
           15              </xs:sequence>
           16            </xs:complexType>
           17          </xs:element>
           18        </xs:sequence>
           19      </xs:complexType>
           20    </xs:element>
           21  </xs:schema>'
           22    , local => true
           23    , genTypes => true
           24    , genTables => false
           25    , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
           26    --, options => dbms_xmlschema.REGISTER_BINARYXML
           27    ) ;
           28  
           29  end;
           30  /
           
          PL/SQL procedure successfully completed
           
          SQL> 
          SQL> CREATE TABLE GPH_XML_TAB OF XMLType
            2  XMLTYPE STORE AS OBJECT RELATIONAL
            3  XMLSCHEMA "PartsRequest.xsd" ELEMENT "Parts";
           
          Table created
           
          SQL> 
          SQL> INSERT INTO GPH_XML_TAB VALUES(
            2    XMLTYPE(bfilename('TEST_DIR', 'parts.xml'), NLS_CHARSET_ID('WE8MSWIN1252') )
            3    );
           
          1 row inserted
           
          SQL> set long 5000
          SQL> select xmlserialize(document object_value) from gph_xml_tab;
           
          XMLSERIALIZE(DOCUMENTOBJECT_VA
          --------------------------------------------------------------------------------
          <?xml version="1.0" encoding="UTF-8"?>
          <Parts>
            <Part>
              <FunctionGroup> </FunctionGroup>
            </Part>
          </Parts>
           
          • 2. Re: Preserve spaces in XMLTYPE from file
            ranit B
            Hi Odie,

            Could you please explain the working of the above code?
            How exactly is this working?

            Help much appreciated.

            Thanks,
            Ranit
            • 3. Re: Preserve spaces in XMLTYPE from file
              988994
              Thanks odie! I will try this out. Thanks. Added the db version btw (11.2).
              • 4. Re: Preserve spaces in XMLTYPE from file
                988994
                Indeed this works, the spaces are preserved. Unfortunately the OBJECT RELATIONAL store type is not supported in temporary tables, leaving me with quite a bit work avoiding concurrency problems using regular tables.
                • 5. Re: Preserve spaces in XMLTYPE from file
                  odie_63
                  user2515287 wrote:
                  Unfortunately the OBJECT RELATIONAL store type is not supported in temporary tables, leaving me with quite a bit work avoiding concurrency problems using regular tables.
                  That's assuming you're commiting in the middle of your process, if so you can create a relational table with a "SESSION_ID" of some kind to handle data privacy across sessions, and of course the schema-based XMLType column :
                  CREATE TABLE GPH_XML_TAB_TMP (session_id number, doc XMLType)
                  XMLTYPE column doc STORE AS object relational
                  XMLSCHEMA "PartsRequest.xsd" ELEMENT "Parts" ;
                  If you do so, don't forget to set "genTables => false" in schema registration.