Home » Developer & Programmer » JDeveloper, Java & XML » Generate XML Extract (11g, 11.2.0.3.0, aix 7.1)
| Generate XML Extract [message #645509] |
Mon, 07 December 2015 14:14  |
wtolentino
Messages: 422
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 #645527 is a reply to message #645509] |
Tue, 08 December 2015 02:15  |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #645881 is a reply to message #645527] |
Tue, 15 December 2015 14:13 |
wtolentino
Messages: 422
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.
|
|
|
|
| Re: Generate XML Extract [message #645883 is a reply to message #645881] |
Tue, 15 December 2015 14:28 |
|
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
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>
|
|
|
|
| Re: Generate XML Extract [message #645885 is a reply to message #645883] |
Tue, 15 December 2015 14:43 |
wtolentino
Messages: 422
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>
|
|
|
|
|
|
| Re: Generate XML Extract [message #645928 is a reply to message #645894] |
Wed, 16 December 2015 08:20 |
wtolentino
Messages: 422
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.
|
|
|
|
| Re: Generate XML Extract [message #645933 is a reply to message #645928] |
Wed, 16 December 2015 08:30 |
|
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
Goto Forum:
Current Time: Sun Jan 26 01:11:07 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
