Search code examples
jsonxmlxsdoracle-data-integrator

Translating JSON to XML using XSD to keep whitespace

I am currently using ODI to import data that is in JSON format into an Oracle Database. I am currently using the Complex File topology, which requires me to identify the JSON file and the XSD used to translate it to XML before it maps the data into a table in the database.

I have a JSON file called sample.json:

{"DATA_DS": {"G_1": [
    {
        "FIELDA": "Test Data   ",
        "FIELDB": "12345",
        "FIELDC": "   ",
        "FIELDD": null,
        "FIELDE": "   Test Data"
    }
]}}

I have an XSD called sample.xsd:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://TargetNamespace.com/InboundService" xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" targetNamespace="http://TargetNamespace.com/InboundService" elementFormDefault="qualified" nxsd:version="JSON" nxsd:encoding="UTF-8">
   <xsd:element name="Root-Element">
      <xsd:complexType>
         <xsd:sequence>
            <xsd:element name="DATA_DS">
               <xsd:complexType>
                  <xsd:sequence>
                     <xsd:element name="G_1" maxOccurs="unbounded">
                        <xsd:complexType>
                           <xsd:sequence>
                              <xsd:element name="FIELDA" type="xsd:string"/>
                              <xsd:element name="FIELDB" type="xsd:string"/>
                              <xsd:element name="FIELDC" type="xsd:string"/>
                              <xsd:element name="FIELDD" type="xsd:string"/>
                              <xsd:element name="FIELDE" type="xsd:string"/>
                           </xsd:sequence>
                        </xsd:complexType>
                     </xsd:element>
                  </xsd:sequence>
               </xsd:complexType>
            </xsd:element>
         </xsd:sequence>
      </xsd:complexType>
   </xsd:element>
</xsd:schema>

When trying to translate from JSON to XML, it says that it cannot normalize. On further investigation, I noticed that the error occurs due to FIELDC having ONLY space.

I want to be able to keep everything that is inside the double quotes, including space.

I have tried changing the XSD twice, but to no avail.

  1. Created a simpleType and changed the type of every element inside to use the new simpleType:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://TargetNamespace.com/InboundService" xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" targetNamespace="http://TargetNamespace.com/InboundService" elementFormDefault="qualified" nxsd:version="JSON" nxsd:encoding="UTF-8">
   <xsd:element name="Root-Element">
      <xsd:complexType>
         <xsd:sequence>
            <xsd:element name="DATA_DS">
               <xsd:complexType>
                  <xsd:sequence>
                     <xsd:element name="G_1" maxOccurs="unbounded">
                        <xsd:complexType>
                           <xsd:sequence>
                              <xsd:element name="FIELDA" type="stringspace"/>
                              <xsd:element name="FIELDB" type="stringspace"/>
                              <xsd:element name="FIELDC" type="stringspace"/>
                              <xsd:element name="FIELDD" type="stringspace"/>
                              <xsd:element name="FIELDE" type="stringspace"/>
                           </xsd:sequence>
                        </xsd:complexType>
                     </xsd:element>
                  </xsd:sequence>
               </xsd:complexType>
            </xsd:element>
         </xsd:sequence>
      </xsd:complexType>
   </xsd:element>
   <xsd:simpleType name="stringspace">
      <xsd:restriction base="xsd:string">
         <xsd:whiteSpace value="preserve" />
      </xsd:restriction>
   </xsd:simpleType>
</xsd:schema>
  1. Created a simpleType inside each element and identify to preserve whitespace:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://TargetNamespace.com/InboundService" xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" targetNamespace="http://TargetNamespace.com/InboundService" elementFormDefault="qualified" nxsd:version="JSON" nxsd:encoding="UTF-8">
   <xsd:element name="Root-Element">
      <xsd:complexType>
         <xsd:sequence>
            <xsd:element name="DATA_DS">
               <xsd:complexType>
                  <xsd:sequence>
                     <xsd:element name="G_1" maxOccurs="unbounded">
                        <xsd:complexType>
                           <xsd:sequence>
                              <xsd:element name="FIELDA"><xsd:simpleType><xsd:restriction base="xsd:string"><xsd:whiteSpace value="preserve"/></xsd:restriction></xsd:simpleType></xsd:element>
                              <xsd:element name="FIELDB"><xsd:simpleType><xsd:restriction base="xsd:string"><xsd:whiteSpace value="preserve"/></xsd:restriction></xsd:simpleType></xsd:element>
                              <xsd:element name="FIELDC"><xsd:simpleType><xsd:restriction base="xsd:string"><xsd:whiteSpace value="preserve"/></xsd:restriction></xsd:simpleType></xsd:element>
                              <xsd:element name="FIELDD"><xsd:simpleType><xsd:restriction base="xsd:string"><xsd:whiteSpace value="preserve"/></xsd:restriction></xsd:simpleType></xsd:element>
                              <xsd:element name="FIELDE"><xsd:simpleType><xsd:restriction base="xsd:string"><xsd:whiteSpace value="preserve"/></xsd:restriction></xsd:simpleType></xsd:element>
                           </xsd:sequence>
                        </xsd:complexType>
                     </xsd:element>
                  </xsd:sequence>
               </xsd:complexType>
            </xsd:element>
         </xsd:sequence>
      </xsd:complexType>
   </xsd:element>
</xsd:schema>

I am kind of new to the whole JSON,XML,XSD space, so any kind of help will be greatly appreciated. Thanks a lot in advance! :)

Solution

  • You say "it says that it cannot normalize" but you don't say what "it" is.

    There are many ways of converting to JSON to XML, and you haven't said which one you are using. It sounds to me as if "it" is the particular conversion tool or library that you are using, and your problem is with this tool or library.

    Also, rather than paraphrasing an error message, it's best to quote it exactly. There may be people here who recognize it and can tell you exactly what it means.