1 Reply Latest reply: Jul 31, 2013 12:45 AM by odie_63 RSS

    Need to generate a standard XML even without data

    816802

      Hi All,

       

      I am using the below query to generate XML Data.

       

      SELECT XMLSERIALIZE (CONTENT DATA AS CLOB) AS DATA

                 FROM (  SELECT XMLELEMENT (

                                   "RegionData",

                                   XMLAGG (

                                      XMLFOREST (

                                         XMLCData(r.region) AS "RegionName",

                                         r.first_name || r.last_name AS "EmployeeFullName",

                                         r.ntlogin AS "EmployeeAlias",

                                         r.job_title AS "EmployeeRole",

                                         r.sap_number AS "SAPNumber",

                                         r.sales_transaction_dt AS "Day",

                                         r.postpaid_totalqty AS "PostpaidCount",

                                         r.postpaid_totaldollars AS "PostpaidAmount",

                                         r.postpaidfeature_totalqty AS "PostpaidFeatureCount",

                                         r.postpaidfeature_totaldollar AS "PostpaidFeatureAmount",

                                         r.prepaid_totalqty AS "PrepaidCount",

                                         r.prepaid_totaldollars AS "PrepaidAmount",

                                         r.prepaidfeature_totalqty AS "PrepaidFeatureCount",

                                         r.prepaidfeature_totaldollars AS "PrepaidFeatureAmount",

                                         r.accessory_totalqty AS "AccessoriesCount",

                                         r.accessory_totaldollars AS "AccessoriesAmount",

                                         r.handset_totalqty AS "HandsetsCount",

                                         r.handset_totaldollars AS "HandsetsAmount",

                                         (SELECT XMLAGG (

                                                    XMLELEMENT (

                                                       "Division",

                                                       XMLFOREST (

                                                          di.division AS "DivisonName",

                                                          di.postpaid_totalqty AS "PostpaidCount",

                                                          di.postpaid_totaldollars AS "PostpaidAmount",

                                                          di.postpaidfeature_totalqty AS "PostpaidFeatureCount",

                                                          di.postpaidfeature_totaldollar AS "PostpaidFeatureAmount",

                                                          di.prepaid_totalqty AS "PrepaidCount",

                                                          di.prepaid_totaldollars AS "PrepaidAmount",

                                                          di.prepaidfeature_totalqty AS "PrepaidFeatureCount",

                                                          di.prepaidfeature_totaldollars AS "PrepaidFeatureAmount",

                                                          di.accessory_totalqty AS "AccessoriesCount",

                                                          di.accessory_totaldollars AS "AccessoriesAmount",

                                                          di.handset_totalqty AS "HandsetsCount",

                                                          di.handset_totaldollars AS "HandsetsAmount",

                                                          (SELECT XMLAGG (

                                                                     XMLELEMENT (

                                                                        "District",

                                                                        XMLFOREST (

                                                                           dis.district AS "DistrictName",

                                                                           dis.postpaid_totalqty AS "PostpaidCount",

                                                                           dis.postpaid_totaldollars AS "PostpaidAmount",

                                                                           dis.postpaidfeature_totalqty AS "PostpaidFeatureCount",

                                                                           dis.postpaidfeature_totaldollar AS "PostpaidFeatureAmount",

                                                                           dis.prepaid_totalqty AS "PrepaidCount",

                                                                           dis.prepaid_totaldollars AS "PrepaidAmount",

                                                                           dis.prepaidfeature_totalqty AS "PrepaidFeatureCount",

                                                                           dis.prepaidfeature_totaldollars AS "PrepaidFeatureAmount",

                                                                           dis.accessory_totalqty AS "AccessoriesCount",

                                                                           dis.accessory_totaldollars AS "AccessoriesAmount",

                                                                           dis.handset_totalqty AS "HandsetsCount",

                                                                           dis.handset_totaldollars AS "HandsetsAmount",

                                                                           (SELECT XMLAGG (

                                                                                      XMLELEMENT (

                                                                                         "Store",

                                                                                         XMLFOREST (

                                                                                            mst.store_id AS "StoreNumber",

                                                                                            mst.store_name AS "StoreLocation",

         mst.postpaid_totalqty AS "PostpaidCount",

                                                                                            mst.postpaid_totaldollars AS "PostpaidAmount",

                                                                                            mst.postpaidfeature_totalqty AS "PostpaidFeatureCount",

                                                                                            mst.postpaidfeature_totaldollar AS "PostpaidFeatureAmount",

                                                                                            mst.prepaid_totalqty AS "PrepaidCount",

                                                                                            mst.prepaid_totaldollars AS "PrepaidAmount",

                                                                                            mst.prepaidfeature_totalqty AS "PrepaidFeatureCount",

                                                                                            mst.prepaidfeature_totaldollars AS "PrepaidFeatureAmount",

                                                                                            mst.accessory_totalqty AS "AccessoriesCount",

                                                                                            mst.accessory_totaldollars AS "AccessoriesAmount",

                                                                                            mst.handset_totalqty AS "HandsetsCount",

                                                                                            mst.handset_totaldollars AS "HandsetsAmount")))

                                                                              FROM stores_comm_mobility_info_vw mst

                                                                             WHERE mst.district =

                                                                                      dis.district) "StoreData")))

                                                             FROM diST_comm_mobility_info_vw dis

                                                            WHERE dis.division =

                                                                     di.division) "DistrictData")))

                                            FROM div_comm_mobility_info_vw di

                                           WHERE di.region = r.region) AS "DivisionData")))

                                   AS DATA

                           FROM reg_comm_mobility_info_vw r WHERE region=p_region;

       

      ----Ex

      <RegionData>

        <RegionName>West</RegionName>

        <EmployeeFullName>James</EmployeeFullName>

        <PostpaidCount>1000</PostpaidCount>

        <DivisionData>

        <Division>

        <DivisonName>WestDiv1</DivisonName>

        <PostpaidCount>1000</PostpaidCount>

        <DistrictData>

        <District>

        <DistrictName>WestDistrict1</DistrictName>

        <PostpaidCount>1000</PostpaidCount>

        <StoreData>

        <StoreNumber>123</StoreNumber>

        <PostpaidCount>1000</PostpaidCount>

        </StoreData>

        </District>

        </DistrictData>

        </Division>

        </DivisionData>

      </RegionData>

       

      But the problem here is if there is no data, then that particular element is not coming. My requirement is if there is no data in any of the levels then a default emplty element needs to be populated and if the query returns no rows then only empty skeleton needs to be created.

       

      Appreciate your response.

       

      Thanks,

      MK

        • 1. Re: Need to generate a standard XML even without data
          odie_63

          Hi,

          My requirement is if there is no data in any of the levels then a default emplty element needs to be populated and if the query returns no rows then only empty skeleton needs to be created.

          First part of the requirement is easy : don't use XMLForest, but XMLElement instead.

           

          Second part depends on what structure you want to return.

          For example do you just need the following or a deeper structure ?

          <RegionData/>