OraFAQ Forum: JDeveloper, Java & XML

Home » Developer & Programmer » JDeveloper, Java & XML » Generate XML Extract (11g, 11.2.0.3.0, aix 7.1)

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

Generate XML Extract [message #645509]

Mon, 07 December 2015 14:14

wtolentino
Messages: 398
Registered: March 2005

Senior Member

i am new to XML and i was attempting to use the xmlElement function in SQL to produced this output:

<?xml version="1.0" encoding="utf-8"?>
<Projects>
  <Project action="modify">
    <ProgramCode>M0E1</ProgramCode>
    <RecipientId>36</RecipientId>
    <FederalProjectNumber>0229473</FederalProjectNumber>
    <Details>
      <Detail action="modify">
        <LineNumber>30</LineNumber>
	<ProgramCode>M0E1</ProgramCode>
	<ImprovementType>47</ImprovementType>
	<ACFunds>15685747</ACFunds>
	<FederalFunds>0</FederalFunds>
	<ACConvAmount>791,288.00</ACConvAmount>
      </Detail>
    </Details>
  </Project>
  <Project action="modify">
    <ProgramCode>M0E1</ProgramCode>
    <RecipientId>36</RecipientId>
    <FederalProjectNumber>0229473</FederalProjectNumber>
    <Details>
      <Detail action="modify">
        <LineNumber>31</LineNumber>
	<ProgramCode>M0E1</ProgramCode>
	<ImprovementType>17</ImprovementType>
	<ACFunds>1,340,213.00</ACFunds>
	<FederalFunds>0</FederalFunds>
	<ACConvAmount>791,288.00</ACConvAmount>
      </Detail>
    </Details>
  </Project>
</Projects>

so far this was what i have:

select xmlElement("Projects",
                  xmlElement("Projects",
                             xmlAttributes(aci.proj_action as "action"),           
                             xmlElement("ProgramCode",aci.prog_code),
                             xmlElement("RecipientID",aci.recipient_id), 
                             xmlElement("FederalProjectNumber",aci.proj_num),
                             xmlElement("LineNumber",aci.detail_line_num),
                             xmlElement("ImprovementType",aci.improvement_type),
                             xmlElement("ACFunds",aci.federal_fund),
                             xmlElement("ACConvAmount",aci.bill_suspen))) as result
  from (select fai.proj_num,   
               'modify' proj_action,
               recipient_id,
               fai.prog_code, 
               fai.detail_line_num, 
               fai.improvement_type,
               fai.federal_fund,
               fai.advance_const,
               fai.bill_suspen,
               row_number() over (partition by fai.proj_num 
                                  order by fai.proj_num, fai.advance_const desc) rn
          from fmis_ac_intfc fai) aci;

output:
<Projects><Projects action="modify"><ProgramCode>M0E1</ProgramCode><RecipientID>36</RecipientID><FederalProjectNumber>0229473</FederalProjectNumber><LineNumber>31</LineNumber><ImprovementType>17</ImprovementType><ACFunds>0</ACFunds><ACConvAmount>791288</ACConvAmount></Projects></Projects>
<Projects><Projects action="modify"><ProgramCode>M0E1</ProgramCode><RecipientID>36</RecipientID><FederalProjectNumber>0229473</FederalProjectNumber><LineNumber>30</LineNumber><ImprovementType>47</ImprovementType><ACFunds>0</ACFunds><ACConvAmount>791288</ACConvAmount></Projects></Projects>
<Projects><Projects action="modify"><ProgramCode>H230</ProgramCode><RecipientID>36</RecipientID><FederalProjectNumber>0759873</FederalProjectNumber><LineNumber>31</LineNumber><ImprovementType>17</ImprovementType><ACFunds>0</ACFunds><ACConvAmount>1001</ACConvAmount></Projects></Projects>
<Projects><Projects action="modify"><ProgramCode>H230</ProgramCode><RecipientID>36</RecipientID><FederalProjectNumber>0759873</FederalProjectNumber><LineNumber>30</LineNumber><ImprovementType>21</ImprovementType><ACFunds>0</ACFunds><ACConvAmount>1001</ACConvAmount></Projects></Projects>

these are the create table and sample data:

create table fmis_ac_intfc
(proj_num         varchar2(40),
 proj_action      varchar2(40),
 prog_code        varchar2(40),
 recipient_id     number,
 detail_line_num  number,
 improvement_type varchar2(40),
 federal_fund     number,
 advance_const    number,
 bill_suspen      number)
 

Insert into FMIS_AC_INTFC (PROJ_NUM,PROJ_ACTION,PROG_CODE,RECIPIENT_ID,DETAIL_LINE_NUM,IMPROVEMENT_TYPE,FEDERAL_FUND,ADVANCE_CONST,BILL_SUSPEN) 
values ('0229473','modify','M0E1',36,31,'17',0,1340213,791288);
Insert into FMIS_AC_INTFC (PROJ_NUM,PROJ_ACTION,PROG_CODE,RECIPIENT_ID,DETAIL_LINE_NUM,IMPROVEMENT_TYPE,FEDERAL_FUND,ADVANCE_CONST,BILL_SUSPEN) 
values ('0229473','modify','M0E1',36,30,'47',0,15685747,791288);
Insert into FMIS_AC_INTFC (PROJ_NUM,PROJ_ACTION,PROG_CODE,RECIPIENT_ID,DETAIL_LINE_NUM,IMPROVEMENT_TYPE,FEDERAL_FUND,ADVANCE_CONST,BILL_SUSPEN) 
values ('0759873','modify','H230',36,31,'17',0,1215000,1001);
Insert into FMIS_AC_INTFC (PROJ_NUM,PROJ_ACTION,PROG_CODE,RECIPIENT_ID,DETAIL_LINE_NUM,IMPROVEMENT_TYPE,FEDERAL_FUND,ADVANCE_CONST,BILL_SUSPEN) 
values ('0759873','modify','H230',36,30,'21',0,6715180,1001);

please advise. thank you.

[Updated on: Mon, 07 December 2015 14:30]

Report message to a moderator

Re: Generate XML Extract [message #645521 is a reply to message #645509]

Tue, 08 December 2015 00:24

msol25
Messages: 396
Registered: June 2011

Senior Member

Dear,

You can fetch data from xml using extractvalue function in your select query and can insert data into your table.Please read below mentioned document.

https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions054.htm

Report message to a moderator

Re: Generate XML Extract [message #645523 is a reply to message #645521]

Tue, 08 December 2015 01:26

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You missed the point, it is the opposite the input is in basic data types (numbers, strings) and the output is XML.

Report message to a moderator

Re: Generate XML Extract [message #645527 is a reply to message #645509]

Tue, 08 December 2015 02:15

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select xmlserialize (document
  2           xmlelement(
  3             "Projects",
  4             xmlagg(
  5               xmlelement(
  6                 "Project",
  7                 xmlattributes(proj_action "action"),
  8                 xmlforest(
  9                   prog_code    "ProgramCode",
 10                   recipient_id "RecipientId",
 11                   proj_num     "FederalProjectNumber",
 12                   details "Details"))
 13               order by prog_code, proj_action))
 14           indent size = 2) res
 15  from ( select proj_action, prog_code, recipient_id, proj_num,
 16                xmlagg(
 17                  xmlelement(
 18                    "Detail",
 19                    xmlattributes(proj_action "action"),
 20                    xmlforest(
 21                      detail_line_num  "LineNumber",
 22                      prog_code        "ProgramCode",
 23                      improvement_type "ImprovementType",
 24                      advance_const    "ACFunds",
 25                      federal_fund     "FederalFunds",
 26                      bill_suspen       "ACConvAmount"))
 27                  order by detail_line_num) details
 28          from fmis_ac_intfc
 29          group by proj_action, prog_code, recipient_id, proj_num )
 30  /
RES
------------------------------------------------------------------------------------------
<Projects>
  <Project action="modify">
    <ProgramCode>H230</ProgramCode>
    <RecipientId>36</RecipientId>
    <FederalProjectNumber>0759873</FederalProjectNumber>
    <Details>
      <Detail action="modify">
        <LineNumber>30</LineNumber>
        <ProgramCode>H230</ProgramCode>
        <ImprovementType>21</ImprovementType>
        <ACFunds>6715180</ACFunds>
        <FederalFunds>0</FederalFunds>
        <ACConvAmount>1001</ACConvAmount>
      </Detail>
      <Detail action="modify">
        <LineNumber>31</LineNumber>
        <ProgramCode>H230</ProgramCode>
        <ImprovementType>17</ImprovementType>
        <ACFunds>1215000</ACFunds>
        <FederalFunds>0</FederalFunds>
        <ACConvAmount>1001</ACConvAmount>
      </Detail>
    </Details>
  </Project>
  <Project action="modify">
    <ProgramCode>M0E1</ProgramCode>
    <RecipientId>36</RecipientId>
    <FederalProjectNumber>0229473</FederalProjectNumber>
    <Details>
      <Detail action="modify">
        <LineNumber>30</LineNumber>
        <ProgramCode>M0E1</ProgramCode>
        <ImprovementType>47</ImprovementType>
        <ACFunds>15685747</ACFunds>
        <FederalFunds>0</FederalFunds>
        <ACConvAmount>791288</ACConvAmount>
      </Detail>
      <Detail action="modify">
        <LineNumber>31</LineNumber>
        <ProgramCode>M0E1</ProgramCode>
        <ImprovementType>17</ImprovementType>
        <ACFunds>1340213</ACFunds>
        <FederalFunds>0</FederalFunds>
        <ACConvAmount>791288</ACConvAmount>
      </Detail>
    </Details>
  </Project>
</Projects>

[Updated on: Tue, 08 December 2015 02:48]

Report message to a moderator

Re: Generate XML Extract [message #645548 is a reply to message #645527]

Tue, 08 December 2015 07:59

wtolentino
Messages: 398
Registered: March 2005

Senior Member

perfect thanks so much.

Report message to a moderator

Re: Generate XML Extract [message #645881 is a reply to message #645527]

Tue, 15 December 2015 14:13

wtolentino
Messages: 398
Registered: March 2005

Senior Member

i need to add a header to the xml

<?xml version="1.0" encoding="utf-8"?>

i tried to use xmlcomment with xmlconcat but it returns with a !-- on the beginning and -- on the end of the element.

<!--?xml version="1.0" encoding="utf-8"?-->
<Projects>
  <Project action="modify">
    <ProgramCode>H230</ProgramCode>
    <RecipientId>36</RecipientId>
    <FederalProjectNumber>0759873</FederalProjectNumber>
    <Details>
      <Detail action="modify">
        <LineNumber>30</LineNumber>
        <ProgramCode>H230</ProgramCode>
        <ImprovementType>21</ImprovementType>
        <ACFunds>6715180</ACFunds>
        <FederalFunds>0</FederalFunds>
        <ACConvAmount>1001</ACConvAmount>
      </Detail>
      <Detail action="modify">
        <LineNumber>31</LineNumber>
        <ProgramCode>H230</ProgramCode>
        <ImprovementType>17</ImprovementType>
        <ACFunds>1215000</ACFunds>
        <FederalFunds>0</FederalFunds>
        <ACConvAmount>1001</ACConvAmount>
      </Detail>
    </Details>
  </Project>
  <Project action="modify">
    <ProgramCode>M0E1</ProgramCode>
    <RecipientId>36</RecipientId>
    <FederalProjectNumber>0229473</FederalProjectNumber>
    <Details>
      <Detail action="modify">
        <LineNumber>30</LineNumber>
        <ProgramCode>M0E1</ProgramCode>
        <ImprovementType>47</ImprovementType>
        <ACFunds>15685747</ACFunds>
        <FederalFunds>0</FederalFunds>
        <ACConvAmount>791288</ACConvAmount>
      </Detail>
      <Detail action="modify">
        <LineNumber>31</LineNumber>
        <ProgramCode>M0E1</ProgramCode>
        <ImprovementType>17</ImprovementType>
        <ACFunds>1340213</ACFunds>
        <FederalFunds>0</FederalFunds>
        <ACConvAmount>791288</ACConvAmount>
      </Detail>
    </Details>
  </Project>
</Projects>

this is the code:

 select xmlserialize (document
           XMLconcat(
          xmlcomment('?xml version="1.0" encoding="utf-8"?'),
          xmlelement(
             "Projects",
             xmlagg(
               xmlelement(
                 "Project",
                 xmlattributes(proj_action "action"),
                 xmlforest(
                   prog_code    "ProgramCode",
                   recipient_id "RecipientId",
                   proj_num     "FederalProjectNumber",
                   details "Details"))
               order by prog_code, proj_action)))
           indent size = 2) res
  from ( select proj_action, prog_code, recipient_id, proj_num,
                xmlagg(
                  xmlelement(
                    "Detail",
                    xmlattributes(proj_action "action"),
                    xmlforest(
                      detail_line_num  "LineNumber",
                      prog_code        "ProgramCode",
                      improvement_type "ImprovementType",
                      advance_const    "ACFunds",
                      federal_fund     "FederalFunds",
                      bill_suspen       "ACConvAmount"))
                  order by detail_line_num) details
          from fmis_ac_intfc
          group by proj_action, prog_code, recipient_id, proj_num )

please advise. thank you.

Report message to a moderator

Re: Generate XML Extract [message #645883 is a reply to message #645881]

Tue, 15 December 2015 14:28

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can simply use concatenate operator:

SQL> select '<?xml version="1.0" encoding="utf-8"?>
  2  ' || xmlserialize (document
  3           xmlelement(
  4             "Projects",
  5             xmlagg(
  6               xmlelement(
  7                 "Project",
  8                 xmlattributes(proj_action "action"),
  9                 xmlforest(
 10                   prog_code    "ProgramCode",
 11                   recipient_id "RecipientId",
 12                   proj_num     "FederalProjectNumber",
 13                   details "Details"))
 14               order by prog_code, proj_action))
 15           indent size = 2) res
 16  from ( select proj_action, prog_code, recipient_id, proj_num,
 17                xmlagg(
 18                  xmlelement(
 19                    "Detail",
 20                    xmlattributes(proj_action "action"),
 21                    xmlforest(
 22                      detail_line_num  "LineNumber",
 23                      prog_code        "ProgramCode",
 24                      improvement_type "ImprovementType",
 25                      advance_const    "ACFunds",
 26                      federal_fund     "FederalFunds",
 27                      bill_suspen       "ACConvAmount"))
 28                  order by detail_line_num) details
 29          from fmis_ac_intfc
 30          group by proj_action, prog_code, recipient_id, proj_num )
 31  /
RES
-------------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<Projects>
  <Project action="modify">
    <ProgramCode>H230</ProgramCode>
    <RecipientId>36</RecipientId>
    <FederalProjectNumber>0759873</FederalProjectNumber>
    <Details>
      <Detail action="modify">
        <LineNumber>30</LineNumber>
        <ProgramCode>H230</ProgramCode>
        <ImprovementType>21</ImprovementType>
        <ACFunds>6715180</ACFunds>
        <FederalFunds>0</FederalFunds>
        <ACConvAmount>1001</ACConvAmount>
      </Detail>
      <Detail action="modify">
        <LineNumber>31</LineNumber>
        <ProgramCode>H230</ProgramCode>
        <ImprovementType>17</ImprovementType>
        <ACFunds>1215000</ACFunds>
        <FederalFunds>0</FederalFunds>
        <ACConvAmount>1001</ACConvAmount>
      </Detail>
    </Details>
  </Project>
  <Project action="modify">
    <ProgramCode>M0E1</ProgramCode>
    <RecipientId>36</RecipientId>
    <FederalProjectNumber>0229473</FederalProjectNumber>
    <Details>
      <Detail action="modify">
        <LineNumber>30</LineNumber>
        <ProgramCode>M0E1</ProgramCode>
        <ImprovementType>47</ImprovementType>
        <ACFunds>15685747</ACFunds>
        <FederalFunds>0</FederalFunds>
        <ACConvAmount>791288</ACConvAmount>
      </Detail>
      <Detail action="modify">
        <LineNumber>31</LineNumber>
        <ProgramCode>M0E1</ProgramCode>
        <ImprovementType>17</ImprovementType>
        <ACFunds>1340213</ACFunds>
        <FederalFunds>0</FederalFunds>
        <ACConvAmount>791288</ACConvAmount>
      </Detail>
    </Details>
  </Project>
</Projects>

Report message to a moderator

Re: Generate XML Extract [message #645885 is a reply to message #645883]

Tue, 15 December 2015 14:43

wtolentino
Messages: 398
Registered: March 2005

Senior Member

thanks. how do i push the next tag to the next line?

<?xml version="1.0" encoding="utf-8"?><Projects>
 <Project action="modify">
    <ProgramCode>M0E1</ProgramCode>
    <RecipientId>30</RecipientId>
...
...

so that i will look like this:

<?xml version="1.0" encoding="utf-8"?>
<Projects>
 <Project action="modify">
    <ProgramCode>M0E1</ProgramCode>
    <RecipientId>30</RecipientId>

Report message to a moderator

Re: Generate XML Extract [message #645894 is a reply to message #645885]

Wed, 16 December 2015 00:15

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Like I did it putting a new line in the previous string.

Report message to a moderator

Re: Generate XML Extract [message #645928 is a reply to message #645894]

Wed, 16 December 2015 08:20

wtolentino
Messages: 398
Registered: March 2005

Senior Member

thank so much i didn't think about that i can concatenate a string with xmltype. because when i first tried to concatenate a string nside the xmlelement it will not work because of datatype mismatch.

Report message to a moderator

Re: Generate XML Extract [message #645933 is a reply to message #645928]

Wed, 16 December 2015 08:30

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Xmlserialize apply a format to an XMLTYPE and returns a CLOB on which you can apply concatenate operator.
XMLTYPE contains a method named GetClobVal which also returns a CLOB from an XMLTYPE.
SQL*Plus automatically applies this method when you select an XMLTYPE.

[Updated on: Wed, 16 December 2015 08:31]

Report message to a moderator

Previous Topic:	sql query help to fetch sub string from xml
Next Topic:	Report adjustment

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 17 21:10:59 CDT 2024