0

I have following xml which I want to generate using oracle.

<Component ID="600564" asmid="1" def="FactSheet" type="Document">
    <Profile>
        <Port_ID>182</Port_ID>
        <Inception_Date>31 Dec 1998</Inception_Date>
    </Profile>
    <AverageAnnualReturns>
        <TableData>
            <Item>
                <ClassID>33</ClassID>
                <ClassName>A1USD</ClassName>
                <Superscript>2</Superscript>
                <Superscript_TCE/>              
            </Item>
            <Item>
                <ClassID>45</ClassID>
                <ClassName>I1EUR</ClassName>
                <Superscript>1,2</Superscript>
                <Superscript_TCE/>              
            </Item>
        </TableData>
        <SuppressLifeColumn>Yes</SuppressLifeColumn>
        <SuppressBenchColumn10yr>No</SuppressBenchColumn10yr>
        <SuppressBenchColumn5yr>No</SuppressBenchColumn5yr>
        <SuppressBenchColumn3yr>No</SuppressBenchColumn3yr>
        <SuppressBenchColumn1yr>No</SuppressBenchColumn1yr>
    </AverageAnnualReturns>
</Component>

Query for profile as follow

SELECT PCR.PORT_ID,  
to_char(p.port_start_dt, 'dd Mon yyyy') as INCEPTION_DATE 
FROM edp_admin.PORT_CLASS_REPORT PCR 
JOIN edp_admin.PORTFOLIO p on (PCR.PORT_ID = P.PORT_ID and PCR.CLASS_ID = P.BASE_CLASS_ID) 
where PCR.PORT_ID = 182 AND PCR.REPORT_ID = 472

Query for AverageAnnualReturn is as follow

SELECT distinct MPR.PORT_ID, 
MPR.CLASS_ID, 
MPR.CLASS_NAME, 
PCR.VALUE_NUM3 as RANK, 
NVL (NC.SUPERSCRIPT_TEXT,' ') as SUPERSCRIPT_TEXT 
FROM EDP_ADMIN.SYSTEM_VALUE PCR 
LEFT JOIN EDP_ADMIN.MPR_MIL_VIEW MPR ON (PCR.VALUE_NUM1 = MPR.PORT_ID AND PCR.VALUE_NUM2 = MPR.CLASS_ID AND MPR.CLASSIF_ID = 206) 
LEFT JOIN EDP_ADMIN.PORT_CLASS_YIELD PCY ON (PCR.VALUE_NUM1 = PCY.PORT_ID AND PCR.VALUE_NUM2 = PCY.CLASS_ID AND MPR.PROFILE_DATE = PCY.PORT_CLASS_YIELD_DT AND PCY.YIELD_ID = 6) 
LEFT JOIN EDP_ADMIN.PORTFOLIO_CLASS PC ON (PCR.VALUE_NUM1 = PC.PORT_ID AND PCR.VALUE_NUM2 = PC.CLASS_ID) 
LEFT JOIN EDP_ADMIN.PORT_CLASS_MKT_PRICE PCMP ON (PCR.VALUE_NUM1 = PCMP.PORT_ID AND PCR.VALUE_NUM2 = PCMP.CLASS_ID AND PCMP.MKT_PRICE_ID = 12 AND MPR.PROFILE_DATE = PCMP.PORT_CLA_MKT_PRICE_PER_END_DT) 
LEFT JOIN (SELECT DISTINCT R1.PORT_ID, R1.CLASS_ID, R1.PORT_CLASS_EXP_RAT_VALUE, R1.PORT_CLASS_EXP_RAT_DT FROM edp_admin.Port_Class_Expense_Ratio R1 INNER JOIN (SELECT DISTINCT PORT_ID, CLASS_ID, MAX(PORT_CLASS_EXP_RAT_DT) as MONTH_END_DATE FROM EDP_ADMIN.PORT_CLASS_EXPENSE_RATIO GROUP BY PORT_ID, CLASS_ID ) R2 ON (R1.PORT_ID = R2.PORT_ID AND R1.CLASS_ID = R2.CLASS_ID AND R1.PORT_CLASS_EXP_RAT_DT = R2.MONTH_END_DATE) ) PCER ON PCR.VALUE_NUM1 = PCER.PORT_ID and PCR.VALUE_NUM2 = PCER.CLASS_ID 
LEFT JOIN EDP_ADMIN.FN_MIL_FS_CONCAT_NOTES_VIEW NC ON NC.PORT_ID = PCR.VALUE_NUM1 AND NC.CLASS_ID = PCR.VALUE_NUM2 AND NC.HYPO_TYPE = PCR.VALUE_CHAR1 
WHERE PCR.CODE = 'MIL_FS_PERF_CONFIG' AND PCR.VALUE_NUM1 IN (182) AND PCR.VALUE_CHAR1 = 'UK' ORDER BY RANK

