Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

need help with xml format

ateeqrahmanOct 18 2021

Database : 11.2.0.4
Windows 2016

I am trying to achieve the below format
<cac:Item>
<cbc:Name>RENT </cbc:Name>
<cac:ClassifiedTaxCategory>
<VLID_VAT_TYPE>E</VLID_VAT_TYPE>
<VLID_VAT_PERCENT>0</VLID_VAT_PERCENT>
</cac:ClassifiedTaxCategory>

<cbc:Name>FURNITURE/APPLIANCES RENT </cbc:Name>
<cac:ClassifiedTaxCategory>
<VLID_VAT_TYPE>S</VLID_VAT_TYPE>
<VLID_VAT_PERCENT>5</VLID_VAT_PERCENT>
</cac:ClassifiedTaxCategory>

<cbc:Name>MANAGEMENT FEES </cbc:Name>
<cac:ClassifiedTaxCategory>
<VLID_VAT_TYPE>S</VLID_VAT_TYPE>
<VLID_VAT_PERCENT>5</VLID_VAT_PERCENT>
</cac:ClassifiedTaxCategory>

</cac:Item>

I have used below query trying to acheive the desired output. Please correct me where am i wrong.

select XMLELEMENT("cac:Item", XMLAGG (XMLELEMENT("cbc:Name",d.VLID_DESCRIPTION,
XMLELEMENT("cac:ClassifiedTaxCategory",
XMLFOREST(d.VLID_VAT_TYPE as "cbc:ID",d.VLID_VAT_PERCENT as "cbc:percent"
XMLELEMENT("cac:TaxScheme",
XMLFOREST(d.TaxScheme as "cbc:ID"
)))))))as "ZATCA" FROM zatca d where invoice_number='2018/06/25/557557';
This post has been answered by Solomon Yakobson on Oct 18 2021
Jump to Answer

Comments

Solomon Yakobson
Answer

You didn't provide data sample, so I used SCOTT.EMP table but left your tag names and namespace names. I also used XMLSERIALIZE for display purpose:

SELECT  XMLSERIALIZE(
                     DOCUMENT
                     XMLELEMENT(
                                "cac:Item",
                                XMLATTRIBUTES(    
                                              'https://xxx' as "xmlns:cbc",    
                                              'https://yyy' as "xmlns:cac"    
                                             ),
                                XMLELEMENT(
                                           "cbc:Name",
                                           JOB
                                          ),
                                XMLELEMENT(
                                           "cac:ClassifiedTaxCategory",
                                           XMLAGG(
                                                  XMLFOREST(
                                                            ENAME "VLID_VAT_TYPE",
                                                            SAL "VLID_VAT_PERCENT"
                                                           )
                                                 )
                                          )
                               )
                   INDENT SIZE=2
                  )
  FROM  EMP
  GROUP BY JOB
/


