3 Replies Latest reply on Sep 20, 2018 8:53 AM by cormaco

    Blob image to XML file

    yoshiishi

      Hi,

       

      Can someone let me know how I can extract out a blob image into an xml file?

      The xml file contains other text data and I would like to include the blob image(s) as well.

      The xml  file is intended to be used to transfer data from one system to another, so I would like to include text as well as blob images.

       

      My coding is current like below and would like to incorporate the blob into it.

       

      CREATE OR REPLACE PROCEDURE CLAIMS.Claims_Xml (XML_ORDERID NUMBER) IS

      Begin

      DECLARE

        claims_xml1  clob;

        v_po_no     varchar2(1000);

      BEGIN

               dbms_output.put_line('1');

        -- Create XML context.

               select xmlserialize(document 

               xmlelement("Claims"

               ,xmlelement("ClientID", '138146')

               ,xmlagg(claim_xml))

             --  as clob indent

              ) into claims_xml1

              from (

               select xmlelement("Claim"

               , xmlelement("ClaimId", a.claimid) 

               , xmlelement("AssetId", a.assetid) 

               , xmlelement("ClaimNo", a.claimno)

               , xmlelement("StatusId", c.descr)

             --  , xmlelement("ClaimDept", deocde(deptcode),'C','Communication','D','Deck','E','Electrical','H','Furniture Hotel','T','Technical Hotel','M','Machinery','V','HVAC')) 

               , xmlelement("Subject", a.subject) 

               , xmlelement("CreatedBy", l.name)

               , xmlelement("DamageDate", a.damagedate)

               , xmlelement("DamageSentDate", a.damagesentdate)

               , xmlelement("DamageApproveDate", a.damageapprovedate)

               , xmlelement("MakerName", a.makername)

               , xmlelement("VendorName", a.vendorname)

               , xmlelement("CompType", a.comptype)

               , xmlelement("SerialNo", a.serialno)

               , xmlelement("DeckFZone", a.deckfzone)

               , xmlelement("YardMarking", a.yardmarking)

               , xmlelement("DefectDesc", a.defectdesc)

               , xmlelement("CurrencyCode",a.currencycode)

               , xmlelement("TotalCost", a.totalcost)        

                 , xmlelement("Repair"

                   , xmlelement("RepairDesc", a.repairdesc)

                   , xmlelement("RepairDate", a.repairdate)

                   , xmlelement("RepairApproveDate", a.repairapprovedate)

                   , xmlelement("RepairSentDate", a.repairsentdate)

                   , xmlelement("ClosedDate", a.closeddate)

                   , xmlelement("DamageApprovedBy", m.name)

                   , xmlelement("RepairApprovedBy", n.name)

                              )

                 , xmlelement("Comment"

                   , xmlelement("Comments", a.comments)

                             ) 

                 , xmlelement("HourlyExpenses"

                   ,(

                      select xmlagg(

                          xmlelement("ExpenseType",j.description

                           , xmlforest( i.workdate as "WorkDate"

                                      , j.description as "ExpenseType"

                                      , i.hours as "Hours"

                                      , j.hourlyrate as "HourlyRate"

                                      , i.currencycode as "CurrencyCode"

                                      , i.cost as "Cost"

                                      )

                                     )

                                    )

                      from claimwork i, claimworkgroup j

                      where a.claimid=i.claimid(+)

                      and i.workgroupid=J.WORKGROUPID(+)

                    )  

                  ) 

                  , xmlelement("Spares"

                   ,(

                      select xmlagg(

                           xmlelement("Spare"

                           , xmlforest( k.partname as "PartName"

                                      , k.makerref as "MakerRef"

                                      , k.quantity as "Quantity"

                                      , k.price as "Price"

                                      , k.currencycode as "CurrencyCode"

                                      , K.INCLUDEINCLAIMCOST as "IncludeInClaimCost"

                                      )

                                     )

                                    )

                      from claimstockitem k

                      where a.claimid=k.claimid(+)

                     )  

                  ) 

                 , xmlelement("Yard"

                   , xmlelement("YardApprovedBy", o.name)

                   , xmlelement("YardNotes", a.yardnotes)

                   , xmlelement("YardApprovedDate", a.yardapproveddate)

                   , xmlelement("YardStatus", a.yardstatus)

                   , xmlelement("YardRejectedDate", a.yardrejecteddate)

                   , xmlelement("YardRejectedBy", p.name)

                              )

             ) as claim_xml 

            from claim a, claimdept b, claimstatus c, claimtype e, amosorderform f, amosworkorder g, asset h, amos.amosuser l,amos.amosuser m

            , amos.amosuser n, amos.amosuser o, amos.amosuser p

            where a.claimid=xml_orderid

            and a.assetid=h.assetid(+)

            and a.statusid=c.statusid(+)

            and a.deptcode=b.deptcode(+)

            and a.claimid=f.claimid(+)

            and a.claimid=g.claimid(+)

            and a.createdby=l.userid(+)

            and a.damageapprovedby=m.userid(+)

            and a.repairapprovedby=n.userid(+)

            and a.yardapprovedby=o.userid(+)

            and a.yardrejectedby=p.userid(+)

             );

        select claimno into v_po_no from claim

        where claimid=  XML_ORDERID;

        v_po_no:= 'CLAIM-'||v_po_no||'.xml';     

        --dbms_output.put_line('2');

        --dbms_output.put_line('3');

        --dbms_output.put_line('4');

        --dbms_output.put_line('order_xml'||order_xml1);

        dbms_output.put_line('6');

        DBMS_XSLPROCESSOR.clob2file('<?xml version="1.0" encoding="UTF-8" standalone="no"?>'||chr(10)||claims_xml1,'e:\amos-sap',v_po_no);

        dbms_output.put_line('7');

      EXCEPTION

        WHEN OTHERS THEN

          DBMS_OUTPUT.put_line(Substr(SQLERRM,1,255));

          --UTL_FILE.fclose(v_file);

      END;

      End;

      /

       

      Thanks in advance.

        • 1. Re: Blob image to XML file
          cormaco

          A typical way to include binary data in a XML document is to use base64 encoding.

          You can find many code examples in the forums how to do this.

          • 2. Re: Blob image to XML file
            yoshiishi

            Manage to find some resource on this and created a function that would do the conversion.

             

            CREATE OR REPLACE function CLAIMS.encode_base64(p_blob_in in blob) return clob is

                v_clob clob;

                v_result clob;

                v_offset integer;

                v_chunk_size binary_integer := (48 / 4) * 3;

                v_buffer_varchar varchar2(48);

                v_buffer_raw raw(48);

              begin

                if p_blob_in is null then

                  return null;

                end if;

                dbms_lob.createtemporary(v_clob, true);

                v_offset := 1;

                for i in 1 .. ceil(dbms_lob.getlength(p_blob_in) / v_chunk_size) loop

                  dbms_lob.read(p_blob_in, v_chunk_size, v_offset, v_buffer_raw);

                  v_buffer_raw := utl_encode.base64_encode(v_buffer_raw);

                  v_buffer_varchar := utl_raw.cast_to_varchar2(v_buffer_raw);

                  dbms_lob.writeappend(v_clob, length(v_buffer_varchar), v_buffer_varchar);

                  v_offset := v_offset + v_chunk_size;

                end loop;

                v_result := v_clob;

               

               

                dbms_lob.freetemporary(v_clob);

                return v_result;

              end encode_base64;

            /

             

            My modified code:

             

            CREATE OR REPLACE PROCEDURE CLAIMS.Claims_Xml (XML_ORDERID NUMBER) IS

            Begin

            DECLARE

              claims_xml1  clob;

              v_po_no     varchar2(1000);

            BEGIN

                     dbms_output.put_line('1');

              -- Create XML context.

                     select xmlserialize(document 

                     xmlelement("Claims"

                     ,xmlelement("ClientID", '138146')

                     ,xmlagg(claim_xml))

                   --  as clob indent

                    ) into claims_xml1

                    from (

                     select xmlelement("Claim"

                     , xmlelement("ClaimId", a.claimid) 

                     , xmlelement("AssetId", a.assetid) 

                     , xmlelement("ClaimNo", a.claimno)

                     , xmlelement("StatusId", c.descr)

                   --  , xmlelement("ClaimDept", deocde(deptcode),'C','Communication','D','Deck','E','Electrical','H','Furniture Hotel','T','Technical Hotel','M','Machinery','V','HVAC')) 

                     , xmlelement("Subject", a.subject) 

                     , xmlelement("CreatedBy", l.name)

                     , xmlelement("DamageDate", a.damagedate)

                     , xmlelement("DamageSentDate", a.damagesentdate)

                     , xmlelement("DamageApproveDate", a.damageapprovedate)

                     , xmlelement("MakerName", a.makername)

                     , xmlelement("VendorName", a.vendorname)

                     , xmlelement("CompType", a.comptype)

                     , xmlelement("SerialNo", a.serialno)

                     , xmlelement("DeckFZone", a.deckfzone)

                     , xmlelement("YardMarking", a.yardmarking)

                     , xmlelement("DefectDesc", a.defectdesc)

                     , xmlelement("CurrencyCode",a.currencycode)

                     , xmlelement("TotalCost", a.totalcost)        

                       , xmlelement("Repair"

                         , xmlelement("RepairDesc", a.repairdesc)

                         , xmlelement("RepairDate", a.repairdate)

                         , xmlelement("RepairApproveDate", a.repairapprovedate)

                         , xmlelement("RepairSentDate", a.repairsentdate)

                         , xmlelement("ClosedDate", a.closeddate)

                         , xmlelement("DamageApprovedBy", m.name)

                         , xmlelement("RepairApprovedBy", n.name)

                                    )

                       , xmlelement("Comment"

                         , xmlelement("Comments", a.comments)

                                   ) 

                       , xmlelement("HourlyExpenses"

                         ,(

                            select xmlagg(

                                xmlelement("ExpenseType",j.description

                                 , xmlforest( i.workdate as "WorkDate"

                                            , j.description as "ExpenseType"

                                            , i.hours as "Hours"

                                            , j.hourlyrate as "HourlyRate"

                                            , i.currencycode as "CurrencyCode"

                                            , i.cost as "Cost"

                                            )

                                           )

                                          )

                            from claimwork i, claimworkgroup j

                            where a.claimid=i.claimid(+)

                            and i.workgroupid=J.WORKGROUPID(+)

                          )  

                        ) 

                        , xmlelement("Images"

                         ,(

                            select xmlagg(

                                xmlelement("Image"

                                 , xmlforest( q.title as "Title"

                                            , encode_base64(q.image) as "ImageXML"

                                            )

                                           )

                                          )

                            from images q

                            where a.claimid=q.claimid(+)

                            )  

                        ) 

                         , xmlelement("Spares"

                         ,(

                            select xmlagg(

                                 xmlelement("Spare"

                                 , xmlforest( k.partname as "PartName"

                                            , k.makerref as "MakerRef"

                                            , k.quantity as "Quantity"

                                            , k.price as "Price"

                                            , k.currencycode as "CurrencyCode"

                                            , K.INCLUDEINCLAIMCOST as "IncludeInClaimCost"

                                            )

                                           )

                                          )

                            from claimstockitem k

                            where a.claimid=k.claimid(+)

                           )  

                        ) 

                       , xmlelement("Yard"

                         , xmlelement("YardApprovedBy", o.name)

                         , xmlelement("YardNotes", a.yardnotes)

                         , xmlelement("YardApprovedDate", a.yardapproveddate)

                         , xmlelement("YardStatus", a.yardstatus)

                         , xmlelement("YardRejectedDate", a.yardrejecteddate)

                         , xmlelement("YardRejectedBy", p.name)

                                    )

                   ) as claim_xml 

                  from claim a, claimdept b, claimstatus c, claimtype e, amosorderform f, amosworkorder g, asset h, amos.amosuser l,amos.amosuser m

                  , amos.amosuser n, amos.amosuser o, amos.amosuser p

                  where a.claimid=xml_orderid

                  and a.assetid=h.assetid(+)

                  and a.statusid=c.statusid(+)

                  and a.deptcode=b.deptcode(+)

                  and a.claimid=f.claimid(+)

                  and a.claimid=g.claimid(+)

                  and a.createdby=l.userid(+)

                  and a.damageapprovedby=m.userid(+)

                  and a.repairapprovedby=n.userid(+)

                  and a.yardapprovedby=o.userid(+)

                  and a.yardrejectedby=p.userid(+)

                   );

              select claimno into v_po_no from claim

              where claimid=  XML_ORDERID;

              v_po_no:= 'CLAIM-'||v_po_no||'.xml';     

              --dbms_output.put_line('2');

              --dbms_output.put_line('3');

              --dbms_output.put_line('4');

              --dbms_output.put_line('order_xml'||order_xml1);

              dbms_output.put_line('6');

              DBMS_XSLPROCESSOR.clob2file('<?xml version="1.0" encoding="UTF-8" standalone="no"?>'||chr(10)||claims_xml1,'e:\amos-sap',v_po_no);

              dbms_output.put_line('7');

            EXCEPTION

              WHEN OTHERS THEN

                DBMS_OUTPUT.put_line(Substr(SQLERRM,1,255));

                --UTL_FILE.fclose(v_file);

            END;

            End;

            /

             

            The xml line is very long, under the tag ImageXML,  would this be correct?

             

            I can't seem to find anything on how I can validate if the xml file is correct in terms of the image being converted.

            How could I verify that the image converted to xml is working as it should?

            • 3. Re: Blob image to XML file
              cormaco

              You can use one of many available tools and services to convert your base64 string back.