How to solve complex XMLELEMENT in Oracle












0















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.











share|improve this question

























  • 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











  • 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
















0















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.











share|improve this question

























  • 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











  • 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














0












0








0








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.











share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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 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











  • 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











  • 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












1 Answer
1






active

oldest

votes


















3














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.





share|improve this answer


























  • 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 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











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


















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









3














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.





share|improve this answer


























  • 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 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
















3














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.





share|improve this answer


























  • 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 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














3












3








3







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.





share|improve this answer















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.






share|improve this answer














share|improve this answer



share|improve this answer








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 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



















  • 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 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

















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


















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














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





















































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







Popular posts from this blog

How to change which sound is reproduced for terminal bell?

Title Spacing in Bjornstrup Chapter, Removing Chapter Number From Contents

Can I use Tabulator js library in my java Spring + Thymeleaf project?