sql & xml

[Roberto]

Hi,
i would to create a xml file containing data extract with a sql query, but xml file must respect a specific dtd (and not the generic schema that i obtain whit the export of the grid).
Is the first time that i must export data in a xml and...i'm very confused

i work with oracle 8.1.7 and pl/sql dev 5.1 version

someone has some suggestion in order to help me??

thanks
Roberto

 

[kctx7]

Roberto,

You should be able to use PL/SQL functions like XMLElement, XMLForest, and XMLSequence to formulate your data into a single XML column. PL/SQL Dev can be used to export the data if you want, but its XML capabilities are mostly for extracting and viewing data.

Kerry

 

[Roberto]

Hi,
i've find something on the web around this function (XMLElement, XMLForest, etc...) but with my oracle 8.1.7 i do not succeed to use them...there is a way to see if this function are included? If there aren't, how i can install this function?

Thanks
Roberto

 

[Roberto]

Hi,
with a server test, i've migrate on oracle 9.2 and on my pc client i've installed oracle client 9.2 with all XML option.
Now i've tried to execute a query in this style

select XMLElement("Emp",
XMLAttributes(e.employee_id AS "ID", e.last_name),
XMLElement("Dept",e.dept),
XMLElement("Salary",e.salary)) AS "Emp Element"
from emps e where e.employee_id = 206

Now the query don't return errors, but the result is nothing, the part of sql-windows where appears the result is empty, what's could be the problem???

If i try a simple query like this
select XMLElement ("employee", e.employee)
from employee e
in pl/sql or sql*plus on my PC, there is no result,
if i try this query direcly in the sql*plus on the server the result are correct

???????

Roberto

P.s.: scuse me from my english )))

 

[Marco Kalter]

In PL/SQL Developer 5.1 you will have to apply the getclobval() function:

select XMLElement ("employee", e.employee).getclobval()
from employee e

PL/SQL Developer 6.0 will automatically get the XML text from the XMLTYPE instance. See chapter 6.7 in the User's Guide for more details.