2 Replies Latest reply on Jun 6, 2011 10:20 AM by N@*841964*

    How to generate blank xml tag when encounter null elements in it

    N@*841964*
      Hi Gurus,

      Iam facing one issue in generating the blank tag, here is my file which I am generating right now
      <HEADER>
          <MINISTRY_CODE>RPO</MINISTRY_CODE>
          <DEPARTMENT_CODE>000</DEPARTMENT_CODE>
          <ORDER_CODE>RPO000EPO11001002</ORDER_CODE>
          <EXTERNAL_SYSTEM_CODE>E</EXTERNAL_SYSTEM_CODE>
          <AMENDMENT_NUMBER>0</AMENDMENT_NUMBER>
          <VARIATION_NUMBER>0</VARIATION_NUMBER>
          <EXCEPTIONS>
            <EXCEPTION>
              <EXCEPTION_CODE>PO016</EXCEPTION_CODE>
              <EXCEPTION_LONG_DESC>PO cannot be created/amended successfully</EXCEPTION_LONG_DESC>
            </EXCEPTION>
          </EXCEPTIONS>
        </HEADER>
        <ITEMS>
          <ITEM>
            <LINE_NUMBER>1</LINE_NUMBER>
            <EXCEPTIONS>
              <EXCEPTION>
                <EXCEPTION_CODE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
                <EXCEPTION_LONG_DESC xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
              </EXCEPTION>
            </EXCEPTIONS>
            <LOCATIONS>
              <LOCATION>
                <LINE_NUMBER>1</LINE_NUMBER>
                <EXCEPTIONS>
                  <EXCEPTION>
                    <EXCEPTION_CODE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
                    <EXCEPTION_LONG_DESC xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
                  </EXCEPTION>
                </EXCEPTIONS>
                <DISTRIBUTIONS>
                  <DISTRIBUTION>
                    <LINE_NUMBER>1</LINE_NUMBER>
                    <EXCEPTIONS>
                      <EXCEPTION>
                        <EXCEPTION_CODE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
                        <EXCEPTION_LONG_DESC xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
                      </EXCEPTION>
                    </EXCEPTIONS>
                  </DISTRIBUTION>
                </DISTRIBUTIONS>
              </LOCATION>
            </LOCATIONS>
          </ITEM>
        </ITEMS>
      </GEBIZ_ORDER_ACK>
      current I am generating EXCEPTION tag even if it is null like
      <EXCEPTIONS>
      <EXCEPTION>
      <EXCEPTION_CODE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
      <EXCEPTION_LONG_DESC xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
      </EXCEPTION>
      </EXCEPTIONS>
      but my requirement is not generate them when exception code is null and only generate like
      <EXCEPTIONS/>

      this is sql i am using to generate the above said code let me know how can i control that
                         SELECT xmlserialize(document
                                    xmlelement("GEBIZ_ORDER_ACK"
                                    , xmlagg(
                                        xmlconcat(hdr, dtls)
                                      )
                                    )
                                    as clob indent size = 2
                                  )
                         --  INTO lv_out
                           FROM (
                                 SELECT xmlelement("HEADER"
                                             , xmlelement("MINISTRY_CODE", poh.ministry_code)
                                             , xmlelement("EXCEPTIONS"
                                                   , xmlelement("EXCEPTION"
                                                               , xmlelement("EXCEPTION_CODE"   --,NVL(poh.exception_code,'NULL')
                                                               , xmlattributes(
                                                                 NVL2(poh.exception_code,null,'http://www.w3.org/2001/XMLSchema-instance') as "xmlns:xsi"
                                                               , NVL2(poh.exception_code,null,'true') as "xsi:nil"
                                                                 )
                                                               ,poh.exception_code)
                                                               , xmlelement("EXCEPTION_LONG_DESC"
                                                               , xmlattributes(
                                                                 NVL2(poh.exception_long_desc,null,'http://www.w3.org/2001/XMLSchema-instance') as "xmlns:xsi"
                                                               , NVL2(poh.exception_long_desc,null,'true') as "xsi:nil"
                                                                 )
                                                               ,poh.exception_long_desc)
                                                               )
                                                         )
                                             ) hdr
                                       , xmlelement("ITEMS"
                                       , xmlagg(
                                                xmlelement("ITEM"
                                                 ,xmlelement("LINE_NUMBER", v1.line_no)
                                                 ,xmlelement("EXCEPTIONS"
                                                          , xmlelement("EXCEPTION"
                                                                      , xmlelement("EXCEPTION_CODE"    --,NVL(v1.exception_code,'NULL')
                                                                      , xmlattributes(
                                                                        NVL2(v1.exception_code,null,'http://www.w3.org/2001/XMLSchema-instance') as "xmlns:xsi"
                                                                      , NVL2(v1.exception_code,null,'true') as "xsi:nil"
                                                                        )
                                                                      , v1.exception_code)
                                                                      , xmlelement("EXCEPTION_LONG_DESC"
                                                                      , xmlattributes(
                                                                        NVL2(v1.exception_long_desc,null,'http://www.w3.org/2001/XMLSchema-instance') as "xmlns:xsi"
                                                                      , NVL2(v1.exception_long_desc,null,'true') as "xsi:nil"
                                                                        )
                                                                      ,v1.exception_long_desc)
                                                                      )
                                                            )
                                                 ,xmlelement("LOCATIONS"
                                                 ,( SELECT xmlagg(
                                                            xmlelement("LOCATION"
                                                          , xmlelement("LINE_NUMBER",v2.location_line_no)
                                                          , xmlelement("EXCEPTIONS"
                                                          , xmlelement("EXCEPTION"
                                                          , xmlelement("EXCEPTION_CODE"   --,NVL(v2.exception_code,'NULL')
                                                          , xmlattributes(
                                                            NVL2(v2.exception_code,null,'http://www.w3.org/2001/XMLSchema-instance') as "xmlns:xsi"
                                                          , NVL2(v2.exception_code,null,'true') as "xsi:nil"
                                                            )
                                                          , v2.exception_code)
                                                          , xmlelement("EXCEPTION_LONG_DESC"
                                                          , xmlattributes(
                                                            NVL2(v2.exception_long_desc,null,'http://www.w3.org/2001/XMLSchema-instance') as "xmlns:xsi"
                                                          , NVL2(v2.exception_long_desc,null,'true') as "xsi:nil"
                                                            )
                                                          ,v2.exception_long_desc)
                                                                      )
                                                            )
                                                 ,xmlelement("DISTRIBUTIONS"
                                                 ,(SELECT xmlagg
                                                   (       xmlelement("DISTRIBUTION"
                                                           ,xmlelement("LINE_NUMBER", v3.distribution_line_no)
                                                           ,xmlelement("EXCEPTIONS"
                                                           , xmlelement("EXCEPTION"
                                                           , xmlelement("EXCEPTION_CODE"--,NVL(v3.exception_code,'NULL')
                                                           , xmlattributes(
                                                             NVL2(v3.exception_code,null,'http://www.w3.org/2001/XMLSchema-instance') as "xmlns:xsi"
                                                           , NVL2(v3.exception_code,null,'true') as "xsi:nil"
                                                            )
                                                           ,v3.exception_code)
                                                           , xmlelement("EXCEPTION_LONG_DESC"
                                                           , xmlattributes(
                                                             NVL2(v3.exception_long_desc,null,'http://www.w3.org/2001/XMLSchema-instance') as "xmlns:xsi"
                                                           , NVL2(v3.exception_long_desc,null,'true') as "xsi:nil"
                                                            )
                                                          ,v3.exception_long_desc)
                                                                      )
                                                              )
                                                            ) ORDER BY v3.distribution_line_no
                                                         ) FROM xgbz_fin_stage_ack_pot_v@dev_1157.RP.EDU.SG v3
                                                          WHERE v2.po_cont_code = v3.po_cont_code
                                                            AND v2.line_no = v3.line_no
                                                            AND v2.location_line_no = v3.location_line_no
                                                            AND v2.fs_timestamp = v3.fs_timestamp
                                                            AND v3.tx_timestamp IS  NULL
                                                       )
                                                     )
                                                   ) ORDER BY v2.location_line_no
                                                ) FROM xgbz_fin_stage_ack_pol_v@dev_1157.RP.EDU.SG v2
                                                 WHERE v2.line_no = v1.line_no
                                                   AND v2.po_cont_code = v1.po_cont_code
                                                   AND v2.fs_timestamp = v1.fs_timestamp
                                                   AND v2.TX_TIMESTAMP IS NULL
                                   )
                                 )
                               ) ORDER BY v1.line_no
                             )
                           ) dtls
                      FROM xgbz_fin_stage_ack_poh_v@dev_1157.rp.edu.sg poh,
                           xgbz_fin_stage_ack_pod_v@dev_1157.rp.edu.sg v1
                     WHERE v1.po_cont_code = poh.po_cont_code
                       AND v1.fs_timestamp = poh.fs_timestamp
                       AND v1.tx_timestamp IS NULL
                       AND poh.tx_timestamp IS NULL
                       AND poh.po_cont_code = rec_po_ack.po_cont_code
                     GROUP BY poh.po_cont_code,poh.ext_system_code,poh.amendment_no,poh.variation_no,poh.exception_code,poh.exception_long_desc,
                              poh.ministry_code, poh.dept_code)
      This is urgent please help in this regard.

      thanks in advance.

      Regards
      Nagendra