This discussion is archived
5 Replies Latest reply: Feb 12, 2013 8:17 AM by odie_63 RSS

Preserve spaces in XMLTYPE from file

988994 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks odie! I will try this out. Thanks. Added the db version btw (11.2).
  • 4. Re: Preserve spaces in XMLTYPE from file
    988994 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points