Home » Developer & Programmer » JDeveloper, Java & XML » XML FOREST (Oracle 11g, XP)
XML FOREST [message #596043] Tue, 17 September 2013 11:48 Go to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
Hi,

I need the code for the following. I did try...but getting additional tags.

  Create table patient (pat_mrn varchar2(100)) ;
  Create table encount (pat_mrn varchar2(100), encounter_id varchar2(1000));
  Create table oper    (encounter_id varchar2(1000), comp_name varchar2(1000));

 Insert into patient values ('63280');
 Insert into encount values ('63280', '42');
 Insert  into oper values  (42, 'sugar');
 Insert  into oper  values (42, 'sbp');
 Insert  into oper  values (42, 'dbp');

CREATE OR REPLACE TYPE COMPONENT AS OBJECT (    "ID" VARCHAR2(1000));

CREATE OR REPLACE TYPE component_list_t AS TABLE OF COMPONENT;

CREATE OR REPLACE TYPE cm_results_o_t AS OBJECT (RES_LIST component_list_t);



  O/p required :
     <Patient>
       <pat_mrn> 63280 </pat_mrn>
      <Results>
          <Component>
            <ID>sugar</ID>
          </Component>
          <Component>
            <ID>sbp</ID>
          </Component>
          <Component>
            <ID>dbp</ID>
          </Component>
         
         </Results> 
    </patient>


Code I wrote :


Select P.PAT_MRN,
       XMLELEMENT("Patient",
                  (XMLELEMENT("pat_mrn", P.pat_mrn)),
                  (XMLELEMENT("Results",
                              XMLForest(cm_results_o_t(CAST(MULTISET
                                                            (SELECT O.COMP_NAME AS "ID"
                                                               FROM oper O
                                                              WHERE O.ENCOUNTER_ID =
                                                                    E.ENCOUNTER_ID) AS
                                                            component_list_t)) AS
                                        "Results")))) AS Orderxml
  FROM PATIENT P
  JOIN ENCOUNT E
    ON P.PAT_MRN = E.PAT_MRN
   AND P.PAT_MRN = '63280'
   AND E.ENCOUNTER_ID = 42



So, we can clearly see there are lot of additional tags .. o/p i am getting

  
  <Patient>
<pat_mrn>63280</pat_mrn>
<Results>
<Results>
<RES_LIST>
<COMPONENT>
<ID>sugar</ID>
</COMPONENT>
<COMPONENT>
<ID>sbp</ID>
</COMPONENT>
<COMPONENT>
<ID>dbp</ID>
</COMPONENT>
</RES_LIST>
</Results>
</Results>
</Patient>


I am new to xml..So, any help is appreciated.

Thanks.
Re: XML FOREST [message #596048 is a reply to message #596043] Tue, 17 September 2013 12:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't the third xmlelement:
SQL> Select P.PAT_MRN,
  2         XMLELEMENT("Patient",
  3                    (XMLELEMENT("pat_mrn", P.pat_mrn)),
  4                    (XMLForest(cm_results_o_t(CAST(MULTISET
  5                                                              (SELECT O.COMP_NAME AS "ID"
  6                                                                 FROM oper O
  7                                                                WHERE O.ENCOUNTER_ID =
  8                                                                      E.ENCOUNTER_ID) AS
  9                                                              component_list_t)) AS
 10                                          "Results"))) AS Orderxml
 11    FROM PATIENT P
 12    JOIN ENCOUNT E
 13      ON P.PAT_MRN = E.PAT_MRN
 14     AND P.PAT_MRN = '63280'
 15     AND E.ENCOUNTER_ID = 42
 16  /
PAT_MRN
----------------------------------------------------------------------------------------------------
ORDERXML
------------------------------------------------------------------------------------------------------------------------
63280
<Patient><pat_mrn>63280</pat_mrn><Results><RES_LIST><COMPONENT><ID>sugar</ID></COMPONENT><COMPONENT><ID>sbp</ID></COMPON
ENT><COMPONENT><ID>dbp</ID></COMPONENT></RES_LIST></Results></Patient>

Manually formatting the result:
<Patient>
  <pat_mrn>63280</pat_mrn>
  <Results>
    <RES_LIST>
      <COMPONENT>
         <ID>sugar</ID>
      </COMPONENT>
      <COMPONENT>
         <ID>sbp</ID>
      </COMPONENT>
      <COMPONENT>
         <ID>dbp</ID>
      </COMPONENT>
    </RES_LIST>
  </Results>
</Patient>

Regards
Michel
Re: XML FOREST [message #596049 is a reply to message #596048] Tue, 17 September 2013 12:16 Go to previous messageGo to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
Mike,

I don't want RES_LIST tag at all. So, how can the code be modified to remove it.

O.p should be :

<Patient>
  <pat_mrn>63280</pat_mrn>
  <Results>
          <COMPONENT>
         <ID>sugar</ID>
      </COMPONENT>
      <COMPONENT>
         <ID>sbp</ID>
      </COMPONENT>
      <COMPONENT>
         <ID>dbp</ID>
      </COMPONENT>
  </Results>
</Patient>

Thanks.

[Updated on: Tue, 17 September 2013 12:17]

Report message to a moderator

Re: XML FOREST [message #596051 is a reply to message #596049] Tue, 17 September 2013 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah! I thought this was the double "Results" that was the problem; better to say the result you want with words. Smile
RES_LIST is part of the definition of your object.
I don't know if it is possible to remove it without complex code.

Regards
Michel
Re: XML FOREST [message #596054 is a reply to message #596049] Tue, 17 September 2013 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think I find it, you don't need this intermediate cm_results_o_t object type:
SQL> Select P.PAT_MRN,
  2         XMLELEMENT("Patient",
  3                    XMLELEMENT("pat_mrn", P.pat_mrn),
  4                    XMLForest(CAST(MULTISET
  5                                    (SELECT O.COMP_NAME AS "ID"
  6                                     FROM oper O
  7                                     WHERE O.ENCOUNTER_ID = E.ENCOUNTER_ID) 
  8                                 AS component_list_t) 
  9                               AS "Results")) AS Orderxml
 10    FROM PATIENT P
 11    JOIN ENCOUNT E
 12      ON P.PAT_MRN = E.PAT_MRN
 13     AND P.PAT_MRN = '63280'
 14     AND E.ENCOUNTER_ID = 42
 15  /
PAT_MRN
----------------------------------------------------------------------------------------------------
ORDERXML
------------------------------------------------------------------------------------------------------------------------
63280
<Patient><pat_mrn>63280</pat_mrn><Results><COMPONENT><ID>sugar</ID></COMPONENT><COMPONENT><ID>sbp</ID></COMPONENT><COMPO
NENT><ID>dbp</ID></COMPONENT></Results></Patient>

<Patient>
  <pat_mrn>63280</pat_mrn>
  <Results>
    <COMPONENT>
      <ID>sugar</ID>
    </COMPONENT>
    <COMPONENT>
       <ID>sbp</ID>
    </COMPONENT>
    <COMPONENT>
      <ID>dbp</ID>
    </COMPONENT>
  </Results>
</Patient>

Regards
Michel
Re: XML FOREST [message #596058 is a reply to message #596054] Tue, 17 September 2013 13:22 Go to previous messageGo to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
Thanks. It works.
Re: XML FOREST [message #596059 is a reply to message #596051] Tue, 17 September 2013 13:30 Go to previous message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
Michel Cadot wrote on Tue, 17 September 2013 13:51
I don't know if it is possible to remove it without complex code.



SELECT  P.PAT_MRN,
        XMLSERIALIZE(
                     DOCUMENT
                     XMLELEMENT(
                                "Patient",
                                XMLELEMENT(
                                           "pat_mrn",
                                            P.PAT_MRN
                                          ),
                                XMLELEMENT(
                                           "Results",
                                           XMLAGG(
                                                  XMLELEMENT(
                                                             "COMPONENT",
                                                             XMLELEMENT(
                                                                        "ID",
                                                                        COMP_NAME
                                                                       )
                                                            )
                                                     )
                                          )
                               )
                      AS CLOB
                      INDENT SIZE=2
                    ) Orderxml
  FROM  PATIENT P,
        ENCOUNT E,
        OPER O
  WHERE E.PAT_MRN = P.PAT_MRN
    AND O.ENCOUNTER_ID = E.ENCOUNTER_ID
  GROUP BY P.PAT_MRN
/

PAT_MRN    ORDERXML
---------- ---------------------------
63280      <Patient>
             <pat_mrn>63280</pat_mrn>
             <Results>
               <COMPONENT>
                 <ID>dbp</ID>
               </COMPONENT>
               <COMPONENT>
                 <ID>sugar</ID>
               </COMPONENT>
               <COMPONENT>
                 <ID>sbp</ID>
               </COMPONENT>
             </Results>
           </Patient>


SY.



[Edit MC: remove repeating headers in result]

[Updated on: Wed, 18 September 2013 00:29] by Moderator

Report message to a moderator

Previous Topic: error when i run any page with view object
Next Topic: Return a ResultSet as a ref cursor
Goto Forum:
  


Current Time: Wed Oct 22 00:14:55 CDT 2014

Total time taken to generate the page: 0.22153 seconds