I tried using oracle sql functions for generating xml like below

select dbms_xmlquery.getxml(sql for profile) from dual

but it generates two extra xml nodes rowset and row like below

<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <PORT_ID>182</PORT_ID>
      <INCEPTION_DATE>31 Dec 1998</INCEPTION_DATE>      
   </ROW>
</ROWSET>

Other way was

select xmlelement("Component",xmlattributes('600564' as ID,
                                             '1' as asmid,
                                             'FactSheet' as def,
                                             'Document' as type
                                             ),
                  xmlagg(
                  xmlelement("Profile")

                  )                                            

            )from dual

It generates structure as below

<Component ID="600564" ASMID="1" DEF="FactSheet" TYPE="Document"><Profile></Profile></Component>

BUt I am not geeting how do I populate data under profile node then averageannualreturns

1 Answer 1

2

You can use subselects in order to get the data for both the profile and the averageannualreturns nodes. For example:

select xmlelement("Component",
         xmlattributes('600564' as ID,
                       '1' as asmid,
                       'FactSheet' as def,
                       'Document' as type
         ),
        (
         SELECT XMLELEMENT("Profile",
                  XMLFOREST(
                    'example_port' AS "Port_ID",  
                    '30/11/2016' as "Inception_Date" 
                  )
                )
         FROM dual
        )                                 
) from dual;

