This question is specific to Oracle database. I already know how to achieve this using SQL Server but with sql server I use the Select FOR XML AUTO command but for some reason that won't work with oracle. Then I tried another method shown below with oracle. I am close to getting what I need.
This example is using the HR database that comes with Oracle 10g express edition.
This example uses the EMPLOYEES table in the HR database.
I'm using ASP.net with C# to get data from 3 columns in my oracle table. I want the data from those 3 columns to be output to the browser as xml. I can get the xml to the browser with the following script but it's not the correct format I need.
I called the following script Employees.aspx
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Configur
ation" %>
<%@ Import Namespace="System.Data.Ora
cleClient"
%>
<script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
string connStr = "Data Source=127.0.0.1;User ID=HR;Password=HR;Unicode=
True";
using (OracleConnection conn = new OracleConnection(connStr))
{
OracleCommand command = new OracleCommand("select FIRST_NAME, LAST_NAME, EMAIL from
EMPLOYEES", conn);
conn.Open();
DataSet ds = new DataSet();
ds.DataSetName = "EMPLOYEES";
ds.Load(command.ExecuteRea
der(), LoadOption.OverwriteChange
s, "EMPLOYEE");
Response.ContentType = "text/xml";
ds.WriteXml(Response.Outpu
tStream);
}
}
</script>
The above scripts outputs xml to the browser that looks like this:
<EMPLOYEES>
<EMPLOYEE>
<FIRST_NAME>Steven</FIRST_
NAME>
<LAST_NAME>King</LAST_NAME
>
<EMAIL>SKING</EMAIL>
</EMPLOYEE>
<EMPLOYEE>
<FIRST_NAME>Neena</FIRST_N
AME>
<LAST_NAME>Kochhar</LAST_N
AME>
<EMAIL>NKOCHHAR</EMAIL>
</EMPLOYEE>
<EMPLOYEE>
<FIRST_NAME>Lex</FIRST_NAM
E>
<LAST_NAME>De Haan</LAST_NAME>
<EMAIL>LDEHAAN</EMAIL>
</EMPLOYEE>
<EMPLOYEE>
<FIRST_NAME>Alexander</FIR
ST_NAME>
<LAST_NAME>Hunold</LAST_NA
ME>
<EMAIL>AHUNOLD</EMAIL>
</EMPLOYEE>
</EMPLOYEES>
But I need it to output the xml with the table columns as attributes, so I need it to look like this.
Can anyone help with getting by xml from oracle to output to the browser to look like this:
<?xml version="1.0" encoding="utf-8" ?>
<EMPLOYEES>
<EMPLOYEE FIRST_NAME="Steven" LAST_NAME="King" EMAIL="SKING" />
<EMPLOYEE FIRST_NAME="Neena" LAST_NAME="Kochhar" EMAIL="NKOCHHAR" />
<EMPLOYEE FIRST_NAME="Lex" LAST_NAME="De Haan" EMAIL="LDEHAAN" />
<EMPLOYEE FIRST_NAME="Alexander" LAST_NAME="Hunold" EMAIL="AHUNOLD" />
</EMPLOYEES>