How to solve complex XMLELEMENT in Oracle
problem
XML
<logs>
<log>
<ID>123456</ID>
<NAME>TEST</NAME>
</log>
</logs>
<logs>
<log>
<ID>1234567</ID>
<NAME>TEST1</NAME>
</log>
</logs>
in the above xml logs are closing just after log tag.
But i need XML where logs tag to closed in xml in given below.
<logs>
<log>
<ID>123456</ID>
<NAME>TEST</NAME>
</log>
<log>
<ID>1234567</ID>
<NAME>TEST1</NAME>
</log>
</logs>
code i tried
v_output is output variable of clob type.
BEGIN
x := XMLType (in_xml);
FOR r IN (SELECT EXTRACTVALUE (VALUE (p), '/log/id/text()') AS log_id
FROM TABLE (XMLSEQUENCE (EXTRACT (x, '/logs/log'))) p)
LOOP
OPEN cursorname (r.id);
FETCH cursorname INTO tmp;
-- fetching columns
IF cursorname%FOUND
THEN
SELECT XMLELEMENT ("logs",
XMLELEMENT ("log", XMLFOREST (id, name))).GETCLOBVAL ()
INTO out_xml
FROM view where id in ( rec1.id ) ;
v_output := out_xml;
DBMS_OUTPUT.PUT_LINE (v_output);
END IF;
CLOSE cursorname;
END LOOP;
can anyone suggest how can i solve multiple xml elements using xmlelement.
Input is XML and after parsing xml output should be xml.
if i have requirement Dyanamic XML i could use code below.
select xmltype(cursor(select * from view )) from dual;
cursor is in loop.
Any suggestion is most welcome.
xml plsql oracle11g
|
show 3 more comments
problem
XML
<logs>
<log>
<ID>123456</ID>
<NAME>TEST</NAME>
</log>
</logs>
<logs>
<log>
<ID>1234567</ID>
<NAME>TEST1</NAME>
</log>
</logs>
in the above xml logs are closing just after log tag.
But i need XML where logs tag to closed in xml in given below.
<logs>
<log>
<ID>123456</ID>
<NAME>TEST</NAME>
</log>
<log>
<ID>1234567</ID>
<NAME>TEST1</NAME>
</log>
</logs>
code i tried
v_output is output variable of clob type.
BEGIN
x := XMLType (in_xml);
FOR r IN (SELECT EXTRACTVALUE (VALUE (p), '/log/id/text()') AS log_id
FROM TABLE (XMLSEQUENCE (EXTRACT (x, '/logs/log'))) p)
LOOP
OPEN cursorname (r.id);
FETCH cursorname INTO tmp;
-- fetching columns
IF cursorname%FOUND
THEN
SELECT XMLELEMENT ("logs",
XMLELEMENT ("log", XMLFOREST (id, name))).GETCLOBVAL ()
INTO out_xml
FROM view where id in ( rec1.id ) ;
v_output := out_xml;
DBMS_OUTPUT.PUT_LINE (v_output);
END IF;
CLOSE cursorname;
END LOOP;
can anyone suggest how can i solve multiple xml elements using xmlelement.
Input is XML and after parsing xml output should be xml.
if i have requirement Dyanamic XML i could use code below.
select xmltype(cursor(select * from view )) from dual;
cursor is in loop.
Any suggestion is most welcome.
xml plsql oracle11g
The first 'XML' isn't valid and can't be treated as an XMLType, so it's slightly unclear what you are showing us - if that is your current output then what is the input (in_xml
)? Or where do the ID and name values come from? And what isview
? What iscursorname
?
– Alex Poole
Nov 12 '18 at 12:01
do no bother about xml validation please can give solution
– afeef
Nov 12 '18 at 12:06
I can't give you a solution if I don't understand your problem, the initial data and what you're trying to do.
– Alex Poole
Nov 12 '18 at 12:07
in_xml <logs> <log> <ID>123456</ID> </log> <log> <ID>1234567</ID> </log> </logs>
– afeef
Nov 12 '18 at 12:07
im trying to get details by accessing id and using cursor fetch data by id
– afeef
Nov 12 '18 at 12:08
|
show 3 more comments
problem
XML
<logs>
<log>
<ID>123456</ID>
<NAME>TEST</NAME>
</log>
</logs>
<logs>
<log>
<ID>1234567</ID>
<NAME>TEST1</NAME>
</log>
</logs>
in the above xml logs are closing just after log tag.
But i need XML where logs tag to closed in xml in given below.
<logs>
<log>
<ID>123456</ID>
<NAME>TEST</NAME>
</log>
<log>
<ID>1234567</ID>
<NAME>TEST1</NAME>
</log>
</logs>
code i tried
v_output is output variable of clob type.
BEGIN
x := XMLType (in_xml);
FOR r IN (SELECT EXTRACTVALUE (VALUE (p), '/log/id/text()') AS log_id
FROM TABLE (XMLSEQUENCE (EXTRACT (x, '/logs/log'))) p)
LOOP
OPEN cursorname (r.id);
FETCH cursorname INTO tmp;
-- fetching columns
IF cursorname%FOUND
THEN
SELECT XMLELEMENT ("logs",
XMLELEMENT ("log", XMLFOREST (id, name))).GETCLOBVAL ()
INTO out_xml
FROM view where id in ( rec1.id ) ;
v_output := out_xml;
DBMS_OUTPUT.PUT_LINE (v_output);
END IF;
CLOSE cursorname;
END LOOP;
can anyone suggest how can i solve multiple xml elements using xmlelement.
Input is XML and after parsing xml output should be xml.
if i have requirement Dyanamic XML i could use code below.
select xmltype(cursor(select * from view )) from dual;
cursor is in loop.
Any suggestion is most welcome.
xml plsql oracle11g
problem
XML
<logs>
<log>
<ID>123456</ID>
<NAME>TEST</NAME>
</log>
</logs>
<logs>
<log>
<ID>1234567</ID>
<NAME>TEST1</NAME>
</log>
</logs>
in the above xml logs are closing just after log tag.
But i need XML where logs tag to closed in xml in given below.
<logs>
<log>
<ID>123456</ID>
<NAME>TEST</NAME>
</log>
<log>
<ID>1234567</ID>
<NAME>TEST1</NAME>
</log>
</logs>
code i tried
v_output is output variable of clob type.
BEGIN
x := XMLType (in_xml);
FOR r IN (SELECT EXTRACTVALUE (VALUE (p), '/log/id/text()') AS log_id
FROM TABLE (XMLSEQUENCE (EXTRACT (x, '/logs/log'))) p)
LOOP
OPEN cursorname (r.id);
FETCH cursorname INTO tmp;
-- fetching columns
IF cursorname%FOUND
THEN
SELECT XMLELEMENT ("logs",
XMLELEMENT ("log", XMLFOREST (id, name))).GETCLOBVAL ()
INTO out_xml
FROM view where id in ( rec1.id ) ;
v_output := out_xml;
DBMS_OUTPUT.PUT_LINE (v_output);
END IF;
CLOSE cursorname;
END LOOP;
can anyone suggest how can i solve multiple xml elements using xmlelement.
Input is XML and after parsing xml output should be xml.
if i have requirement Dyanamic XML i could use code below.
select xmltype(cursor(select * from view )) from dual;
cursor is in loop.
Any suggestion is most welcome.
xml plsql oracle11g
xml plsql oracle11g
edited Nov 19 '18 at 9:46
IMSoP
46.1k65693
46.1k65693
asked Nov 12 '18 at 11:20
afeefafeef
481620
481620
The first 'XML' isn't valid and can't be treated as an XMLType, so it's slightly unclear what you are showing us - if that is your current output then what is the input (in_xml
)? Or where do the ID and name values come from? And what isview
? What iscursorname
?
– Alex Poole
Nov 12 '18 at 12:01
do no bother about xml validation please can give solution
– afeef
Nov 12 '18 at 12:06
I can't give you a solution if I don't understand your problem, the initial data and what you're trying to do.
– Alex Poole
Nov 12 '18 at 12:07
in_xml <logs> <log> <ID>123456</ID> </log> <log> <ID>1234567</ID> </log> </logs>
– afeef
Nov 12 '18 at 12:07
im trying to get details by accessing id and using cursor fetch data by id
– afeef
Nov 12 '18 at 12:08
|
show 3 more comments
The first 'XML' isn't valid and can't be treated as an XMLType, so it's slightly unclear what you are showing us - if that is your current output then what is the input (in_xml
)? Or where do the ID and name values come from? And what isview
? What iscursorname
?
– Alex Poole
Nov 12 '18 at 12:01
do no bother about xml validation please can give solution
– afeef
Nov 12 '18 at 12:06
I can't give you a solution if I don't understand your problem, the initial data and what you're trying to do.
– Alex Poole
Nov 12 '18 at 12:07
in_xml <logs> <log> <ID>123456</ID> </log> <log> <ID>1234567</ID> </log> </logs>
– afeef
Nov 12 '18 at 12:07
im trying to get details by accessing id and using cursor fetch data by id
– afeef
Nov 12 '18 at 12:08
The first 'XML' isn't valid and can't be treated as an XMLType, so it's slightly unclear what you are showing us - if that is your current output then what is the input (
in_xml
)? Or where do the ID and name values come from? And what is view
? What is cursorname
?– Alex Poole
Nov 12 '18 at 12:01
The first 'XML' isn't valid and can't be treated as an XMLType, so it's slightly unclear what you are showing us - if that is your current output then what is the input (
in_xml
)? Or where do the ID and name values come from? And what is view
? What is cursorname
?– Alex Poole
Nov 12 '18 at 12:01
do no bother about xml validation please can give solution
– afeef
Nov 12 '18 at 12:06
do no bother about xml validation please can give solution
– afeef
Nov 12 '18 at 12:06
I can't give you a solution if I don't understand your problem, the initial data and what you're trying to do.
– Alex Poole
Nov 12 '18 at 12:07
I can't give you a solution if I don't understand your problem, the initial data and what you're trying to do.
– Alex Poole
Nov 12 '18 at 12:07
in_xml <logs> <log> <ID>123456</ID> </log> <log> <ID>1234567</ID> </log> </logs>
– afeef
Nov 12 '18 at 12:07
in_xml <logs> <log> <ID>123456</ID> </log> <log> <ID>1234567</ID> </log> </logs>
– afeef
Nov 12 '18 at 12:07
im trying to get details by accessing id and using cursor fetch data by id
– afeef
Nov 12 '18 at 12:08
im trying to get details by accessing id and using cursor fetch data by id
– afeef
Nov 12 '18 at 12:08
|
show 3 more comments
1 Answer
1
active
oldest
votes
If I understand what you're trying to do, you're starting from an string version of an XML document like:
<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>
and trying to add the name
value corresponding to each of those id
value, which you get from a view.
You could extract all the IDs using an XMLTable, join those to the view, and reconstruct the final XML via XMLAgg, something like:
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
into out_xml
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
As a demo in plain SQL starting from your string, and with a CTE to represent your view:
-- CTE to represent your view
with your_view (id, name) as (
select 123456, 'TEST' from dual
union all
select 1234567, 'TEST1' from dual
)
-- actual query
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
from xmltable(
'/logs/log'
passing xmltype('<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>')
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
XMLELEMENT("LOGS",XMLAGG(XMLELEMENT("LOG",XMLFOREST(V.ID,V.NAME)))).GETCLOBVAL()
-----------------------------------------------------------------------------------------------------
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
or serialized to prettify it:
-- CTE to represent your view
with your_view (id, name) as (
select 123456, 'TEST' from dual
union all
select 1234567, 'TEST1' from dual
)
-- actual query
select xmlserialize(document
xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name))))
indent) as out_xml
from xmltable(
'/logs/log'
passing xmltype('<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>')
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
OUT_XML
--------------------------------------------------------------------------------
<logs>
<log>
<ID>123456</ID>
<NAME>TEST</NAME>
</log>
<log>
<ID>1234567</ID>
<NAME>TEST1</NAME>
</log>
</logs>
If you really wanted to use the mechanism shown in the question you linked to then you could build up the final output CLOB by appending each element generated inside the loop, with the start and end logs
tag added outside the loop:
declare
in_xml clob := '<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>';
out_xml clob;
v_output clob;
begin
v_output := '<logs>';
for r in (
select id
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
)
)
loop
select xmlelement("log", xmlforest (id, name)).getclobval()
into out_xml
from your_view where id = r.id;
v_output := v_output || out_xml;
end loop;
v_output := v_output || '</logs>';
dbms_output.put_line (v_output);
end;
/
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
PL/SQL procedure successfully completed.
But it's simpler to avoid the cursor loop and just join once:
declare
in_xml clob := '<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>';
v_output clob;
begin
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
into v_output
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
dbms_output.put_line (v_output);
end;
/
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
PL/SQL procedure successfully completed.
My Question is related to stackoverflow.com/questions/19763224/…
– afeef
Nov 12 '18 at 12:24
in input i were getting id in xml then i need to fetch records on for example where id (1233,12344) i have shown sample xml [id,name] in xml but there few more .
– afeef
Nov 12 '18 at 12:26
@afeef - well, only if you want to make your code more complicated and probably slower than it needs to be; you don't need to use loops or seperate queries against the view...
– Alex Poole
Nov 12 '18 at 12:26
@afeef - if there are more elements that you need to include and they all come from the same view, then just add those column names to thexmlforest()
call?
– Alex Poole
Nov 12 '18 at 12:27
Many Thanks for the help i will try solution and let you know.
– afeef
Nov 12 '18 at 12:29
|
show 4 more comments
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
});
}
});
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%2f53261098%2fhow-to-solve-complex-xmlelement-in-oracle%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
If I understand what you're trying to do, you're starting from an string version of an XML document like:
<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>
and trying to add the name
value corresponding to each of those id
value, which you get from a view.
You could extract all the IDs using an XMLTable, join those to the view, and reconstruct the final XML via XMLAgg, something like:
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
into out_xml
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
As a demo in plain SQL starting from your string, and with a CTE to represent your view:
-- CTE to represent your view
with your_view (id, name) as (
select 123456, 'TEST' from dual
union all
select 1234567, 'TEST1' from dual
)
-- actual query
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
from xmltable(
'/logs/log'
passing xmltype('<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>')
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
XMLELEMENT("LOGS",XMLAGG(XMLELEMENT("LOG",XMLFOREST(V.ID,V.NAME)))).GETCLOBVAL()
-----------------------------------------------------------------------------------------------------
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
or serialized to prettify it:
-- CTE to represent your view
with your_view (id, name) as (
select 123456, 'TEST' from dual
union all
select 1234567, 'TEST1' from dual
)
-- actual query
select xmlserialize(document
xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name))))
indent) as out_xml
from xmltable(
'/logs/log'
passing xmltype('<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>')
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
OUT_XML
--------------------------------------------------------------------------------
<logs>
<log>
<ID>123456</ID>
<NAME>TEST</NAME>
</log>
<log>
<ID>1234567</ID>
<NAME>TEST1</NAME>
</log>
</logs>
If you really wanted to use the mechanism shown in the question you linked to then you could build up the final output CLOB by appending each element generated inside the loop, with the start and end logs
tag added outside the loop:
declare
in_xml clob := '<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>';
out_xml clob;
v_output clob;
begin
v_output := '<logs>';
for r in (
select id
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
)
)
loop
select xmlelement("log", xmlforest (id, name)).getclobval()
into out_xml
from your_view where id = r.id;
v_output := v_output || out_xml;
end loop;
v_output := v_output || '</logs>';
dbms_output.put_line (v_output);
end;
/
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
PL/SQL procedure successfully completed.
But it's simpler to avoid the cursor loop and just join once:
declare
in_xml clob := '<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>';
v_output clob;
begin
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
into v_output
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
dbms_output.put_line (v_output);
end;
/
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
PL/SQL procedure successfully completed.
My Question is related to stackoverflow.com/questions/19763224/…
– afeef
Nov 12 '18 at 12:24
in input i were getting id in xml then i need to fetch records on for example where id (1233,12344) i have shown sample xml [id,name] in xml but there few more .
– afeef
Nov 12 '18 at 12:26
@afeef - well, only if you want to make your code more complicated and probably slower than it needs to be; you don't need to use loops or seperate queries against the view...
– Alex Poole
Nov 12 '18 at 12:26
@afeef - if there are more elements that you need to include and they all come from the same view, then just add those column names to thexmlforest()
call?
– Alex Poole
Nov 12 '18 at 12:27
Many Thanks for the help i will try solution and let you know.
– afeef
Nov 12 '18 at 12:29
|
show 4 more comments
If I understand what you're trying to do, you're starting from an string version of an XML document like:
<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>
and trying to add the name
value corresponding to each of those id
value, which you get from a view.
You could extract all the IDs using an XMLTable, join those to the view, and reconstruct the final XML via XMLAgg, something like:
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
into out_xml
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
As a demo in plain SQL starting from your string, and with a CTE to represent your view:
-- CTE to represent your view
with your_view (id, name) as (
select 123456, 'TEST' from dual
union all
select 1234567, 'TEST1' from dual
)
-- actual query
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
from xmltable(
'/logs/log'
passing xmltype('<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>')
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
XMLELEMENT("LOGS",XMLAGG(XMLELEMENT("LOG",XMLFOREST(V.ID,V.NAME)))).GETCLOBVAL()
-----------------------------------------------------------------------------------------------------
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
or serialized to prettify it:
-- CTE to represent your view
with your_view (id, name) as (
select 123456, 'TEST' from dual
union all
select 1234567, 'TEST1' from dual
)
-- actual query
select xmlserialize(document
xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name))))
indent) as out_xml
from xmltable(
'/logs/log'
passing xmltype('<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>')
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
OUT_XML
--------------------------------------------------------------------------------
<logs>
<log>
<ID>123456</ID>
<NAME>TEST</NAME>
</log>
<log>
<ID>1234567</ID>
<NAME>TEST1</NAME>
</log>
</logs>
If you really wanted to use the mechanism shown in the question you linked to then you could build up the final output CLOB by appending each element generated inside the loop, with the start and end logs
tag added outside the loop:
declare
in_xml clob := '<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>';
out_xml clob;
v_output clob;
begin
v_output := '<logs>';
for r in (
select id
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
)
)
loop
select xmlelement("log", xmlforest (id, name)).getclobval()
into out_xml
from your_view where id = r.id;
v_output := v_output || out_xml;
end loop;
v_output := v_output || '</logs>';
dbms_output.put_line (v_output);
end;
/
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
PL/SQL procedure successfully completed.
But it's simpler to avoid the cursor loop and just join once:
declare
in_xml clob := '<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>';
v_output clob;
begin
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
into v_output
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
dbms_output.put_line (v_output);
end;
/
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
PL/SQL procedure successfully completed.
My Question is related to stackoverflow.com/questions/19763224/…
– afeef
Nov 12 '18 at 12:24
in input i were getting id in xml then i need to fetch records on for example where id (1233,12344) i have shown sample xml [id,name] in xml but there few more .
– afeef
Nov 12 '18 at 12:26
@afeef - well, only if you want to make your code more complicated and probably slower than it needs to be; you don't need to use loops or seperate queries against the view...
– Alex Poole
Nov 12 '18 at 12:26
@afeef - if there are more elements that you need to include and they all come from the same view, then just add those column names to thexmlforest()
call?
– Alex Poole
Nov 12 '18 at 12:27
Many Thanks for the help i will try solution and let you know.
– afeef
Nov 12 '18 at 12:29
|
show 4 more comments
If I understand what you're trying to do, you're starting from an string version of an XML document like:
<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>
and trying to add the name
value corresponding to each of those id
value, which you get from a view.
You could extract all the IDs using an XMLTable, join those to the view, and reconstruct the final XML via XMLAgg, something like:
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
into out_xml
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
As a demo in plain SQL starting from your string, and with a CTE to represent your view:
-- CTE to represent your view
with your_view (id, name) as (
select 123456, 'TEST' from dual
union all
select 1234567, 'TEST1' from dual
)
-- actual query
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
from xmltable(
'/logs/log'
passing xmltype('<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>')
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
XMLELEMENT("LOGS",XMLAGG(XMLELEMENT("LOG",XMLFOREST(V.ID,V.NAME)))).GETCLOBVAL()
-----------------------------------------------------------------------------------------------------
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
or serialized to prettify it:
-- CTE to represent your view
with your_view (id, name) as (
select 123456, 'TEST' from dual
union all
select 1234567, 'TEST1' from dual
)
-- actual query
select xmlserialize(document
xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name))))
indent) as out_xml
from xmltable(
'/logs/log'
passing xmltype('<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>')
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
OUT_XML
--------------------------------------------------------------------------------
<logs>
<log>
<ID>123456</ID>
<NAME>TEST</NAME>
</log>
<log>
<ID>1234567</ID>
<NAME>TEST1</NAME>
</log>
</logs>
If you really wanted to use the mechanism shown in the question you linked to then you could build up the final output CLOB by appending each element generated inside the loop, with the start and end logs
tag added outside the loop:
declare
in_xml clob := '<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>';
out_xml clob;
v_output clob;
begin
v_output := '<logs>';
for r in (
select id
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
)
)
loop
select xmlelement("log", xmlforest (id, name)).getclobval()
into out_xml
from your_view where id = r.id;
v_output := v_output || out_xml;
end loop;
v_output := v_output || '</logs>';
dbms_output.put_line (v_output);
end;
/
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
PL/SQL procedure successfully completed.
But it's simpler to avoid the cursor loop and just join once:
declare
in_xml clob := '<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>';
v_output clob;
begin
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
into v_output
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
dbms_output.put_line (v_output);
end;
/
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
PL/SQL procedure successfully completed.
If I understand what you're trying to do, you're starting from an string version of an XML document like:
<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>
and trying to add the name
value corresponding to each of those id
value, which you get from a view.
You could extract all the IDs using an XMLTable, join those to the view, and reconstruct the final XML via XMLAgg, something like:
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
into out_xml
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
As a demo in plain SQL starting from your string, and with a CTE to represent your view:
-- CTE to represent your view
with your_view (id, name) as (
select 123456, 'TEST' from dual
union all
select 1234567, 'TEST1' from dual
)
-- actual query
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
from xmltable(
'/logs/log'
passing xmltype('<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>')
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
XMLELEMENT("LOGS",XMLAGG(XMLELEMENT("LOG",XMLFOREST(V.ID,V.NAME)))).GETCLOBVAL()
-----------------------------------------------------------------------------------------------------
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
or serialized to prettify it:
-- CTE to represent your view
with your_view (id, name) as (
select 123456, 'TEST' from dual
union all
select 1234567, 'TEST1' from dual
)
-- actual query
select xmlserialize(document
xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name))))
indent) as out_xml
from xmltable(
'/logs/log'
passing xmltype('<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>')
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
OUT_XML
--------------------------------------------------------------------------------
<logs>
<log>
<ID>123456</ID>
<NAME>TEST</NAME>
</log>
<log>
<ID>1234567</ID>
<NAME>TEST1</NAME>
</log>
</logs>
If you really wanted to use the mechanism shown in the question you linked to then you could build up the final output CLOB by appending each element generated inside the loop, with the start and end logs
tag added outside the loop:
declare
in_xml clob := '<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>';
out_xml clob;
v_output clob;
begin
v_output := '<logs>';
for r in (
select id
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
)
)
loop
select xmlelement("log", xmlforest (id, name)).getclobval()
into out_xml
from your_view where id = r.id;
v_output := v_output || out_xml;
end loop;
v_output := v_output || '</logs>';
dbms_output.put_line (v_output);
end;
/
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
PL/SQL procedure successfully completed.
But it's simpler to avoid the cursor loop and just join once:
declare
in_xml clob := '<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>';
v_output clob;
begin
select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()
into v_output
from xmltable(
'/logs/log'
passing xmltype(in_xml)
columns id number path 'ID'
) x
join your_view v on v.id = x.id;
dbms_output.put_line (v_output);
end;
/
<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>
PL/SQL procedure successfully completed.
edited Nov 12 '18 at 12:49
answered Nov 12 '18 at 12:21
Alex PooleAlex Poole
130k6101176
130k6101176
My Question is related to stackoverflow.com/questions/19763224/…
– afeef
Nov 12 '18 at 12:24
in input i were getting id in xml then i need to fetch records on for example where id (1233,12344) i have shown sample xml [id,name] in xml but there few more .
– afeef
Nov 12 '18 at 12:26
@afeef - well, only if you want to make your code more complicated and probably slower than it needs to be; you don't need to use loops or seperate queries against the view...
– Alex Poole
Nov 12 '18 at 12:26
@afeef - if there are more elements that you need to include and they all come from the same view, then just add those column names to thexmlforest()
call?
– Alex Poole
Nov 12 '18 at 12:27
Many Thanks for the help i will try solution and let you know.
– afeef
Nov 12 '18 at 12:29
|
show 4 more comments
My Question is related to stackoverflow.com/questions/19763224/…
– afeef
Nov 12 '18 at 12:24
in input i were getting id in xml then i need to fetch records on for example where id (1233,12344) i have shown sample xml [id,name] in xml but there few more .
– afeef
Nov 12 '18 at 12:26
@afeef - well, only if you want to make your code more complicated and probably slower than it needs to be; you don't need to use loops or seperate queries against the view...
– Alex Poole
Nov 12 '18 at 12:26
@afeef - if there are more elements that you need to include and they all come from the same view, then just add those column names to thexmlforest()
call?
– Alex Poole
Nov 12 '18 at 12:27
Many Thanks for the help i will try solution and let you know.
– afeef
Nov 12 '18 at 12:29
My Question is related to stackoverflow.com/questions/19763224/…
– afeef
Nov 12 '18 at 12:24
My Question is related to stackoverflow.com/questions/19763224/…
– afeef
Nov 12 '18 at 12:24
in input i were getting id in xml then i need to fetch records on for example where id (1233,12344) i have shown sample xml [id,name] in xml but there few more .
– afeef
Nov 12 '18 at 12:26
in input i were getting id in xml then i need to fetch records on for example where id (1233,12344) i have shown sample xml [id,name] in xml but there few more .
– afeef
Nov 12 '18 at 12:26
@afeef - well, only if you want to make your code more complicated and probably slower than it needs to be; you don't need to use loops or seperate queries against the view...
– Alex Poole
Nov 12 '18 at 12:26
@afeef - well, only if you want to make your code more complicated and probably slower than it needs to be; you don't need to use loops or seperate queries against the view...
– Alex Poole
Nov 12 '18 at 12:26
@afeef - if there are more elements that you need to include and they all come from the same view, then just add those column names to the
xmlforest()
call?– Alex Poole
Nov 12 '18 at 12:27
@afeef - if there are more elements that you need to include and they all come from the same view, then just add those column names to the
xmlforest()
call?– Alex Poole
Nov 12 '18 at 12:27
Many Thanks for the help i will try solution and let you know.
– afeef
Nov 12 '18 at 12:29
Many Thanks for the help i will try solution and let you know.
– afeef
Nov 12 '18 at 12:29
|
show 4 more comments
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.
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%2f53261098%2fhow-to-solve-complex-xmlelement-in-oracle%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
The first 'XML' isn't valid and can't be treated as an XMLType, so it's slightly unclear what you are showing us - if that is your current output then what is the input (
in_xml
)? Or where do the ID and name values come from? And what isview
? What iscursorname
?– Alex Poole
Nov 12 '18 at 12:01
do no bother about xml validation please can give solution
– afeef
Nov 12 '18 at 12:06
I can't give you a solution if I don't understand your problem, the initial data and what you're trying to do.
– Alex Poole
Nov 12 '18 at 12:07
in_xml <logs> <log> <ID>123456</ID> </log> <log> <ID>1234567</ID> </log> </logs>
– afeef
Nov 12 '18 at 12:07
im trying to get details by accessing id and using cursor fetch data by id
– afeef
Nov 12 '18 at 12:08