Home » SQL & PL/SQL » SQL & PL/SQL » XML Create logic
| XML Create logic [message #689463] |
Wed, 10 January 2024 01:39  |
 |
vips2988
Messages: 1
Registered: January 2024
|
Junior Member |
|
|
Hi All,
I am facing some issue to create logic in the format of following xml output from my table data
<Data>
<Details source="A">
<pack>EMP1</pack>
<ref>textxml</ref>
<dept>9988335</dept>
<MAIN code="TASKCREATE">
<StartPosition>SECTION123</StartPosition>
<EndPosition>SECTION123</EndPosition>
<Reference Code="Action">CREATE</Reference>
<Reference Code="Select For">Mumbai</Reference>
<Reference Code="Location">Andheri</Reference>
<ReferenceNumber Code="Duration">7</ReferenceNumber>
</MAIN>
<MAIN code="TASKCREATE">
<StartPosition>Area1</StartPosition>
<EndPosition>Area1</EndPosition>
<Reference Code="Action">CREATE</Reference>
<Reference Code="Select For">Pune</Reference>
<ReferenceNumber Code="Duration">5</ReferenceNumber>
</MAIN>
</Details>
</Data>
------------------------=========================================================
But I am not able to achieve the output with following query and data so please help me with same
select XMLELEMENT("Data",XMLELEMENT("Details",XMLATTRIBUTES('A' as "sourcesystem"),
XMLAgg(
XMLForest('COMPNAME' as pack, a.fileformat as ref, a.dept as dept
,XMLELEMENT("MAIN",XMLATTRIBUTES('SELECTFOR' as "code")) as test
))))
from (select 'textxml' fileformat,9988335 dept,'SECTION123' startposition,'SECTION123'endposition,'CREATE' action,'MUMBAI'select_for,'ANDHERI' loc,7 Duration
from dual
union ALL
select 'textxml' fileformat,9988335 dept,'Area1' startposition,'Area1'endposition,'CREATE' action,'Pune' select_for,null loc,5 Duration
from dual
);
|
|
|
|
| Re: XML Create logic [message #689464 is a reply to message #689463] |
Wed, 10 January 2024 03:48  |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Here's a query that fits/mimics your output but I'm not sure it will solve your actual issue as you have many constants in your query that I think are not constant in your real case:
SQL> select xmlserialize (
2 content
3 XMLELEMENT("Data",
4 XMLELEMENT("Details",XMLATTRIBUTES('A' as "source"),
5 XMLELEMENT("pack",'COMPNAME'),
6 XMLELEMENT("ref",a.fileformat),
7 XMLELEMENT("dept",a.dept),
8 XMLAgg(
9 XMLELEMENT("MAIN",XMLATTRIBUTES('SELECTFOR' as "code"),
10 XMLELEMENT("StartPosition",a.startposition),
11 XMLELEMENT("EndPosition",a.endposition),
12 XMLELEMENT("Reference",XMLATTRIBUTES('Action' as "Code"),a.action),
13 XMLELEMENT("Reference",XMLATTRIBUTES('Select For' as "Code"),a.select_for),
14 XMLELEMENT("Reference",XMLATTRIBUTES('Location' as "Code"),a.loc),
15 XMLELEMENT("ReferenceNumber",XMLATTRIBUTES('Duration' as "Code"),a.Duration)
16 )
17 )
18 )
19 )
20 indent size=2) result
21 from (select 'textxml' fileformat,9988335 dept,'SECTION123' startposition,
22 'SECTION123'endposition,'CREATE' action,'MUMBAI'select_for,
23 'ANDHERI' loc,7 Duration
24 from dual
25 union ALL
26 select 'textxml' fileformat,9988335 dept,'Area1' startposition,
27 'Area1'endposition,'CREATE' action,'Pune' select_for,
28 null loc,5 Duration
29 from dual
30 ) a
31 group by a.fileformat, a.dept
32 /
RESULT
-------------------------------------------------------------------------------------------------------------
<Data>
<Details source="A">
<pack>COMPNAME</pack>
<ref>textxml</ref>
<dept>9988335</dept>
<MAIN code="SELECTFOR">
<StartPosition>SECTION123</StartPosition>
<EndPosition>SECTION123</EndPosition>
<Reference Code="Action">CREATE</Reference>
<Reference Code="Select For">MUMBAI</Reference>
<Reference Code="Location">ANDHERI</Reference>
<ReferenceNumber Code="Duration">7</ReferenceNumber>
</MAIN>
<MAIN code="SELECTFOR">
<StartPosition>Area1</StartPosition>
<EndPosition>Area1</EndPosition>
<Reference Code="Action">CREATE</Reference>
<Reference Code="Select For">Pune</Reference>
<Reference Code="Location"/>
<ReferenceNumber Code="Duration">5</ReferenceNumber>
</MAIN>
</Details>
</Data>
1 row selected.
And for the moderator bit:
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Format your query, if you don't know how to do it, learn it using SQL Formatter.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
[Updated on: Wed, 10 January 2024 08:47] Report message to a moderator |
|
|
|
| Re: XML Create logic [message #689466 is a reply to message #689464] |
Thu, 11 January 2024 17:54 |
 |
mathguy
Messages: 107
Registered: January 2023
|
Senior Member |
|
|
You need to clarify a number of details.
In your SELECT list you have references like a.dept, a.fileformat... who is a in this context? Obviously you can't get ANYTHING with your query, exactly as shown to us, since this will throw a syntax error right away, and it has nothing to do with XML. Obviously, you meant for a to be an alias for the inline query (to generate testing data), but you forgot to add the alias after the closing parenthesis. Didn't you see this when you tested?
Then: in the code you named the attribute of "Details" as "sourcesystem" - what chance do you think there is the attribute in the output will be called "source"? Similarly, you hard-coded 'COMPNAME' as "pack"; how could that ever become EMP1 in the output?
It feels almost as if you changed the problem too much - perhaps to simplify it for us, perhaps to hide confidential data, or who knows for what other reason. Impossible for us to know. Except for the missing alias in the FROM clause, your query does generate a valid XML document similar in structure to your desired output, but with many differences; for example you seem aware that putting element tags in double-quotes does something (it does: it preserves capitalization), and in some places you do that, but in others you don't, resulting in tag names like PACK, REF and DEPT where you say you need pack, ref, dept. Is that something you need help with? What exactly DO you need help with?
|
|
|
|
| Re: XML Create logic [message #689467 is a reply to message #689466] |
Thu, 11 January 2024 19:20 |
|
mathguy
Messages: 107
Registered: January 2023
|
Senior Member |
|
|
You need to clarify a number of details.
In your SELECT list you have references like a.dept, a.fileformat... who is a in this context? Obviously you can't get ANYTHING with your query, exactly as shown to us, since this will throw a syntax error right away, and it has nothing to do with XML. Obviously, you meant for a to be an alias for the inline query (to generate testing data), but you forgot to add the alias after the closing parenthesis. Didn't you see this when you tested?
Then: in the code you named the attribute of "Details" as "sourcesystem" - what chance do you think there is the attribute in the output will be called "source"? Similarly, you hard-coded 'COMPNAME' as "pack"; how could that ever become EMP1 in the output?
It feels almost as if you changed the problem too much - perhaps to simplify it for us, perhaps to hide confidential data, or who knows for what other reason. Impossible for us to know. Except for the missing alias in the FROM clause, your query does generate a valid XML document similar in structure to your desired output, but with many differences; for example you seem aware that putting element tags in double-quotes does something (it does: it preserves capitalization), and in some places you do that, but in others you don't, resulting in tag names like PACK, REF and DEPT where you say you need pack, ref, dept. Is that something you need help with? What exactly DO you need help with?
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 10:02:44 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
