OraFAQ Forum: JDeveloper, Java & XML

Home » Developer & Programmer » JDeveloper, Java & XML » XML FOREST (Oracle 11g, XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

XML FOREST [message #596043]

Tue, 17 September 2013 11:48

rajivn786
Messages: 161
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.

Report message to a moderator

Re: XML FOREST [message #596048 is a reply to message #596043]

Tue, 17 September 2013 12:13

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: XML FOREST [message #596049 is a reply to message #596048]

Tue, 17 September 2013 12:16

rajivn786
Messages: 161
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

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: XML FOREST [message #596054 is a reply to message #596049]

Tue, 17 September 2013 13:00

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: XML FOREST [message #596058 is a reply to message #596054]

Tue, 17 September 2013 13:22

rajivn786
Messages: 161
Registered: January 2010

Senior Member

Thanks. It works.

Report message to a moderator

Re: XML FOREST [message #596059 is a reply to message #596051]

Tue, 17 September 2013 13:30

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 15:31:53 CST 2025