Newbie for XML coding, help needed

user517202 · Post by **user517202** » Sat Jul 21, 2018 3:35 am

DECLARE

doc xmldom.DOMDocument;

l_xmltype XMLTYPE;

main_node xmldom.DOMNode;

root_node xmldom.DOMNode;

user_node xmldom.DOMNode;

item_node xmldom.DOMNode;

root_elmt xmldom.DOMElement;

item_elmt xmldom.DOMElement;

item_text xmldom.DOMText;

CURSOR cur_bi_hdr

IS

SELECT CREATED_DATE, UUID

FROM BI_HEADER ;

CURSOR cur_bi_sourcesystem

IS

SELECT CLOSED,ESTABLISHMENT,CREATED_AT,ID

FROM BI_SOURCESYSTEM ;

CURSOR cur_bi_transheader

IS

SELECT VOIDED_BY,CLOSED,PARAMETER_VALUE,FINAL_TOTAL

FROM BI_TRANSACTIONHEADER ;

CURSOR cur_bi_transdetail

IS

SELECT ID,ERVC_TYPE,VOIDED_REASON,PARAMETER_VALUE,PRODUCT,BARCODE,PRICE_NOTAX,PRICE_WITHTAX,QUANTITY,PRICE,PURE_SALES,

APPLIED_TAXES,TAX_RATE,TAX_AMOUNT,VOIDED

FROM BI_TRANSACTIONDETAIL ;

BEGIN

doc := xmldom.newDOMDocument;

xmldom.setVersion(doc,'1.0" encoding="UTF-8');

main_node := xmldom.makeNode(doc);

root_elmt := xmldom.createElement(doc, 'ConsumerTransactionList');

root_node := xmldom.appendChild(main_node, xmldom.makeNode(root_elmt));

FOR rec_bi_hdr IN cur_bi_hdr LOOP

item_elmt := xmldom.createElement( doc, 'Header');

xmldom.setAttribute(item_elmt, 'Action', 'CREATE');

user_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));

END LOOP;

FOR rec_bi_hdr IN cur_bi_hdr LOOP

item_elmt := xmldom.createElement( doc, 'ConsumerTransaction');

user_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));

FOR rec_bi_sourcesystem IN cur_bi_sourcesystem

LOOP

item_elmt := xmldom.createElement( doc, 'SourceSystem');

--xmldom.setAttribute(item_elmt, 'Code', 'hunundundint');

user_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));

item_elmt := xmldom.createElement(doc, 'SourceTimestamp');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_sourcesystem.CLOSED);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'BusinessDayDate');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, sysdate);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'AffiliateCode');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, 'CES');

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'MarketCode');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, 'HUN');

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'BrandCode');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, 12);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'TouchPointCode');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_sourcesystem.ESTABLISHMENT);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'TerminalID');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_sourcesystem.CREATED_AT);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'InvoiceNum');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_sourcesystem.ID);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

END LOOP;

FOR rec_bi_transheader IN cur_bi_transheader

LOOP

item_elmt := xmldom.createElement( doc, 'TransactionHeader');

--xmldom.setAttribute(item_elmt, 'Code', 'hunundundint');

user_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));

item_elmt := xmldom.createElement(doc, 'TransactionTimestamp');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transheader.CLOSED);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'CurrencyCode');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transheader.PARAMETER_VALUE);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'Void');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transheader.VOIDED_BY);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement( doc, 'PersonnelList');

user_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));

item_elmt := xmldom.createElement( doc, 'Personnel');

user_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));

item_elmt := xmldom.createElement( doc, 'SourceSystem');

user_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));

item_elmt := xmldom.createElement(doc, 'PersonnelCode');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, 51543902519618);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'NetRevenueVAT');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transheader.FINAL_TOTAL);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

END LOOP;

FOR rec_bi_transheader IN cur_bi_transheader

LOOP

item_elmt := xmldom.createElement( doc, 'TransactionDetailList');

--xmldom.setAttribute(item_elmt, 'Code', 'hunundundint');

user_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));

FOR rec_bi_transdetail IN cur_bi_transdetail

LOOP

item_elmt := xmldom.createElement( doc, 'TransactionDetail');

--xmldom.setAttribute(item_elmt, 'Code', 'hunundundint');

user_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));

item_elmt := xmldom.createElement(doc, 'LineNum');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.ID);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'TransactionTypeCode');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, 'sle');

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'LineNum');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.ERVC_TYPE);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'ReturnReasonCode');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.VOIDED_REASON);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'ItemTypeCode');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, 'X');

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'CurrencyCode');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.PARAMETER_VALUE);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'ProductCode');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.PRODUCT);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'ProductUPC');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.BARCODE);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'UnitPriceNoVAT');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.PRICE_NOTAX);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'UnitPriceVAT');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.PRICE_WITHTAX);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'Quantity');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.QUANTITY);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'NetRevenueNoVAT');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.PRICE);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'NetRevenueVAT');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.PURE_SALES);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'ProductTaxCode');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.APPLIED_TAXES);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'ProductTaxAmount');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.TAX_AMOUNT);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'VATPercentageRate');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.TAX_RATE);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

item_elmt := xmldom.createElement(doc, 'Void');

item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));

item_text := xmldom.createTextNode(doc, rec_bi_transdetail.VOIDED);

