Home » SQL & PL/SQL » SQL & PL/SQL » XML Create logic
XML Create logic [message #689463] Wed, 10 January 2024 01:39 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
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 Go to previous messageGo to next message
mathguy
Messages: 108
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 Go to previous message
mathguy
Messages: 108
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?
Previous Topic: Grants of System Privileges Thru Stored Procedure
Next Topic: Need help to build a query
Goto Forum:
  


Current Time: Thu May 23 11:44:59 CDT 2024