Click here to Skip to main content
15,390,899 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Need help coding for multiple xml nodes in one sql query in PL/SQL

I have a table with all the values that need to be included in the Select, but I can't get multiple Nodes to work in my SQL statement.

Here is my code, If anyone can give me suggestions. I would appreciate it.
This is code that is in a Sql Script.


select XMLSerialize(Content
XMLElement("Events",
XMLAgg(
XMLElement("Event",
XMLElement("id", dudeevnt_crn_id),
XMLElement("name", dudeevnt_name),
XMLElement("summary", dudeevnt_summary),
XMLElement("description", dudeevnt_description),
XMLElement("keywords", dudeevnt_keywords),
XMLElement("organization", dudeevnt_organization),
XMLElement("local-start-date-time", To_char(dudeevnt_start_date_time,'dd-Mon-YY hh24:mi:ss')),
XMLElement("local-end-date-time", To_char(dudeevnt_end_date_time, 'dd-Mon-YY hh24:mi:ss')),
XMLElement("private", dudeevnt_privite),
XMLElement("status", dudeevnt_status),
XMLElement("contact",
XMLElement("name", dudeevnt_contact_name),
XMLElement("phone", dudeevnt_contact_phone),
XMLElement("email", dudeevnt_contact_email),
XMLElement("last-modified-on", to_char(dudeevnt_last_modified_on, 'dd-Mon-YY hh24:mi:ss')),
XMLElement("last-modified-by", dudeevnt_last_modified_by)),
XMLElement("category",
XMLElement("category name", dudeevnt_category_name),
XMLElement("global", dudeevnt_global)),
XMLElement("locations",
XMLElement("location",
XMLElement("name", dudeevnt_facility_name),
XMLElement("global", dudeevnt_global)
))
)
)
) as clob indent size=4
) AS "RESULT"
from clsccusr.dudeevnt
ORDER BY 1 asc;

What I have tried:

I have tried multiple ways to the xmlelement statement I keep getting errors

ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00210: expected '=' instead of '>'
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.
Posted
Updated 4-Feb-22 9:49am

1 solution

Try the following query:
SELECT XMLSERIALIZE(
CONTENT XMLELEMENT(
NAME "Events", XMLATTRIBUTES
(
    '' AS "Event Name"
),
XMLAGG
(
    XMLELEMENT('' "Event"),
    XMLElement("id", dudeevnt_crn_id),
	XMLElement("name", dudeevnt_name)
	-- Other Attributes
)
)
as clob indent size=4
) AS "RESULT"
from clsccusr.dudeevnt
ORDER BY 1 asc;
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900