Home » Developer & Programmer » JDeveloper, Java & XML » Help adding more fields in XML? (Oracle, 11g, Linux)
Help adding more fields in XML? [message #638708] Thu, 18 June 2015 21:02 Go to next message
laknar
Messages: 22
Registered: February 2009
Junior Member
I have multiple tables. My requirement is to generate an XML file for each Relationship manager hierarchically. So far, i'am able to generate the below XML file(shown output below). However i have data from 2 more tables which need to be joined and append some of fields like acc_type,acc_code and txn_code in the respective record in XML. But i couldn't add these fields. Can anyone suggest a solution to add these fields?. Also is there a way to generate the XML file for each relationship manager dynamically.(Note i have used a where clause to pull the records for rltp_mngr=10). Looking to group the rltp_mngr and generate an XML file automatically instead of one by one.

drop table rltp_mngr;
drop table product;
drop table cust;
drop table account;
drop table transaction;
drop table acctype;
drop table txntype;

create table rltp_mngr(rltp_id number,rltp_name varchar2(50));
Insert into rltp_mngr values(10, 'Phil');
Insert into rltp_mngr values(20, 'Jack');

create table product(rltp_id number,prod_id number,prod_name varchar2(50));
Insert into product values(10,1, 'Personal');
Insert into product values(20,1, 'Business');

create table cust(rltp_id number,prod_id number,cust_id number,cust_name varchar2(50));
insert into cust values(10,1,2,'Fixed');
insert into cust values(20,1,2,'Fixed');

Create table account(rltp_id number,prod_id number,cust_id number,acc_id number,acc_name varchar2(50),acc_balance number(18,2));
insert into account values(10,1,2,3,'Savings',3000);
insert into account values(10,1,2,7,'Savings',3000);
insert into account values(20,1,2,3,'Savings',3000);

create table transaction(rltp_id number,prod_id number,cust_id number,acc_id number,txn_id number,txn_amt number(18,2));
insert into transaction values(10,1,2,3,4,500);
insert into transaction values(10,1,2,3,5,500);
insert into transaction values(10,1,2,3,6,500);
insert into transaction values(10,1,2,7,8,500);
insert into transaction values(20,1,2,3,4,500);

create table acctype(rltp_id number,prod_id number,cust_id number,acc_id number,acc_type varchar2(5),acc_code varchar2(10));
insert into acctype values(10,1,2,3,'X','ZZ');
insert into acctype values(10,1,2,3,'X','YY');
insert into acctype values(10,1,2,3,'X','AA');
insert into acctype values(10,1,2,3,'X','BB');

create table txntype(rltp_id number,prod_id number,cust_id number,acc_id number,txn_id number,txn_code varchar2(10));
insert into txntype values(10,1,2,3,4,'11');
insert into txntype values(10,1,2,3,4,'12');
insert into txntype values(10,1,2,7,8,'11');
insert into txntype values(10,1,2,7,8,'12');
insert into txntype values(10,1,2,7,8,'13');


select 
               XMLElement("transactiondetails",
                         XMLElement("rltp_id", rltp_id),
                         XMLElement("rltp_name", rltp_name),
                            (SELECT XMLAGG(XMLElement("product", 
                                          XMLElement("prod_id", p.prod_id),
                                          XMLElement("prod_name", p.prod_name),
                                (SELECT XMLAGG(XMLElement("customer",
                                              XMLElement("cust_id", cust_id),
                                              XMLElement("cust_name", cust_name),
                                         (SELECT XMLAGG(XMLElement("account",
                                                    XMLElement("acc_id", acc_id),
                                                    XMLElement("acc_name", acc_name),
                                                    XMLElement("acc_balance", acc_balance),
                                                (SELECT XMLAGG(XMLElement("transaction", 
                                                       XMLElement("txntrack",
                                                       XMLElement("txn_id", t.txn_id),
                                                       XMLElement("txn_amt", t.txn_amt))))
                                                FROM transaction t
                                                WHERE t.rltp_id                 =a.rltp_id
                                                AND t.prod_id      =a.prod_id
                                                AND t.cust_id        =a.cust_id
                                                AND t.acc_id=a.acc_id)))
                                    FROM account a
                                    WHERE c.rltp_id=a.rltp_id
                                    AND c.prod_id=a.prod_id
                                    AND c.cust_id=a.cust_id)))
                                FROM cust c
                                WHERE p.rltp_id = c.rltp_id
                                AND p.prod_id = c.prod_id)))
                             FROM product p
                             WHERE p.rltp_id = r.rltp_id )          
                            ) AS xml
FROM
  rltp_mngr r
  WHERE rltp_id='10';



Output:-

<transactiondetails>
  <rltp_id>10</rltp_id>
  <rltp_name>Phil</rltp_name>
  <product>
    <prod_id>1</prod_id>
    <prod_name>Personal</prod_name>
    <customer>
      <cust_id>2</cust_id>
      <cust_name>Fixed</cust_name>
      <account>
        <acc_id>3</acc_id>
        <acc_name>Savings</acc_name>
        <acc_balance>3000</acc_balance>
        <transaction>
          <txntrack>
            <txn_id>4</txn_id>
            <txn_amt>500</txn_amt>
         </txntrack>
        </transaction>
        <transaction>
          <txntrack>
            <txn_id>5</txn_id>
            <txn_amt>500</txn_amt>
          </txntrack>
        </transaction>
        <transaction>
          <txntrack>
            <txn_id>6</txn_id>
            <txn_amt>500</txn_amt>
          </txntrack>
        </transaction>
      </account>
      <account>
        <acc_id>7</acc_id>
        <acc_name>Savings</acc_name>
        <acc_balance>3000</acc_balance>
        <transaction>
          <txntrack>
            <txn_id>8</txn_id>
            <txn_amt>500</txn_amt>
	  </txntrack>
        </transaction>
      </account>
    </customer>
  </product>
</transactiondetails>


Expected Output:-

 <transactiondetails>
  <rltp_id>10</rltp_id>
  <rltp_name>Phil</rltp_name>
  <product>
    <prod_id>1</prod_id>
    <prod_name>Personal</prod_name>
    <customer>
      <cust_id>2</cust_id>
      <cust_name>Fixed</cust_name>
      <account>
        <acc_id>3</acc_id>
        <acc_name>Savings</acc_name>
        <acc_balance>3000</acc_balance>
		<acc_type>X</acc_type>
		<acc_code>ZZ</acc_code>
		<acc_code>YY</acc_code>
		<acc_code>AA</acc_code>
		<acc_code>BB</acc_code>
        <transaction>
          <txntrack>
            <txn_id>4</txn_id>
            <txn_amt>500</txn_amt>
			<txn_code>11</txn_code>
			<txn_code>12</txn_code>
		 </txntrack>
        </transaction>
        <transaction>
          <txntrack>
            <txn_id>5</txn_id>
            <txn_amt>500</txn_amt>
			<txn_code/>
          </txntrack>
        </transaction>
        <transaction>
          <txntrack>
            <txn_id>6</txn_id>
            <txn_amt>500</txn_amt>
			<txn_code/>
          </txntrack>
        </transaction>
      </account>
      <account>
        <acc_id>7</acc_id>
        <acc_name>Savings</acc_name>
        <acc_balance>3000</acc_balance>
		<acc_type/>
		<acc_code/>
        <transaction>
          <txntrack>
            <txn_id>8</txn_id>
            <txn_amt>500</txn_amt>
			<txn_code>11</txn_code>
			<txn_code>12</txn_code>
			<txn_code>13</txn_code>
          </txntrack>
        </transaction>
      </account>
    </customer>
  </product>
</transactiondetails>
Re: Help adding more fields in XML? [message #638710 is a reply to message #638708] Fri, 19 June 2015 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65202
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I could help you but as you didn't feedback and thank in your previous topics I will skip your question.

Re: Help adding more fields in XML? [message #638749 is a reply to message #638710] Fri, 19 June 2015 08:23 Go to previous message
laknar
Messages: 22
Registered: February 2009
Junior Member
My apology. i have forget to thank in the previous posts. Thanks for you quick response. It would be great if you provide some idea to achieve this logic.
Previous Topic: Please help with XML parsing
Next Topic: What are the options for getting data in XML format using SQL queries?
Goto Forum:
  


Current Time: Wed Nov 22 21:44:48 CST 2017

Total time taken to generate the page: 0.09921 seconds