CODEWITHSUNDEEP
asp.netsqlxmldatabasecoldfusion
Noe
Noe

Reputation: 41

ASPX SQL Query Result To XML

I'm new to asp.net, what I am trying to do is get SQL query results and output them in a XML string. I have done this on ColdFusion but I can't figure it out in asp. Any help is very much appreciated.

Thanks

ASP Code:

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
   <title></title>    
</head>
<body>

<form id="form1" runat="server">
<div>    
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DuplicationConnectionString %>" 

        SelectCommand="SELECT [occ], [date], [time], [field], [good] FROM [tbl_Data] WHERE ([occ] = @occ)">
        <SelectParameters>
            <asp:Parameter DefaultValue="AAA" Name="occ" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>    
</div>
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" 
    AutoGenerateColumns="False">
    <Columns>
        <asp:BoundField DataField="ocd" HeaderText="occ" SortExpression="occ" />
        <asp:BoundField DataField="date" HeaderText="date" SortExpression="date" />
        <asp:BoundField DataField="time" HeaderText="time" SortExpression="time" />
        <asp:BoundField DataField="field" HeaderText="field" SortExpression="field" />
        <asp:BoundField DataField="good" HeaderText="good" SortExpression="good" />
    </Columns>
</asp:GridView>
<asp:Button ID="btnSubmit" runat="server" Text="Button" />
</form>

ColdFusion Code:

<cfquery name="getData" datasource="Data_01" username="" password="">
    SELECT *
    FROM tbl_data       
</cfquery>

<cfxml variable="mydoc">
   <?xml version='1.0' encoding='utf-8'?><result><rows>
    <cfoutput query="getData">
        <row>                   
        <cell>#occ#</cell>
        <cell>#date#</cell>
        <cell>#time#</cell>
        <cell>#field#</cell>
        <cfif #good# is 0>
            <cell>No</cell>
        <cfelse>
            <cell>Yes</cell>
        </cfif>
        </row>
    </cfoutput>
       </rows></result>
</cfxml>

Upvotes: 0

Views: 197

Answers (1)

Jason Nesbitt
Jason Nesbitt

Reputation: 740

It's easiest to do this in the SQL with FOR XML RAW.

SELECT [occ], [date], [time], [field], [good] 
FROM [tbl_Data] 
WHERE ([occ] = @occ) 
FOR XML RAW

See this article for more detail.

Upvotes: 2

Related Questions