XMLSERIALIZE(DOCUMENTXMLELEMENT("CAC:ITEM",XMLATTRIBUTES('HTTPS://XXX'AS"XMLNS:C
--------------------------------------------------------------------------------
<cac:Item xmlns:cbc="https://xxx" xmlns:cac="https://yyy">
  <cbc:Name>ANALYST</cbc:Name>
  <cac:ClassifiedTaxCategory>
    <VLID_VAT_TYPE>SCOTT</VLID_VAT_TYPE>
    <VLID_VAT_PERCENT>3000</VLID_VAT_PERCENT>
    <VLID_VAT_TYPE>FORD</VLID_VAT_TYPE>
    <VLID_VAT_PERCENT>3000</VLID_VAT_PERCENT>
  </cac:ClassifiedTaxCategory>
</cac:Item>


<cac:Item xmlns:cbc="https://xxx" xmlns:cac="https://yyy">
  <cbc:Name>CLERK</cbc:Name>
  <cac:ClassifiedTaxCategory>
    <VLID_VAT_TYPE>SMITH</VLID_VAT_TYPE>
    <VLID_VAT_PERCENT>800</VLID_VAT_PERCENT>
    <VLID_VAT_TYPE>MILLER</VLID_VAT_TYPE>
    <VLID_VAT_PERCENT>1300</VLID_VAT_PERCENT>
    <VLID_VAT_TYPE>JAMES</VLID_VAT_TYPE>
    <VLID_VAT_PERCENT>950</VLID_VAT_PERCENT>
    <VLID_VAT_TYPE>ADAMS</VLID_VAT_TYPE>
    <VLID_VAT_PERCENT>1100</VLID_VAT_PERCENT>
  </cac:ClassifiedTaxCategory>
</cac:Item>


<cac:Item xmlns:cbc="https://xxx" xmlns:cac="https://yyy">
  <cbc:Name>MANAGER</cbc:Name>
  <cac:ClassifiedTaxCategory>
    <VLID_VAT_TYPE>JONES</VLID_VAT_TYPE>
    <VLID_VAT_PERCENT>2975</VLID_VAT_PERCENT>
    <VLID_VAT_TYPE>CLARK</VLID_VAT_TYPE>
    <VLID_VAT_PERCENT>2450</VLID_VAT_PERCENT>
    <VLID_VAT_TYPE>BLAKE</VLID_VAT_TYPE>
    <VLID_VAT_PERCENT>2850</VLID_VAT_PERCENT>
  </cac:ClassifiedTaxCategory>
</cac:Item>


<cac:Item xmlns:cbc="https://xxx" xmlns:cac="https://yyy">
  <cbc:Name>PRESIDENT</cbc:Name>
  <cac:ClassifiedTaxCategory>
    <VLID_VAT_TYPE>KING</VLID_VAT_TYPE>
    <VLID_VAT_PERCENT>5000</VLID_VAT_PERCENT>
  </cac:ClassifiedTaxCategory>
</cac:Item>


<cac:Item xmlns:cbc="https://xxx" xmlns:cac="https://yyy">
  <cbc:Name>SALESMAN</cbc:Name>
  <cac:ClassifiedTaxCategory>
    <VLID_VAT_TYPE>ALLEN</VLID_VAT_TYPE>
    <VLID_VAT_PERCENT>1600</VLID_VAT_PERCENT>
    <VLID_VAT_TYPE>TURNER</VLID_VAT_TYPE>
    <VLID_VAT_PERCENT>1500</VLID_VAT_PERCENT>
    <VLID_VAT_TYPE>MARTIN</VLID_VAT_TYPE>
    <VLID_VAT_PERCENT>1250</VLID_VAT_PERCENT>
    <VLID_VAT_TYPE>WARD</VLID_VAT_TYPE>
    <VLID_VAT_PERCENT>1250</VLID_VAT_PERCENT>
  </cac:ClassifiedTaxCategory>
</cac:Item>

SQL>

SY.

Marked as Answer by ateeqrahman · Oct 19 2021
cormaco

Does this create your required output:

with
    zatca (invoice_number,
           vlid_description,
           vlid_vat_type,
           vlid_vat_percent,
           TaxScheme
           )
    as
        (select '2018/06/25/557557', 'RENT', 'E', 0, 'TS0' from DUAL
         union all
         select '2018/06/25/557557','FURNITURE/APPLIANCES RENT','S',5,'TS1' from DUAL
         union all
         select '2018/06/25/557557', 'MANAGEMENT FEES', 'S', 5, 'TS2' from DUAL)
select xmlelement (
           "cac:Item",
           xmlattributes('cac URN' as "xmlns:cac",'cbc URN' as "xmlns:cbc"),
           xmlagg (
               xmlelement (
                   "cbc:Name",
                   d.VLID_DESCRIPTION,
                   xmlelement (
                       "cac:ClassifiedTaxCategory",
                       xmlforest (d.VLID_VAT_TYPE as "cbc:ID",
                                  d.VLID_VAT_PERCENT as "cbc:percent"
                                  ),
                       xmlelement("cac:TaxScheme",
                            xmlforest(d.TaxScheme as "cbc:ID")
                       )
                    )
               )
            )
) as "ZATCA"
FROM zatca d
WHERE invoice_number = '2018/06/25/557557';

Output:
<cac:Item xmlns:cac="cac URN" xmlns:cbc="cbc URN">
  <cbc:Name>RENT<cac:ClassifiedTaxCategory>
      <cbc:ID>E</cbc:ID>
      <cbc:percent>0</cbc:percent>
      <cac:TaxScheme>
        <cbc:ID>TS0</cbc:ID>
      </cac:TaxScheme>
    </cac:ClassifiedTaxCategory>
  </cbc:Name>
  <cbc:Name>FURNITURE/APPLIANCES RENT<cac:ClassifiedTaxCategory>
      <cbc:ID>S</cbc:ID>
      <cbc:percent>5</cbc:percent>
      <cac:TaxScheme>
        <cbc:ID>TS1</cbc:ID>
      </cac:TaxScheme>
    </cac:ClassifiedTaxCategory>
  </cbc:Name>
  <cbc:Name>MANAGEMENT FEES<cac:ClassifiedTaxCategory>
      <cbc:ID>S</cbc:ID>
      <cbc:percent>5</cbc:percent>
      <cac:TaxScheme>
        <cbc:ID>TS2</cbc:ID>
      </cac:TaxScheme>
    </cac:ClassifiedTaxCategory>
  </cbc:Name>
</cac:Item>
1 - 2

Post Details

Added on Oct 18 2021
#general-xml, #xml
2 comments
194 views