3 Replies Latest reply on Nov 27, 2018 11:15 AM by Pierre Yotti

    XML To JSON Conversion Shows Binary Content

    3840123

      When trying convert XML response to JSON, it got binary-content.

      Already read the document oracle and it said expected behavior based on the contents being converted.

       

      But I need to see the result on string value, anyone have solution for this issue ?

       

      Thanks

        • 1. Re: XML To JSON Conversion Shows Binary Content
          Pierre Yotti

          3840123 wrote:

          Please update your forum profile with a recognisable username instead of "3840123".

           

          When trying convert XML response to JSON, it got binary-content.

          Already read the document oracle and it said expected behavior based on the contents being converted.

           

          But I need to see the result on string value, anyone have solution for this issue ?

           

          Thanks

           

          Please give us all the details if you asks a question. Oracle database.

          We can not debug something that we can not see.

           

           

          Here is a examples that works

           

          - example1

           

          with  xml_tab as (     
            select '<employees>
            <employee>
              <empno>7369</empno>
              <ename>SMITH</ename>
              <job>CLERK</job>
              <hiredate>17-DEC-1980</hiredate>
            </employee>
            <employee>
              <empno>7499</empno>
              <ename>ALLEN</ename>
              <job>SALESMAN</job>
              <hiredate>20-FEB-1981</hiredate>
            </employee>
            <employee>
              <empno>7521</empno>
              <ename>WARD</ename>
              <job>SALESMAN</job>
              <hiredate>22-FEB-1981</hiredate>
            </employee>
            <employee>
              <empno>7566</empno>
              <ename>JONES</ename>
              <job>MANAGER</job>
              <hiredate>02-APR-1981</hiredate>
            </employee>
            <employee>
              <empno>7654</empno>
              <ename>MARTIN</ename>
              <job>SALESMAN</job>
              <hiredate>28-SEP-1981</hiredate>
            </employee>
            <employee>
              <empno>7698</empno>
              <ename>BLAKE</ename>
              <job>MANAGER</job>
              <hiredate>01-MAY-1981</hiredate>
            </employee>
            <employee>
              <empno>7782</empno>
              <ename>CLARK</ename>
              <job>MANAGER</job>
              <hiredate>09-JUN-1981</hiredate>
            </employee>
            <employee>
              <empno>7788</empno>
              <ename>SCOTT</ename>
              <job>ANALYST</job>
              <hiredate>19-APR-1987</hiredate>
            </employee>
            <employee>
              <empno>7839</empno>
              <ename>KING</ename>
              <job>PRESIDENT</job>
              <hiredate>17-NOV-1981</hiredate>
            </employee>
            <employee>
              <empno>7844</empno>
              <ename>TURNER</ename>
              <job>SALESMAN</job>
              <hiredate>08-SEP-1981</hiredate>
            </employee>
            <employee>
              <empno>7876</empno>
              <ename>ADAMS</ename>
              <job>CLERK</job>
              <hiredate>23-MAY-1987</hiredate>
            </employee>
            <employee>
              <empno>7900</empno>
              <ename>JAMES</ename>
              <job>CLERK</job>
              <hiredate>03-DEC-1981</hiredate>
            </employee>
            <employee>
              <empno>7902</empno>
              <ename>FORD</ename>
              <job>ANALYST</job>
              <hiredate>03-DEC-1981</hiredate>
            </employee>
            <employee>
              <empno>7934</empno>
              <ename>MILLER</ename>
              <job>CLERK</job>
              <hiredate>23-JAN-1982</hiredate>
            </employee>
          </employees>' xml_data
                   from dual)
          SELECT xt.* 
          FROM   xml_tab x, 
                 XMLTABLE('/employees/employee' passing xmltype(x.xml_data) COLUMNS empno 
                 VARCHAR2(4) path 'empno', ename VARCHAR2(10) path 'ename', job VARCHAR2(9 
                 ) path 
                 'job', hiredate VARCHAR2(11) path 'hiredate' ) xt;
          

              

              

          Bildschirmfoto 2018-11-27 um 11.48.46.png

           

          - example2

           

          with  xml_tab as (     
            select '<note>
          <to>Tove</to>
          <from>Jani</from>
          <heading>Reminder</heading>
          <body>Don''t forget me this weekend!</body>
          </note>' xml_data
                   from dual)
          SELECT xt.* 
          FROM   xml_tab x, 
                 XMLTABLE('/note' passing xmltype(x.xml_data) 
                 COLUMNS to_  VARCHAR2(255) path 'to', 
                 from_ VARCHAR2(255) path 'from',
                 heading VARCHAR2(255 ) path 'heading', 
                 body VARCHAR2(255) path 'body' ) xt;
          

           

           

          Bildschirmfoto 2018-11-27 um 11.54.53.png

           

           

          -example 3

          with  xml_tab as (     
            select '<CATALOG>
          <CD>
          <TITLE>Empire Burlesque</TITLE>
          <ARTIST>Bob Dylan</ARTIST>
          <COUNTRY>USA</COUNTRY>
          <COMPANY>Columbia</COMPANY>
          <PRICE>10.90</PRICE>
          <YEAR>1985</YEAR>
          </CD>
          <CD>
          <TITLE>Hide your heart</TITLE>
          <ARTIST>Bonnie Tyler</ARTIST>
          <COUNTRY>UK</COUNTRY>
          <COMPANY>CBS Records</COMPANY>
          <PRICE>9.90</PRICE>
          <YEAR>1988</YEAR>
          </CD>
          <CD>
          <TITLE>Greatest Hits</TITLE>
          <ARTIST>Dolly Parton</ARTIST>
          <COUNTRY>USA</COUNTRY>
          <COMPANY>RCA</COMPANY>
          <PRICE>9.90</PRICE>
          <YEAR>1982</YEAR>
          </CD>
          <CD>
          <TITLE>Still got the blues</TITLE>
          <ARTIST>Gary Moore</ARTIST>
          <COUNTRY>UK</COUNTRY>
          <COMPANY>Virgin records</COMPANY>
          <PRICE>10.20</PRICE>
          <YEAR>1990</YEAR>
          </CD>
          <CD>
          <TITLE>Eros</TITLE>
          <ARTIST>Eros Ramazzotti</ARTIST>
          <COUNTRY>EU</COUNTRY>
          <COMPANY>BMG</COMPANY>
          <PRICE>9.90</PRICE>
          <YEAR>1997</YEAR>
          </CD>
          <CD>
          <TITLE>One night only</TITLE>
          <ARTIST>Bee Gees</ARTIST>
          <COUNTRY>UK</COUNTRY>
          <COMPANY>Polydor</COMPANY>
          <PRICE>10.90</PRICE>
          <YEAR>1998</YEAR>
          </CD>
          <CD>
          <TITLE>Sylvias Mother</TITLE>
          <ARTIST>Dr.Hook</ARTIST>
          <COUNTRY>UK</COUNTRY>
          <COMPANY>CBS</COMPANY>
          <PRICE>8.10</PRICE>
          <YEAR>1973</YEAR>
          </CD>
          <CD>
          <TITLE>Maggie May</TITLE>
          <ARTIST>Rod Stewart</ARTIST>
          <COUNTRY>UK</COUNTRY>
          <COMPANY>Pickwick</COMPANY>
          <PRICE>8.50</PRICE>
          <YEAR>1990</YEAR>
          </CD>
          <CD>
          <TITLE>Romanza</TITLE>
          <ARTIST>Andrea Bocelli</ARTIST>
          <COUNTRY>EU</COUNTRY>
          <COMPANY>Polydor</COMPANY>
          <PRICE>10.80</PRICE>
          <YEAR>1996</YEAR>
          </CD>
          <CD>
          <TITLE>When a man loves a woman</TITLE>
          <ARTIST>Percy Sledge</ARTIST>
          <COUNTRY>USA</COUNTRY>
          <COMPANY>Atlantic</COMPANY>
          <PRICE>8.70</PRICE>
          <YEAR>1987</YEAR>
          </CD>
          <CD>
          <TITLE>Black angel</TITLE>
          <ARTIST>Savage Rose</ARTIST>
          <COUNTRY>EU</COUNTRY>
          <COMPANY>Mega</COMPANY>
          <PRICE>10.90</PRICE>
          <YEAR>1995</YEAR>
          </CD>
          <CD>
          <TITLE>1999 Grammy Nominees</TITLE>
          <ARTIST>Many</ARTIST>
          <COUNTRY>USA</COUNTRY>
          <COMPANY>Grammy</COMPANY>
          <PRICE>10.20</PRICE>
          <YEAR>1999</YEAR>
          </CD>
          <CD>
          <TITLE>For the good times</TITLE>
          <ARTIST>Kenny Rogers</ARTIST>
          <COUNTRY>UK</COUNTRY>
          <COMPANY>Mucik Master</COMPANY>
          <PRICE>8.70</PRICE>
          <YEAR>1995</YEAR>
          </CD>
          <CD>
          <TITLE>Big Willie style</TITLE>
          <ARTIST>Will Smith</ARTIST>
          <COUNTRY>USA</COUNTRY>
          <COMPANY>Columbia</COMPANY>
          <PRICE>9.90</PRICE>
          <YEAR>1997</YEAR>
          </CD>
          <CD>
          <TITLE>Tupelo Honey</TITLE>
          <ARTIST>Van Morrison</ARTIST>
          <COUNTRY>UK</COUNTRY>
          <COMPANY>Polydor</COMPANY>
          <PRICE>8.20</PRICE>
          <YEAR>1971</YEAR>
          </CD>
          <CD>
          <TITLE>Soulsville</TITLE>
          <ARTIST>Jorn Hoel</ARTIST>
          <COUNTRY>Norway</COUNTRY>
          <COMPANY>WEA</COMPANY>
          <PRICE>7.90</PRICE>
          <YEAR>1996</YEAR>
          </CD>
          <CD>
          <TITLE>The very best of</TITLE>
          <ARTIST>Cat Stevens</ARTIST>
          <COUNTRY>UK</COUNTRY>
          <COMPANY>Island</COMPANY>
          <PRICE>8.90</PRICE>
          <YEAR>1990</YEAR>
          </CD>
          <CD>
          <TITLE>Stop</TITLE>
          <ARTIST>Sam Brown</ARTIST>
          <COUNTRY>UK</COUNTRY>
          <COMPANY>A and M</COMPANY>
          <PRICE>8.90</PRICE>
          <YEAR>1988</YEAR>
          </CD>
          <CD>
          <TITLE>Bridge of Spies</TITLE>
          <ARTIST>T''Pau</ARTIST>
          <COUNTRY>UK</COUNTRY>
          <COMPANY>Siren</COMPANY>
          <PRICE>7.90</PRICE>
          <YEAR>1987</YEAR>
          </CD>
          <CD>
          <TITLE>Private Dancer</TITLE>
          <ARTIST>Tina Turner</ARTIST>
          <COUNTRY>UK</COUNTRY>
          <COMPANY>Capitol</COMPANY>
          <PRICE>8.90</PRICE>
          <YEAR>1983</YEAR>
          </CD>
          <CD>
          <TITLE>Midt om natten</TITLE>
          <ARTIST>Kim Larsen</ARTIST>
          <COUNTRY>EU</COUNTRY>
          <COMPANY>Medley</COMPANY>
          <PRICE>7.80</PRICE>
          <YEAR>1983</YEAR>
          </CD>
          <CD>
          <TITLE>Pavarotti Gala Concert</TITLE>
          <ARTIST>Luciano Pavarotti</ARTIST>
          <COUNTRY>UK</COUNTRY>
          <COMPANY>DECCA</COMPANY>
          <PRICE>9.90</PRICE>
          <YEAR>1991</YEAR>
          </CD>
          <CD>
          <TITLE>The dock of the bay</TITLE>
          <ARTIST>Otis Redding</ARTIST>
          <COUNTRY>USA</COUNTRY>
          <COMPANY>Stax Records</COMPANY>
          <PRICE>7.90</PRICE>
          <YEAR>1968</YEAR>
          </CD>
          <CD>
          <TITLE>Picture book</TITLE>
          <ARTIST>Simply Red</ARTIST>
          <COUNTRY>EU</COUNTRY>
          <COMPANY>Elektra</COMPANY>
          <PRICE>7.20</PRICE>
          <YEAR>1985</YEAR>
          </CD>
          <CD>
          <TITLE>Red</TITLE>
          <ARTIST>The Communards</ARTIST>
          <COUNTRY>UK</COUNTRY>
          <COMPANY>London</COMPANY>
          <PRICE>7.80</PRICE>
          <YEAR>1987</YEAR>
          </CD>
          </CATALOG>' xml_data
                   from dual)
          SELECT xt.* 
          FROM   xml_tab x, 
                 XMLTABLE('/CATALOG/CD' passing xmltype(x.xml_data) 
                 COLUMNS TITLE  VARCHAR2(255) path 'TITLE', 
                 ARTIST VARCHAR2(255) path 'ARTIST',
                 COUNTRY VARCHAR2(255 ) path 'COUNTRY', 
                 COMPANY VARCHAR2(255) path 'COMPANY',
                 PRICE VARCHAR2(255) path 'PRICE',
                  YEAR VARCHAR2(255) path 'YEAR') xt;
          

           

              

          Bildschirmfoto 2018-11-27 um 11.59.22.png

          • 2. Re: XML To JSON Conversion Shows Binary Content
            3840123

            Thanks Alli for the response.

            Sorry for the question that not complete.

             

            I'm using OSB Jdev 12c and pipeline using nXSDTranslate (XML to Native).

             

            XML Output is like this :

            <resultStatus>TRUE</resultStatus>

             

            and I want to convert that xml result become JSON output like this :

            { "resultStatus": "TRUE" }

             

            Here is the xsd that created :

            <?xml version = '1.0' encoding = 'UTF-8'?>

            <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://TargetNamespace.com/ServiceName" targetNamespace="http://xmlns.oracle.com/RoamingFeature" elementFormDefault="qualified" xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" nxsd:version="JSON" nxsd:encoding="US-ASCII">

               <xsd:element name="Root-Element">

                  <xsd:complexType>

                     <xsd:sequence>

                                 <xsd:element name="resultStatus" type="xsd:string"/>

                     </xsd:sequence>

                  </xsd:complexType>

               </xsd:element>

            </xsd:schema>

             

            When execute the module will get this result :

            <con:binary-content ref="cid:59c22a5d:N25:16754cdd026:N7ff9" xmlns:con="http://www.bea.com/wli/sb/context"/>