I believe this is the sort of thing you would need (although I don't have your tables to test with):

select xmlelement("Component",
         xmlattributes('600564' as ID,
                       '1' as asmid,
                       'FactSheet' as def,
                       'Document' as type
         ),
        (
         SELECT XMLELEMENT("Profile",
                  XMLFOREST(
                    PCR.PORT_ID AS "Port_ID",  
                    to_char(p.port_start_dt, 'dd Mon yyyy') as "Inception_Date" 
                  )
                )
         FROM edp_admin.PORT_CLASS_REPORT PCR 
         JOIN edp_admin.PORTFOLIO p on (PCR.PORT_ID = P.PORT_ID and PCR.CLASS_ID = P.BASE_CLASS_ID) 
         where PCR.PORT_ID = 182 AND PCR.REPORT_ID = 472
        ),
        (
          SELECT XMLELEMENT("AverageAnnualReturns",
                   XMLELEMENT("TableData",
                     XMLAGG(
                       XMLELEMENT("Item",
                         XMLFOREST(
                           MPR.CLASS_ID AS "ClassID",
                           MPR.CLASS_NAME AS "ClassName",
                           NVL (NC.SUPERSCRIPT_TEXT,' ') AS "SuperScript",
                           NULL AS "SuperScript_TCE"
                         )
                       )
                     )
                   ),
                   XMLFOREST(
                     'Yes' AS "SupressLifeColumn",
                     'No' AS "SupressBenchColumn10yr",
                     'No' AS "SupressBenchColumn5yr",
                     'No' AS "SupressBenchColumn3yr",
                     'No' AS "SupressBenchColumn1yr"
                   )
                 )
          FROM EDP_ADMIN.SYSTEM_VALUE PCR 
          LEFT JOIN EDP_ADMIN.MPR_MIL_VIEW MPR ON (PCR.VALUE_NUM1 = MPR.PORT_ID AND PCR.VALUE_NUM2 = MPR.CLASS_ID AND MPR.CLASSIF_ID = 206) 
          LEFT JOIN EDP_ADMIN.PORT_CLASS_YIELD PCY ON (PCR.VALUE_NUM1 = PCY.PORT_ID AND PCR.VALUE_NUM2 = PCY.CLASS_ID AND MPR.PROFILE_DATE = PCY.PORT_CLASS_YIELD_DT AND PCY.YIELD_ID = 6) 
          LEFT JOIN EDP_ADMIN.PORTFOLIO_CLASS PC ON (PCR.VALUE_NUM1 = PC.PORT_ID AND PCR.VALUE_NUM2 = PC.CLASS_ID) 
          LEFT JOIN EDP_ADMIN.PORT_CLASS_MKT_PRICE PCMP ON (PCR.VALUE_NUM1 = PCMP.PORT_ID AND PCR.VALUE_NUM2 = PCMP.CLASS_ID AND PCMP.MKT_PRICE_ID = 12 AND MPR.PROFILE_DATE = PCMP.PORT_CLA_MKT_PRICE_PER_END_DT) 
          LEFT JOIN (SELECT DISTINCT R1.PORT_ID, R1.CLASS_ID, R1.PORT_CLASS_EXP_RAT_VALUE, R1.PORT_CLASS_EXP_RAT_DT FROM edp_admin.Port_Class_Expense_Ratio R1 INNER JOIN (SELECT DISTINCT PORT_ID, CLASS_ID, MAX(PORT_CLASS_EXP_RAT_DT) as MONTH_END_DATE FROM EDP_ADMIN.PORT_CLASS_EXPENSE_RATIO GROUP BY PORT_ID, CLASS_ID ) R2 ON (R1.PORT_ID = R2.PORT_ID AND R1.CLASS_ID = R2.CLASS_ID AND R1.PORT_CLASS_EXP_RAT_DT = R2.MONTH_END_DATE) ) PCER ON PCR.VALUE_NUM1 = PCER.PORT_ID and PCR.VALUE_NUM2 = PCER.CLASS_ID 
          LEFT JOIN EDP_ADMIN.FN_MIL_FS_CONCAT_NOTES_VIEW NC ON NC.PORT_ID = PCR.VALUE_NUM1 AND NC.CLASS_ID = PCR.VALUE_NUM2 AND NC.HYPO_TYPE = PCR.VALUE_CHAR1 
          WHERE PCR.CODE = 'MIL_FS_PERF_CONFIG' AND PCR.VALUE_NUM1 IN (182) AND PCR.VALUE_CHAR1 = 'UK' ORDER BY RANK   
        )                                   
) from dual;

Solution using appendChildXML and plsql:

DECLARE
  lComponentXML xmltype;
  lProfileXML   xmltype;
BEGIN
  --generate the component xml
  select xmlelement("Component",
           xmlattributes('600564' as ID,
                         '1' as asmid,
                         'FactSheet' as def,
                         'Document' as type
           )
         )
  INTO lComponentXML
  FROM dual;

  --generate the profile xml
  SELECT XMLELEMENT("Profile",
           XMLFOREST(
             PCR.PORT_ID AS "Port_ID",  
             to_char(p.port_start_dt, 'dd Mon yyyy') as "Inception_Date"
           )
         )
  INTO lProfileXML
  FROM edp_admin.PORT_CLASS_REPORT PCR 
  JOIN edp_admin.PORTFOLIO p on (PCR.PORT_ID = P.PORT_ID and PCR.CLASS_ID = P.BASE_CLASS_ID) 
  where PCR.PORT_ID = 182 AND PCR.REPORT_ID = 472;

  --append the profile xml to the component
  lComponentXML := lComponentXML.appendChildXML('//Component',lProfileXML);
END;
Sign up to request clarification or add additional context in comments.

7 Comments

It worked, but it doesn't work if I use distinct or order by in sql. In sql for AverageAnnulReturns there distinct and order by is used which fails on execution
One thing is also top of my mind how would I apply business conditions to each sql before generating it's xml equivalent structure.
If you're using plsql, I've added an additional solution which would allow you to apply business conditions around the XML, before appending it to the main XML body. I'd probably just recommend building up each chunk of XML individually, and gradually appending it. That way you can get each small chunk working using the order by and distinct clauses, and gradually add them in
Nope, even when I am executing those sub select individually then also distinct does not support, throwing error as missing expression.
Unfortunately without your table structure, any example data, or the query you're trying to run to generate the XML its going to be pretty hard for me to help. The only thing that springs to mind is to use a with ... as statement. i.e. with annual_returns as then your existing query using the distinct and order by clause. You can then query against annual_returns in order to generate the XML.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.