How to convert data in table to XML in Oracle SQL? And using XMLTYPE and extractvalue

 Use the below SQL to convert data in table to XML (XMLTYPE)

DECLARE
l_xmltype XMLTYPE;
BEGIN
SELECT XMLELEMENT("REQUEST",
XMLELEMENT("REQUEST_ID",req.REQUEST_ID),
XMLELEMENT("ORG_ID",req.ORG_ID),
XMLELEMENT("REQUEST_NUMBER",req.REQUEST_NUMBER),
XMLELEMENT("LINES_COUNT",(   SELECT count(*) from xxaas_request_lines req_line 
                            where req.request_id=req_line.request_id 
                        )
),
XMLELEMENT("LINES", 
( 
SELECT   
   XMLAGG(
     XMLELEMENT("LINE",
       /* another way using XMLFOREST
           XMLFOREST(
             req_line.request_id AS "REQUEST_ID",
             req_line.request_line_id AS "REQUEST_LINE_ID"

           )
       */
       XMLELEMENT("REQUEST_LINE_ID",req_line.REQUEST_LINE_ID),
       XMLELEMENT("REQUEST_ID",req_line.REQUEST_ID),
       XMLELEMENT("LINE_DESC",req_line.LINE_DESC),
       XMLELEMENT("LINE_AMOUNT",req_line.LINE_AMOUNT),
       XMLELEMENT("DISTRIBUTIONS_COUNT",(   SELECT count(*) 
       from xxaas_request_budget_lines dist_line 
       where req_line.request_line_id = dist_line. request_line_id 
       and req_line.request_id=dist_line.request_id
       )
       ),
       XMLELEMENT("DISTRIBUTIONS",
            (
            SELECT
            XMLAGG(
                XMLELEMENT("DISTRIBUTION",
                XMLELEMENT("DISTRIBUTION_LINE_ID",dist_line.BUDGET_LINE_ID),
                XMLELEMENT("REQUEST_ID",dist_line.REQUEST_ID),
                XMLELEMENT("REQUEST_LINE_ID",dist_line.REQUEST_LINE_ID),
                XMLELEMENT("DISTRIBUTION_AMOUNT",dist_line.FUND_AMOUNT)
                )
            )
            FROM    xxaas_request_budget_lines dist_line
            WHERE   req_line.request_line_id = dist_line. request_line_id 
            and req_line.request_id=dist_line.request_id
            )
            
       )
     )
   ) 
FROM     xxaas_request_lines req_line
WHERE    req.request_id=req_line.request_id
)
)
)
INTO   l_xmltype
FROM   
xxaas_requests req 
where req.request_id=1;  --to get single row

--XML TEST table containing one column of type "XMLTYPE"
INSERT INTO xml_test VALUES (l_xmltype);  
COMMIT;
END;

 

Below is the sample XML generated

<REQUEST>
  <REQUEST_ID>1</REQUEST_ID>
  <ORG_ID>100</ORG_ID>
  <REQUEST_NUMBER>Request1</REQUEST_NUMBER>
  <LINES_COUNT>2</LINES_COUNT>
  <LINES>
    <LINE>
      <REQUEST_LINE_ID>101</REQUEST_LINE_ID>
      <REQUEST_ID>1</REQUEST_ID>
      <LINE_DESC>Line 1</LINE_DESC>
      <LINE_AMOUNT>5000</LINE_AMOUNT>
      <DISTRIBUTIONS_COUNT>2</DISTRIBUTIONS_COUNT>
      <DISTRIBUTIONS>
        <DISTRIBUTION>
          <DISTRIBUTION_LINE_ID>1001</DISTRIBUTION_LINE_ID>
          <REQUEST_ID>1</REQUEST_ID>
          <REQUEST_LINE_ID>101</REQUEST_LINE_ID>
          <DISTRIBUTION_AMOUNT>2500</DISTRIBUTION_AMOUNT>
        </DISTRIBUTION>
        <DISTRIBUTION>
          <DISTRIBUTION_LINE_ID>1002</DISTRIBUTION_LINE_ID>
          <REQUEST_ID>1</REQUEST_ID>
          <REQUEST_LINE_ID>101</REQUEST_LINE_ID>
          <DISTRIBUTION_AMOUNT>2500</DISTRIBUTION_AMOUNT>
        </DISTRIBUTION>
      </DISTRIBUTIONS>
    </LINE>
    <LINE>
      <REQUEST_LINE_ID>102</REQUEST_LINE_ID>
      <REQUEST_ID>1</REQUEST_ID>
      <LINE_DESC>Line 2</LINE_DESC>
      <LINE_AMOUNT>10000</LINE_AMOUNT>
      <DISTRIBUTIONS_COUNT>1</DISTRIBUTIONS_COUNT>
      <DISTRIBUTIONS>
        <DISTRIBUTION>
          <DISTRIBUTION_LINE_ID>1003</DISTRIBUTION_LINE_ID>
          <REQUEST_ID>1</REQUEST_ID>
          <REQUEST_LINE_ID>102</REQUEST_LINE_ID>
          <DISTRIBUTION_AMOUNT>10000</DISTRIBUTION_AMOUNT>
        </DISTRIBUTION>
      </DISTRIBUTIONS>
    </LINE>
  </LINES>
</REQUEST>

------------------------------------------------------------------------------------------------------

SELECT

    a.country_code,

    a.country_name

FROM

    country a

WHERE

    ROWNUM = 1;

--Fetch XMLTYPE

SELECT 

    XMLELEMENT("COUNTRIES",

        XMLELEMENT("COUNTRY_CODE",a.COUNTRY_CODE),

        XMLELEMENT("COUNTRY_NAME",a.COUNTRY_NAME)

    ) xml_data

FROM     country a

WHERE    rownum=1;

--Insert into CLOB column

insert into test_clob 

SELECT 

    XMLELEMENT("COUNTRIES",

        XMLELEMENT("COUNTRY_CODE",a.COUNTRY_CODE),

        XMLELEMENT("COUNTRY_NAME",a.COUNTRY_NAME)

    ).getClobVal() xml_data

FROM     country a

WHERE    rownum=1;

--convert CLOB to XMLTYPE

SELECT xmltype(xml_data) from test_clob;

--Fetch XML node value by converting CLOB to XMLTYPE 

--and then using extractvalue

SELECT extractvalue(xmltype(xml_data),'/COUNTRIES/COUNTRY_NAME') country_name

from test_clob;