We're currently storing some data in a XMLType column. For the originally intended purpose, we had access to a dynamic SQL statement, via a refcursor and so we've been able to use DBMS_SQL to retrieve column names from those SQL statements. Then we convert that into XML and store it in the XMLType column.
Now we're considering letting other processes use this code, but they'll be working off of a different mechanism which generates their own SQL statements. But we were looking for a generic mechanism to build the same XML data from, best case scenario, a ROWTYPE variable. But everything I've read tells me I at least need a SQL statement (refcursor included) in order to have access to the elements on the row. And I'm trying to steer clear of passing in the SQL statement, since some of this calling code is working off of cursor for loops.
We're trying to ultimately build something like:
create function BUILD_XML (p_generic_rowtype SYSROWTYPE) return varchar2
as
for each column in p_generic_rowtype
loop
build_another_line_of_xml_code;
end loop;
return xml;
end;
We know the myriad of calling programs will have a ROWTYPE variable available, but we won't know anything about it. I
've also thought about building something where a name-value paired collection is passed in, but I still think that's going to require something more manual on the calling program's end. Mostly I'm just trying to see if there's a concise way to get this working, or if we would need to either pass in a SQL stmt or customize the calling code every time.
--=Chuck