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.