Mixing
Transform table data to XML
0
I have data like this:
ID DUE_DATE KIND UNPAID_AMOUNT REQUIRED_AMOUNT PAID_AMOUNT RECEIVED ENTERED
---------- ---------- ---------- ------------- --------------- ----------- ---------- ----------
9331779 2017-08-11 11 606.42 127.59 2017-07-17 2017-07-18
9331779 2017-08-11 11 606.42 446.81 2017-08-16 2017-08-17
9331779 2017-08-11 11 606.42 32.02 2017-09-15 2017-09-18
9331782 2017-11-11 11 606.42 95.57 2017-11-15 2017-11-16
9331782 2017-11-11 11 606.42 446.81 2017-12-15 2017-12-18
9331782 2017-11-11 11 606.42 64.04 2018-01-03 2018-01-04
I want to transform this into XML data, looking like:
<payment-list>
<payment>
<kind>11</kind>
<due>2017-08-11</due>
<amount>null/0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-07-17</received>
<entered>2017-07-18</entered>
<amount>127.59</amount>
</related-payment>
<related-payment>
<received>2017-08-16</received>
<entered>2017-08-17</entered>
<amount>446.81</amount>
</related-payment>
<related-payment>...
</related-payment-list>
</payment>
<payment>
<kind>11</kind>
<due>2017-11-11</due>
<amount>null/0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-11-15</received>
<entered>2017-11-16</entered>
<amount>95.57</amount>
</related-payment>
<related-payment>
<received>2017-12-15</received>
<entered>2017-12-18</entered>
<amount>446.81</amount>
</related-payment>
<related-payment>...
</related-payment-list>
</payment>
<payment-list>
Can this be done in single query? I have an idea/way to do it in loop, but maybe someone has other idea, using XMLAgg etc.
sql xml oracle
edited Jan 2 at 12:01
Alex Poole
134k6108182
asked Jan 2 at 10:54
q4za4q4za4
31918
add a comment |
0
I have data like this:
ID DUE_DATE KIND UNPAID_AMOUNT REQUIRED_AMOUNT PAID_AMOUNT RECEIVED ENTERED
---------- ---------- ---------- ------------- --------------- ----------- ---------- ----------
9331779 2017-08-11 11 606.42 127.59 2017-07-17 2017-07-18
9331779 2017-08-11 11 606.42 446.81 2017-08-16 2017-08-17
9331779 2017-08-11 11 606.42 32.02 2017-09-15 2017-09-18
9331782 2017-11-11 11 606.42 95.57 2017-11-15 2017-11-16
9331782 2017-11-11 11 606.42 446.81 2017-12-15 2017-12-18
9331782 2017-11-11 11 606.42 64.04 2018-01-03 2018-01-04
I want to transform this into XML data, looking like:
<payment-list>
<payment>
<kind>11</kind>
<due>2017-08-11</due>
<amount>null/0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-07-17</received>
<entered>2017-07-18</entered>
<amount>127.59</amount>
</related-payment>
<related-payment>
<received>2017-08-16</received>
<entered>2017-08-17</entered>
<amount>446.81</amount>
</related-payment>
<related-payment>...
</related-payment-list>
</payment>
<payment>
<kind>11</kind>
<due>2017-11-11</due>
<amount>null/0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-11-15</received>
<entered>2017-11-16</entered>
<amount>95.57</amount>
</related-payment>
<related-payment>
<received>2017-12-15</received>
<entered>2017-12-18</entered>
<amount>446.81</amount>
</related-payment>
<related-payment>...
</related-payment-list>
</payment>
<payment-list>
Can this be done in single query? I have an idea/way to do it in loop, but maybe someone has other idea, using XMLAgg etc.
sql xml oracle
edited Jan 2 at 12:01
Alex Poole
134k6108182
asked Jan 2 at 10:54
q4za4q4za4
31918
add a comment |
0
0
0
I have data like this:
ID DUE_DATE KIND UNPAID_AMOUNT REQUIRED_AMOUNT PAID_AMOUNT RECEIVED ENTERED
---------- ---------- ---------- ------------- --------------- ----------- ---------- ----------
9331779 2017-08-11 11 606.42 127.59 2017-07-17 2017-07-18
9331779 2017-08-11 11 606.42 446.81 2017-08-16 2017-08-17
9331779 2017-08-11 11 606.42 32.02 2017-09-15 2017-09-18
9331782 2017-11-11 11 606.42 95.57 2017-11-15 2017-11-16
9331782 2017-11-11 11 606.42 446.81 2017-12-15 2017-12-18
9331782 2017-11-11 11 606.42 64.04 2018-01-03 2018-01-04
I want to transform this into XML data, looking like:
<payment-list>
<payment>
<kind>11</kind>
<due>2017-08-11</due>
<amount>null/0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-07-17</received>
<entered>2017-07-18</entered>
<amount>127.59</amount>
</related-payment>
<related-payment>
<received>2017-08-16</received>
<entered>2017-08-17</entered>
<amount>446.81</amount>
</related-payment>
<related-payment>...
</related-payment-list>
</payment>
<payment>
<kind>11</kind>
<due>2017-11-11</due>
<amount>null/0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-11-15</received>
<entered>2017-11-16</entered>
<amount>95.57</amount>
</related-payment>
<related-payment>
<received>2017-12-15</received>
<entered>2017-12-18</entered>
<amount>446.81</amount>
</related-payment>
<related-payment>...
</related-payment-list>
</payment>
<payment-list>
Can this be done in single query? I have an idea/way to do it in loop, but maybe someone has other idea, using XMLAgg etc.
sql xml oracle
edited Jan 2 at 12:01
Alex Poole
134k6108182
asked Jan 2 at 10:54
q4za4q4za4
31918
I have data like this:
ID DUE_DATE KIND UNPAID_AMOUNT REQUIRED_AMOUNT PAID_AMOUNT RECEIVED ENTERED
---------- ---------- ---------- ------------- --------------- ----------- ---------- ----------
9331779 2017-08-11 11 606.42 127.59 2017-07-17 2017-07-18
9331779 2017-08-11 11 606.42 446.81 2017-08-16 2017-08-17
9331779 2017-08-11 11 606.42 32.02 2017-09-15 2017-09-18
9331782 2017-11-11 11 606.42 95.57 2017-11-15 2017-11-16
9331782 2017-11-11 11 606.42 446.81 2017-12-15 2017-12-18
9331782 2017-11-11 11 606.42 64.04 2018-01-03 2018-01-04
I want to transform this into XML data, looking like:
<payment-list>
<payment>
<kind>11</kind>
<due>2017-08-11</due>
<amount>null/0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-07-17</received>
<entered>2017-07-18</entered>
<amount>127.59</amount>
</related-payment>
<related-payment>
<received>2017-08-16</received>
<entered>2017-08-17</entered>
<amount>446.81</amount>
</related-payment>
<related-payment>...
</related-payment-list>
</payment>
<payment>
<kind>11</kind>
<due>2017-11-11</due>
<amount>null/0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-11-15</received>
<entered>2017-11-16</entered>
<amount>95.57</amount>
</related-payment>
<related-payment>
<received>2017-12-15</received>
<entered>2017-12-18</entered>
<amount>446.81</amount>
</related-payment>
<related-payment>...
</related-payment-list>
</payment>
<payment-list>
Can this be done in single query? I have an idea/way to do it in loop, but maybe someone has other idea, using XMLAgg etc.
sql xml oracle
sql xml oracle
edited Jan 2 at 12:01
Alex Poole
134k6108182
asked Jan 2 at 10:54
q4za4q4za4
31918
edited Jan 2 at 12:01
Alex Poole
134k6108182
asked Jan 2 at 10:54
q4za4q4za4
31918
edited Jan 2 at 12:01
Alex Poole
134k6108182
edited Jan 2 at 12:01
Alex Poole
134k6108182
edited Jan 2 at 12:01
Alex Poole
134k6108182
134k6108182
asked Jan 2 at 10:54
q4za4q4za4
31918
asked Jan 2 at 10:54
q4za4q4za4
31918
asked Jan 2 at 10:54
q4za4q4za4
31918
31918
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
Nesting XMLAgg() calls is a bit tricky, but you could do this in two stages; first get each ID/due/kind/amount with a list of related payments:
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(xmlelement("related-payment",
xmlforest(received as "received", entered as "entered", paid_amount as "amount")))
from your_table
group by id, due_date, kind, unpaid_amount, required_amount;
which with your sample data gives (with the XMl fragment serialized for readability):
ID DUE_DATE KIND UNPAID_AMOUNT REQUIRED_AMOUNT XMLSERIALIZE(CONTENTXMLAGG(XMLELEMENT("RELATED-PAYMENT",XMLFOREST(RECEIVEDAS"REC
---------- ---------- ---------- ------------- --------------- --------------------------------------------------------------------------------
9331779 2017-08-11 11 606.42 <related-payment>
<received>2017-07-17</received>
<entered>2017-07-18</entered>
<amount>127.59</amount>
</related-payment>
<related-payment>
<received>2017-09-15</received>
...
and then use that as an inline view for the second level of aggregation:
select
xmlelement("payment-list",
xmlagg(
xmlelement("payment",
xmlforest(kind as "kind",
to_char(due_date, 'YYYY-MM-DD') as "due",
nvl(unpaid_amount, 0) as "amount",
nvl(required_amount, 0) as "waiting",
related_payments as "related-payment-list"
)
)
)
) as result
from (
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(
xmlelement("related-payment",
xmlforest(received as "received",
entered as "entered",
paid_amount as "amount"
)
)
) as related_payments
from your_table
group by id, due_date, kind, unpaid_amount, required_amount
);
Demo with your sample data as a CTE, and again adding serialization for readability:
-- CTE for sample data
with your_table (id, due_date, kind, unpaid_amount, required_amount, paid_amount, received, entered) as (
select 9331779, date '2017-08-11', 11, null, 606.42, 127.59, date '2017-07-17', date '2017-07-18' from dual
union all select 9331779, date '2017-08-11', 11, null, 606.42, 446.81, date '2017-08-16', date '2017-08-17' from dual
union all select 9331779, date '2017-08-11', 11, null, 606.42, 32.02, date '2017-09-15', date '2017-09-18' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 95.57, date '2017-11-15', date '2017-11-16' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 446.81, date '2017-12-15', date '2017-12-18' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 64.04, date '2018-01-03', date '2018-01-04' from dual
)
-- actual query
select xmlserialize(document
xmlelement("payment-list",
xmlagg(
xmlelement("payment",
xmlforest(kind as "kind",
to_char(due_date, 'YYYY-MM-DD') as "due",
nvl(unpaid_amount, 0) as "amount",
nvl(required_amount, 0) as "waiting",
related_payments as "related-payment-list"
)
)
)
)
indent size=2) as result
from (
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(
xmlelement("related-payment",
xmlforest(received as "received",
entered as "entered",
paid_amount as "amount"
)
)
) as related_payments
from your_table
group by id, due_date, kind, unpaid_amount, required_amount
);
which gets:
RESULT
--------------------------------------------------------------------------------
<payment-list>
<payment>
<kind>11</kind>
<due>2017-08-11</due>
<amount>0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-07-17</received>
<entered>2017-07-18</entered>
<amount>127.59</amount>
</related-payment>
<related-payment>
<received>2017-09-15</received>
<entered>2017-09-18</entered>
<amount>32.02</amount>
</related-payment>
<related-payment>
<received>2017-08-16</received>
<entered>2017-08-17</entered>
<amount>446.81</amount>
</related-payment>
</related-payment-list>
</payment>
<payment>
<kind>11</kind>
<due>2017-11-11</due>
<amount>0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-11-15</received>
<entered>2017-11-16</entered>
<amount>95.57</amount>
</related-payment>
<related-payment>
<received>2018-01-03</received>
<entered>2018-01-04</entered>
<amount>64.04</amount>
</related-payment>
<related-payment>
<received>2017-12-15</received>
<entered>2017-12-18</entered>
<amount>446.81</amount>
</related-payment>
</related-payment-list>
</payment>
</payment-list>
answered Jan 2 at 11:57
Alex PooleAlex Poole
134k6108182
Smooth as... Thanks!
– q4za4
Jan 2 at 12:50
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54005042%2ftransform-table-data-to-xml%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Nesting XMLAgg() calls is a bit tricky, but you could do this in two stages; first get each ID/due/kind/amount with a list of related payments:
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(xmlelement("related-payment",
xmlforest(received as "received", entered as "entered", paid_amount as "amount")))
from your_table
group by id, due_date, kind, unpaid_amount, required_amount;
which with your sample data gives (with the XMl fragment serialized for readability):
ID DUE_DATE KIND UNPAID_AMOUNT REQUIRED_AMOUNT XMLSERIALIZE(CONTENTXMLAGG(XMLELEMENT("RELATED-PAYMENT",XMLFOREST(RECEIVEDAS"REC
---------- ---------- ---------- ------------- --------------- --------------------------------------------------------------------------------
9331779 2017-08-11 11 606.42 <related-payment>
<received>2017-07-17</received>
<entered>2017-07-18</entered>
<amount>127.59</amount>
</related-payment>
<related-payment>
<received>2017-09-15</received>
...
and then use that as an inline view for the second level of aggregation:
select
xmlelement("payment-list",
xmlagg(
xmlelement("payment",
xmlforest(kind as "kind",
to_char(due_date, 'YYYY-MM-DD') as "due",
nvl(unpaid_amount, 0) as "amount",
nvl(required_amount, 0) as "waiting",
related_payments as "related-payment-list"
)
)
)
) as result
from (
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(
xmlelement("related-payment",
xmlforest(received as "received",
entered as "entered",
paid_amount as "amount"
)
)
) as related_payments
from your_table
group by id, due_date, kind, unpaid_amount, required_amount
);
Demo with your sample data as a CTE, and again adding serialization for readability:
-- CTE for sample data
with your_table (id, due_date, kind, unpaid_amount, required_amount, paid_amount, received, entered) as (
select 9331779, date '2017-08-11', 11, null, 606.42, 127.59, date '2017-07-17', date '2017-07-18' from dual
union all select 9331779, date '2017-08-11', 11, null, 606.42, 446.81, date '2017-08-16', date '2017-08-17' from dual
union all select 9331779, date '2017-08-11', 11, null, 606.42, 32.02, date '2017-09-15', date '2017-09-18' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 95.57, date '2017-11-15', date '2017-11-16' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 446.81, date '2017-12-15', date '2017-12-18' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 64.04, date '2018-01-03', date '2018-01-04' from dual
)
-- actual query
select xmlserialize(document
xmlelement("payment-list",
xmlagg(
xmlelement("payment",
xmlforest(kind as "kind",
to_char(due_date, 'YYYY-MM-DD') as "due",
nvl(unpaid_amount, 0) as "amount",
nvl(required_amount, 0) as "waiting",
related_payments as "related-payment-list"
)
)
)
)
indent size=2) as result
from (
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(
xmlelement("related-payment",
xmlforest(received as "received",
entered as "entered",
paid_amount as "amount"
)
)
) as related_payments
from your_table
group by id, due_date, kind, unpaid_amount, required_amount
);
which gets:
RESULT
--------------------------------------------------------------------------------
<payment-list>
<payment>
<kind>11</kind>
<due>2017-08-11</due>
<amount>0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-07-17</received>
<entered>2017-07-18</entered>
<amount>127.59</amount>
</related-payment>
<related-payment>
<received>2017-09-15</received>
<entered>2017-09-18</entered>
<amount>32.02</amount>
</related-payment>
<related-payment>
<received>2017-08-16</received>
<entered>2017-08-17</entered>
<amount>446.81</amount>
</related-payment>
</related-payment-list>
</payment>
<payment>
<kind>11</kind>
<due>2017-11-11</due>
<amount>0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-11-15</received>
<entered>2017-11-16</entered>
<amount>95.57</amount>
</related-payment>
<related-payment>
<received>2018-01-03</received>
<entered>2018-01-04</entered>
<amount>64.04</amount>
</related-payment>
<related-payment>
<received>2017-12-15</received>
<entered>2017-12-18</entered>
<amount>446.81</amount>
</related-payment>
</related-payment-list>
</payment>
</payment-list>
answered Jan 2 at 11:57
Alex PooleAlex Poole
134k6108182
Smooth as... Thanks!
– q4za4
Jan 2 at 12:50
add a comment |
1
Nesting XMLAgg() calls is a bit tricky, but you could do this in two stages; first get each ID/due/kind/amount with a list of related payments:
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(xmlelement("related-payment",
xmlforest(received as "received", entered as "entered", paid_amount as "amount")))
from your_table
group by id, due_date, kind, unpaid_amount, required_amount;
which with your sample data gives (with the XMl fragment serialized for readability):
ID DUE_DATE KIND UNPAID_AMOUNT REQUIRED_AMOUNT XMLSERIALIZE(CONTENTXMLAGG(XMLELEMENT("RELATED-PAYMENT",XMLFOREST(RECEIVEDAS"REC
---------- ---------- ---------- ------------- --------------- --------------------------------------------------------------------------------
9331779 2017-08-11 11 606.42 <related-payment>
<received>2017-07-17</received>
<entered>2017-07-18</entered>
<amount>127.59</amount>
</related-payment>
<related-payment>
<received>2017-09-15</received>
...
and then use that as an inline view for the second level of aggregation:
select
xmlelement("payment-list",
xmlagg(
xmlelement("payment",
xmlforest(kind as "kind",
to_char(due_date, 'YYYY-MM-DD') as "due",
nvl(unpaid_amount, 0) as "amount",
nvl(required_amount, 0) as "waiting",
related_payments as "related-payment-list"
)
)
)
) as result
from (
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(
xmlelement("related-payment",
xmlforest(received as "received",
entered as "entered",
paid_amount as "amount"
)
)
) as related_payments
from your_table
group by id, due_date, kind, unpaid_amount, required_amount
);
Demo with your sample data as a CTE, and again adding serialization for readability:
-- CTE for sample data
with your_table (id, due_date, kind, unpaid_amount, required_amount, paid_amount, received, entered) as (
select 9331779, date '2017-08-11', 11, null, 606.42, 127.59, date '2017-07-17', date '2017-07-18' from dual
union all select 9331779, date '2017-08-11', 11, null, 606.42, 446.81, date '2017-08-16', date '2017-08-17' from dual
union all select 9331779, date '2017-08-11', 11, null, 606.42, 32.02, date '2017-09-15', date '2017-09-18' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 95.57, date '2017-11-15', date '2017-11-16' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 446.81, date '2017-12-15', date '2017-12-18' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 64.04, date '2018-01-03', date '2018-01-04' from dual
)
-- actual query
select xmlserialize(document
xmlelement("payment-list",
xmlagg(
xmlelement("payment",
xmlforest(kind as "kind",
to_char(due_date, 'YYYY-MM-DD') as "due",
nvl(unpaid_amount, 0) as "amount",
nvl(required_amount, 0) as "waiting",
related_payments as "related-payment-list"
)
)
)
)
indent size=2) as result
from (
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(
xmlelement("related-payment",
xmlforest(received as "received",
entered as "entered",
paid_amount as "amount"
)
)
) as related_payments
from your_table
group by id, due_date, kind, unpaid_amount, required_amount
);
which gets:
RESULT
--------------------------------------------------------------------------------
<payment-list>
<payment>
<kind>11</kind>
<due>2017-08-11</due>
<amount>0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-07-17</received>
<entered>2017-07-18</entered>
<amount>127.59</amount>
</related-payment>
<related-payment>
<received>2017-09-15</received>
<entered>2017-09-18</entered>
<amount>32.02</amount>
</related-payment>
<related-payment>
<received>2017-08-16</received>
<entered>2017-08-17</entered>
<amount>446.81</amount>
</related-payment>
</related-payment-list>
</payment>
<payment>
<kind>11</kind>
<due>2017-11-11</due>
<amount>0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-11-15</received>
<entered>2017-11-16</entered>
<amount>95.57</amount>
</related-payment>
<related-payment>
<received>2018-01-03</received>
<entered>2018-01-04</entered>
<amount>64.04</amount>
</related-payment>
<related-payment>
<received>2017-12-15</received>
<entered>2017-12-18</entered>
<amount>446.81</amount>
</related-payment>
</related-payment-list>
</payment>
</payment-list>
answered Jan 2 at 11:57
Alex PooleAlex Poole
134k6108182
Smooth as... Thanks!
– q4za4
Jan 2 at 12:50
add a comment |
1
1
1
Nesting XMLAgg() calls is a bit tricky, but you could do this in two stages; first get each ID/due/kind/amount with a list of related payments:
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(xmlelement("related-payment",
xmlforest(received as "received", entered as "entered", paid_amount as "amount")))
from your_table
group by id, due_date, kind, unpaid_amount, required_amount;
which with your sample data gives (with the XMl fragment serialized for readability):
ID DUE_DATE KIND UNPAID_AMOUNT REQUIRED_AMOUNT XMLSERIALIZE(CONTENTXMLAGG(XMLELEMENT("RELATED-PAYMENT",XMLFOREST(RECEIVEDAS"REC
---------- ---------- ---------- ------------- --------------- --------------------------------------------------------------------------------
9331779 2017-08-11 11 606.42 <related-payment>
<received>2017-07-17</received>
<entered>2017-07-18</entered>
<amount>127.59</amount>
</related-payment>
<related-payment>
<received>2017-09-15</received>
...
and then use that as an inline view for the second level of aggregation:
select
xmlelement("payment-list",
xmlagg(
xmlelement("payment",
xmlforest(kind as "kind",
to_char(due_date, 'YYYY-MM-DD') as "due",
nvl(unpaid_amount, 0) as "amount",
nvl(required_amount, 0) as "waiting",
related_payments as "related-payment-list"
)
)
)
) as result
from (
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(
xmlelement("related-payment",
xmlforest(received as "received",
entered as "entered",
paid_amount as "amount"
)
)
) as related_payments
from your_table
group by id, due_date, kind, unpaid_amount, required_amount
);
Demo with your sample data as a CTE, and again adding serialization for readability:
-- CTE for sample data
with your_table (id, due_date, kind, unpaid_amount, required_amount, paid_amount, received, entered) as (
select 9331779, date '2017-08-11', 11, null, 606.42, 127.59, date '2017-07-17', date '2017-07-18' from dual
union all select 9331779, date '2017-08-11', 11, null, 606.42, 446.81, date '2017-08-16', date '2017-08-17' from dual
union all select 9331779, date '2017-08-11', 11, null, 606.42, 32.02, date '2017-09-15', date '2017-09-18' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 95.57, date '2017-11-15', date '2017-11-16' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 446.81, date '2017-12-15', date '2017-12-18' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 64.04, date '2018-01-03', date '2018-01-04' from dual
)
-- actual query
select xmlserialize(document
xmlelement("payment-list",
xmlagg(
xmlelement("payment",
xmlforest(kind as "kind",
to_char(due_date, 'YYYY-MM-DD') as "due",
nvl(unpaid_amount, 0) as "amount",
nvl(required_amount, 0) as "waiting",
related_payments as "related-payment-list"
)
)
)
)
indent size=2) as result
from (
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(
xmlelement("related-payment",
xmlforest(received as "received",
entered as "entered",
paid_amount as "amount"
)
)
) as related_payments
from your_table
group by id, due_date, kind, unpaid_amount, required_amount
);
which gets:
RESULT
--------------------------------------------------------------------------------
<payment-list>
<payment>
<kind>11</kind>
<due>2017-08-11</due>
<amount>0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-07-17</received>
<entered>2017-07-18</entered>
<amount>127.59</amount>
</related-payment>
<related-payment>
<received>2017-09-15</received>
<entered>2017-09-18</entered>
<amount>32.02</amount>
</related-payment>
<related-payment>
<received>2017-08-16</received>
<entered>2017-08-17</entered>
<amount>446.81</amount>
</related-payment>
</related-payment-list>
</payment>
<payment>
<kind>11</kind>
<due>2017-11-11</due>
<amount>0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-11-15</received>
<entered>2017-11-16</entered>
<amount>95.57</amount>
</related-payment>
<related-payment>
<received>2018-01-03</received>
<entered>2018-01-04</entered>
<amount>64.04</amount>
</related-payment>
<related-payment>
<received>2017-12-15</received>
<entered>2017-12-18</entered>
<amount>446.81</amount>
</related-payment>
</related-payment-list>
</payment>
</payment-list>
answered Jan 2 at 11:57
Alex PooleAlex Poole
134k6108182
Nesting XMLAgg() calls is a bit tricky, but you could do this in two stages; first get each ID/due/kind/amount with a list of related payments:
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(xmlelement("related-payment",
xmlforest(received as "received", entered as "entered", paid_amount as "amount")))
from your_table
group by id, due_date, kind, unpaid_amount, required_amount;
which with your sample data gives (with the XMl fragment serialized for readability):
ID DUE_DATE KIND UNPAID_AMOUNT REQUIRED_AMOUNT XMLSERIALIZE(CONTENTXMLAGG(XMLELEMENT("RELATED-PAYMENT",XMLFOREST(RECEIVEDAS"REC
---------- ---------- ---------- ------------- --------------- --------------------------------------------------------------------------------
9331779 2017-08-11 11 606.42 <related-payment>
<received>2017-07-17</received>
<entered>2017-07-18</entered>
<amount>127.59</amount>
</related-payment>
<related-payment>
<received>2017-09-15</received>
...
and then use that as an inline view for the second level of aggregation:
select
xmlelement("payment-list",
xmlagg(
xmlelement("payment",
xmlforest(kind as "kind",
to_char(due_date, 'YYYY-MM-DD') as "due",
nvl(unpaid_amount, 0) as "amount",
nvl(required_amount, 0) as "waiting",
related_payments as "related-payment-list"
)
)
)
) as result
from (
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(
xmlelement("related-payment",
xmlforest(received as "received",
entered as "entered",
paid_amount as "amount"
)
)
) as related_payments
from your_table
group by id, due_date, kind, unpaid_amount, required_amount
);
Demo with your sample data as a CTE, and again adding serialization for readability:
-- CTE for sample data
with your_table (id, due_date, kind, unpaid_amount, required_amount, paid_amount, received, entered) as (
select 9331779, date '2017-08-11', 11, null, 606.42, 127.59, date '2017-07-17', date '2017-07-18' from dual
union all select 9331779, date '2017-08-11', 11, null, 606.42, 446.81, date '2017-08-16', date '2017-08-17' from dual
union all select 9331779, date '2017-08-11', 11, null, 606.42, 32.02, date '2017-09-15', date '2017-09-18' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 95.57, date '2017-11-15', date '2017-11-16' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 446.81, date '2017-12-15', date '2017-12-18' from dual
union all select 9331782, date '2017-11-11', 11, null, 606.42, 64.04, date '2018-01-03', date '2018-01-04' from dual
)
-- actual query
select xmlserialize(document
xmlelement("payment-list",
xmlagg(
xmlelement("payment",
xmlforest(kind as "kind",
to_char(due_date, 'YYYY-MM-DD') as "due",
nvl(unpaid_amount, 0) as "amount",
nvl(required_amount, 0) as "waiting",
related_payments as "related-payment-list"
)
)
)
)
indent size=2) as result
from (
select id, due_date, kind, unpaid_amount, required_amount,
xmlagg(
xmlelement("related-payment",
xmlforest(received as "received",
entered as "entered",
paid_amount as "amount"
)
)
) as related_payments
from your_table
group by id, due_date, kind, unpaid_amount, required_amount
);
which gets:
RESULT
--------------------------------------------------------------------------------
<payment-list>
<payment>
<kind>11</kind>
<due>2017-08-11</due>
<amount>0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-07-17</received>
<entered>2017-07-18</entered>
<amount>127.59</amount>
</related-payment>
<related-payment>
<received>2017-09-15</received>
<entered>2017-09-18</entered>
<amount>32.02</amount>
</related-payment>
<related-payment>
<received>2017-08-16</received>
<entered>2017-08-17</entered>
<amount>446.81</amount>
</related-payment>
</related-payment-list>
</payment>
<payment>
<kind>11</kind>
<due>2017-11-11</due>
<amount>0</amount>
<waiting>606.42</waiting>
<related-payment-list>
<related-payment>
<received>2017-11-15</received>
<entered>2017-11-16</entered>
<amount>95.57</amount>
</related-payment>
<related-payment>
<received>2018-01-03</received>
<entered>2018-01-04</entered>
<amount>64.04</amount>
</related-payment>
<related-payment>
<received>2017-12-15</received>
<entered>2017-12-18</entered>
<amount>446.81</amount>
</related-payment>
</related-payment-list>
</payment>
</payment-list>
answered Jan 2 at 11:57
Alex PooleAlex Poole
134k6108182
answered Jan 2 at 11:57
Alex PooleAlex Poole
134k6108182
answered Jan 2 at 11:57
Alex PooleAlex Poole
134k6108182
answered Jan 2 at 11:57
Alex PooleAlex Poole
134k6108182
134k6108182
Smooth as... Thanks!
– q4za4
Jan 2 at 12:50
add a comment |
Smooth as... Thanks!
– q4za4
Jan 2 at 12:50
Smooth as... Thanks!
– q4za4
Jan 2 at 12:50
Smooth as... Thanks!
– q4za4
Jan 2 at 12:50
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54005042%2ftransform-table-data-to-xml%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