item_node := xmldom.appendChild(item_node , xmldom.makeNode(item_text));

END LOOP;

END LOOP;

END LOOP;

l_xmltype := dbms_xmldom.getXmlType(doc);

dbms_xmldom.freeDocument(doc);

dbms_output.put_line(l_xmltype.getClobVal);

END;

/

_____________________________________________________________________________________________________________

OUTPUT I AM GETTING FOR ABOVE CODE IS AS BELOW

_____________________________________________________________________________________________________________

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

<ConsumerTransactionList>

<Header Action="CREATE"/>

<ConsumerTransaction/>

<SourceSystem>

<SourceTimestamp>2018-06-16T10:52:00+02:00</SourceTimestamp>

<BusinessDayDate>19-JUL-18</BusinessDayDate>

<AffiliateCode>CES</AffiliateCode>

<MarketCode>HUN</MarketCode>

<BrandCode>12</BrandCode>

<TouchPointCode>12448014</TouchPointCode>

<TerminalID>345345</TerminalID>

<InvoiceNum>12033/16587</InvoiceNum>

</SourceSystem>

<TransactionHeader>

<TransactionTimestamp>2018-06-16T10:52:00+02:00</TransactionTimestamp>

<CurrencyCode>HUF</CurrencyCode>

<Void>false</Void>

</TransactionHeader>

<PersonnelList/>

<Personnel/>

<SourceSystem>

<PersonnelCode>51543902519618</PersonnelCode>

<NetRevenueVAT>6000</NetRevenueVAT>

</SourceSystem>

<TransactionDetailList/>

<TransactionDetail>

<LineNum>53731</LineNum>

<TransactionTypeCode>sle</TransactionTypeCode>

<LineNum>ERVC</LineNum>

<ReturnReasonCode>Defect</ReturnReasonCode>

<ItemTypeCode>X</ItemTypeCode>

<CurrencyCode>HUF</CurrencyCode>

<ProductCode>S2P2040000</ProductCode>

<ProductUPC>773602397778</ProductUPC>

<UnitPriceNoVAT>4724.41</UnitPriceNoVAT>

<UnitPriceVAT>6000</UnitPriceVAT>

<Quantity>1</Quantity>

<NetRevenueNoVAT>4724.41</NetRevenueNoVAT>

<NetRevenueVAT>6000</NetRevenueVAT>

<ProductTaxCode>vat</ProductTaxCode>

<ProductTaxAmount>1275.59</ProductTaxAmount>

<VATPercentageRate>27</VATPercentageRate>

<Void>0</Void>

</TransactionDetail>

</ConsumerTransactionList>

_____________________________________________________________________________________________________________

OUTPUT I WANT IS BELOW

_____________________________________________________________________________________________________________

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

<ConsumerTransactionList>

<Header Action="CREATE">

</Header> -- THIS LINE IS NOT IN MY OUTPUT

<ConsumerTransaction>

<SourceSystem>

<SourceTimestamp>2018-06-16T10:52:00+02:00</SourceTimestamp>

<BusinessDayDate> <BusinessDayDate>

<AffiliateCode>CES</AffiliateCode>

<MarketCode>HUN</MarketCode>

<BrandCode>12</BrandCode>

<TouchPointCode>12448014</TouchPointCode>

<TerminalID> <TerminalID>

<InvoiceNum>12033/16587</InvoiceNum>

</SourceSystem>

<TransactionHeader>

<TransactionTimestamp>2018-06-16T10:52:00+02:00</TransactionTimestamp>

<CurrencyCode>HUF</CurrencyCode>

<Void>false</Void>

<PersonnelList>

<Personnel>

<SourceSystem>

<PersonnelCode>51543902519618</PersonnelCode>

</SourceSystem>

</Personnel> -- THIS LINE IS NOT IN MY OUTPUT

</PersonnelList> -- THIS LINE IS NOT IN MY OUTPUT

<NetRevenueVAT>6000.0000</NetRevenueVAT>

</TransactionHeader>

<TransactionDetailList>

<TransactionDetail>

<LineNum>53731</LineNum>

<TransactionTypeCode>sle</TransactionTypeCode>

<LineNum>3</LineNum>

<ReturnReasonCode> <ReturnReasonCode>

<ItemTypeCode> <ItemTypeCode>

<CurrencyCode>HUF</CurrencyCode>

<ProductCode>S2P2040000</ProductCode>

<ProductUPC>773602397778</ProductUPC>

<UnitPriceNoVAT>4724.4100</UnitPriceNoVAT>

<UnitPriceVAT>6000.0000</UnitPriceVAT>

<Quantity>1</Quantity>

<NetRevenueNoVAT>4724.4100</NetRevenueNoVAT>

<NetRevenueVAT>6000.0000</NetRevenueVAT>

<ProductTaxCode>vat</ProductTaxCode>

<ProductTaxAmount>1275.5900</ProductTaxAmount>

<VATPercentageRate>27.00</VATPercentageRate>

<Void>0</Void>

</TransactionDetail>

</TransactionDetailList> -- THIS LINE IS NOT IN MY OUTPUT

</ConsumerTransaction> -- THIS LINE IS NOT IN MY OUTPUT

</ConsumerTransactionList>