There are times when you might need to transform the content of a table, into XML output. There are many ways to do this, using tools such as ODI and OWB. However there are some in-built functions within the Oracle RDBMS which allow you to generate complex XML documents, within SQL.
Known as XMLDB this means that for a given table/sql you could easily generate the XML document required via using the Oracle internal function. Then you can simply create an “XML” view over the top of the base data. Meaning you can quite easily modify the XML document in the future just using a SQL Extraction View, rather than having to re-engineer the solution in a complex GUI such as OWB or ODI.
There is a worked example below. So given a table with this structure, add some data to the table :-
CREATE TABLE “TBL_WEBSVC”
( “CUSTOMERKEY” NUMBER(10,0) NOT NULL ENABLE,
“LAST_UPDATE_DATE” VARCHAR2(50 BYTE),
“DOCUMENTREFERENCEID” VARCHAR2(240 BYTE),
“DOCUMENTORIGINATOR” CHAR(7 BYTE),
“DOCUMENTTYPE” CHAR(8 BYTE),
“SUPPORTERNUMBER” NVARCHAR2(30),
“LEADMEMBERFLAG” CHAR(4 BYTE),
“ORDERINGPERSONFLAG” CHAR(4 BYTE),
“MEMBERFLAG” CHAR(4 BYTE),
“TITLE” VARCHAR2(9 BYTE),
“FIRSTNAME” NVARCHAR2(50),
“LASTNAME” NVARCHAR2(50),
“DATEOFBIRTH” VARCHAR2(20 BYTE),
“TRUSTEDSOURCEDATEOFBIRTHFLAG” CHAR(1 BYTE),
“TRUSTEDSOURCESUPPORTERNAMEFLAG” CHAR(1 BYTE),
“TELEPHONETYPE” VARCHAR2(14 BYTE),
“TELEPHONENUMBER” NVARCHAR2(50),
“TELEPHONENUMBERINACTIVE” CHAR(1 BYTE),
“TRUSTEDSOURCETELEPHONEFLAG” CHAR(1 BYTE),
“EMAILADDRESS” NVARCHAR2(255),
“EMAILADDRESSINACTIVE” CHAR(1 BYTE),
“TRUSTEDSOURCEEMAILFLAG” CHAR(1 BYTE),
“MEMBERADDRESS” CHAR(4 BYTE),
“ORDERINGADDRESS” CHAR(4 BYTE),
“ADDRESSLINE1” NVARCHAR2(50),
“ADDRESSLINE2” NVARCHAR2(50),
“CITY” NVARCHAR2(50),
“COUNTY” NVARCHAR2(50),
“POSTCODE” NVARCHAR2(50),
“COUNTRY” NVARCHAR2(50),
“TRUSTEDSOURCEADDRESSFLAG” CHAR(1 BYTE),
“CONTACTPREFERENCES” VARCHAR2(4000 BYTE),
“TRIPFORMID” NVARCHAR2(30),
“SOURCECODE” CHAR(24 BYTE),
“FUNDCODE” CHAR(3 BYTE),
“OUTLETID” NVARCHAR2(20) NOT NULL ENABLE,
“NT_PROPERTY_NO” NVARCHAR2(30) NOT NULL ENABLE,
“CRM_PROPERTY_NO” NUMBER,
“RECRUITMENTPOINT” CHAR(2 BYTE),
“DONATIONFROMDATE” VARCHAR2(20 BYTE),
“DONATIONTYPE” CHAR(7 BYTE),
“CREATIONCHANNEL” CHAR(12 BYTE),
“DONATIONCATEGORY” CHAR(8 BYTE),
“GIFTAIDMANDATORYQUESTION” VARCHAR2(3 BYTE),
“ACKNOWLEDGEMENTREQDFLAG” CHAR(5 BYTE),
“SIGNDATE” VARCHAR2(20 BYTE),
“PAYMENTMETHOD” NVARCHAR2(30),
“PAYMENTFREQUENCY” CHAR(6 BYTE),
“PAYMENTAMOUNT” VARCHAR2(10 BYTE),
“PAYMENTTERM” CHAR(9 BYTE),
“EXISTINGRECEIPTID” CHAR(1 BYTE),
“CURRENTPROCESSSTATUS” CHAR(1 BYTE),
“WEBSERVICE_CALL” VARCHAR2(100 BYTE)
)
If you needed to produce a complex XML document per transaction from this table, you could generate the following view :-
CREATE OR REPLACE FORCE VIEW “VW_WEBSVC” (“XML_OUT”, “WEBSERVICE_CALL”, “CUSTOMERKEY”, “DOCUMENTREFERENCEID”, “CRM_PROPERTY_NO”) AS
select
XMLElement(“DocumentReference”,
XMLForest(vw.DocumentReferenceID as “DocumentReferenceID”),
XMLForest(vw.DocumentOriginator as “DocumentOriginator”),
XMLForest(vw.DocumentType as “DocumentType”)
) ||
XMLElement(“Supporters”,
XMLElement(“Supporter”,
case when vw.Supporternumber > 99 then
XMLForest(vw.Supporternumber as “SupporterNumber”)
else
XMLForest(” as “SupporterNumber”)
end,
XMLForest(vw.LeadMemberFlag as “LeadMemberFlag”),
–XMLForest(vw.Supporternumber as “SupporterNumber”),
XMLForest(vw.OrderingPersonFlag as “OrderingPersonFlag”),
XMLForest(vw.MemberFlag as “MemberFlag”),
XMLForest(vw.Title as “Title”),
XMLForest(vw.FIRSTNAME as “FirstName”),
XMLForest(vw.LASTNAME as “LastName”),
XMLForest(vw.DATEOFBIRTH as “DateOfBirth”),
XMLForest(vw.TrustedSourceDateofBirthFlag as “TrustedSourceDateofBirthFlag”),
XMLForest(vw.TrustedSourceSupporterNameFlag as “TrustedSourceSupporterNameFlag”),
–Only Send ContactPoints section if TELEPHONENUMBER or EMAILADDRESS are NOT NULL
case when nvl(length(vw.TELEPHONENUMBER),0)+nvl(length(vw.EMAILADDRESS),0) > 0 then
XMLElement(“ContactPoints”,
XMLElement(“Contact”,
XMLForest(vw.TELEPHONETYPE as “TelephoneType”),
XMLForest(vw.TELEPHONENUMBER as “TeleponeNumber”),
XMLForest(vw.TELEPHONENUMBERINACTIVE as “TelephoneNumberInactive”),
XMLForest(vw.TRUSTEDSOURCETELEPHONEFLAG as “TrustedSourceTelephoneFlag”),
XMLForest(vw.EMAILADDRESS as “EmailAddress”),
XMLForest(vw.EMAILADDRESSINACTIVE as “EmailAddressInactive”),
XMLForest(vw.TRUSTEDSOURCEEMAILFLAG as “TrustedSourceEmailFlag”)
)
)
end ContactPointsCase,
XMLForest(vw.CONTACTPREFERENCES as “ContactPreferences”)
),
XMLElement(“SupportAddress”,
XMLForest(vw.MemberAddress as “MemberAddress”),
XMLForest(vw.OrderingAddress as “OrderingAddress”),
XMLForest(vw.ADDRESSLINE1 as “AddressLine1”),
XMLForest(vw.ADDRESSLINE2 as “AddressLine2”),
XMLForest(vw.CITY as “City”),
XMLForest(vw.COUNTY as “County”),
XMLForest(vw.POSTCODE as “PostCode”),
XMLForest(vw.COUNTRY as “Country”),
XMLForest(vw.TRUSTEDSOURCEADDRESSFLAG as “TrustedSourceAddressFlag”)
)
) ||
XMLElement(“Donation”,
XMLForest(vw.TRIPFORMID as “TripFormID”),
XMLForest(vw.DONATIONTYPE as “DonationType”),
XMLForest(vw.SOURCECODE as “SourceCode”),
XMLForest(vw.FUNDCODE as “FundCode”),
XMLForest(vw.CRM_PROPERTY_NO as “PropertyNo”),
XMLForest(vw.DONATIONFROMDATE as “DonationFromDate”),
XMLForest(vw.CREATIONCHANNEL as “CreationChannel”),
XMLForest(vw.DONATIONCATEGORY as “DonationCategory”),
XMLForest(vw.GIFTAIDMANDATORYQUESTION as “GiftAidMandatoryQuestion”),
XMLForest(vw.ACKNOWLEDGEMENTREQDFLAG as “AcknowledgementReqdFlag”),
XMLForest(vw.RECRUITMENTPOINT as “RecruitmentPoint”)
) ||
XMLElement(“Gift-Aid”,
XMLForest(vw.SIGNDATE as “SignDate”)
) ||
XMLElement(“PaymentDetails”,
case when lower(vw.PAYMENTMETHOD) = ‘cash’
then
XMLForest(‘Cash’ as “PaymentMethod”)
else
XMLForest(‘CC/DC at Property’ as “PaymentMethod”)
end,
–XMLForest(vw.PAYMENTMETHOD as “Paymentmethod”),
XMLForest(vw.PAYMENTFREQUENCY as “PaymentFrequency”),
XMLForest(vw.PAYMENTAMOUNT as “PaymentAmount”),
XMLForest(vw.PAYMENTTERM as “PaymentTerm”),
XMLForest(” as “ExistingReceiptID”)
) ||
–XMLForest(vw.currentprocessstatus as “CurrentProcessStatus”)
XMLForest(” as “CurrentProcessStatus”) xml_out,
vw.webservice_call,
vw.customerkey,
vw.documentreferenceid,
vw.crm_property_no
from TBL_WEBSVC vw
where 1=1;
Selecting from this view would give you an XML document such as this which can be viewed in a browser to validate the XML tags etc.
<DocumentReference><DocumentReferenceID>19311740681165041</DocumentReferenceID><DocumentOriginator>TILLSBO</DocumentOriginator><DocumentType>Donation</DocumentType></DocumentReference><Supporters><Supporter><SupporterNumber></SupporterNumber><LeadMemberFlag>true</LeadMemberFlag><OrderingPersonFlag>true</OrderingPersonFlag><MemberFlag>true</MemberFlag><Title>Mr.</Title><FirstName>Tom</FirstName><LastName>Bear</LastName><DateOfBirth>13-FEB-1987</DateOfBirth><TrustedSourceDateofBirthFlag>N</TrustedSourceDateofBirthFlag><TrustedSourceSupporterNameFlag>N</TrustedSourceSupporterNameFlag><ContactPoints><Contact><TelephoneType>Mobile</TelephoneType><TeleponeNumber>07890572368</TeleponeNumber><TelephoneNumberInactive>N</TelephoneNumberInactive><TrustedSourceTelephoneFlag>N</TrustedSourceTelephoneFlag><EmailAddress>tbear1302@gmail.com</EmailAddress><EmailAddressInactive>N</EmailAddressInactive><TrustedSourceEmailFlag>N</TrustedSourceEmailFlag></Contact></ContactPoints><ContactPreferences>E1,Y,M1,Y</ContactPreferences></Supporter><SupportAddress><MemberAddress>true</MemberAddress><OrderingAddress>true</OrderingAddress><AddressLine1>11 The Green</AddressLine1><AddressLine2>Ashley</AddressLine2><City>Newmarket</City><County>Cambridgeshire</County><PostCode>Cb8 9eb</PostCode><Country>United Kingdom</Country><TrustedSourceAddressFlag>N</TrustedSourceAddressFlag></SupportAddress></Supporters><Donation><TripFormID>117</TripFormID><DonationType>One Off</DonationType><SourceCode>No Campaign / offer code</SourceCode><FundCode>803</FundCode><PropertyNo>8473</PropertyNo><DonationFromDate>29-AUG-2015</DonationFromDate><CreationChannel>Face to Face</CreationChannel><DonationCategory>Donation</DonationCategory><GiftAidMandatoryQuestion>Yes</GiftAidMandatoryQuestion><AcknowledgementReqdFlag>false</AcknowledgementReqdFlag><RecruitmentPoint>00</RecruitmentPoint></Donation><Gift-Aid><SignDate>29-AUG-2015</SignDate></Gift-Aid><PaymentDetails><PaymentMethod>Cash</PaymentMethod><PaymentFrequency>Annual</PaymentFrequency><PaymentAmount>0.50</PaymentAmount><PaymentTerm>IMMEDIATE</PaymentTerm><ExistingReceiptID></ExistingReceiptID></PaymentDetails><CurrentProcessStatus></CurrentProcessStatus>