How to excute a stored procedure to show results in xml format

how to change the result format from a span to xml format

<script runat="server" type="text/C#">
                  void  Page_Load()
                        string accountNo = Request.QueryString["account"];
                        string password = Request.QueryString["password"];
                        string credit = "";

                        string sql = string.Format("SELECT credit FROM [users] s (NOLOCK), [sub] v (nolock) WHERE s.[account] = v.[account] and s.[user] = '{0}' and s.[password] = '{1}'", accountNo, password);

                        using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(test.Configuration.DatabaseConnectionString))

                              using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
                                    // Create command
                                    cmd.CommandText = sql;
                                    cmd.CommandType = System.Data.CommandType.Text;
                                    cmd.Connection = conn;

                                    object resultObj = cmd.ExecuteScalar();

                                    if (resultObj != null)
                                          credit = resultObj.ToString();
                                          balance.Text="Balance: 0";


Who is Participating?
Anthony PerkinsConnect With a Mentor Commented:
In any case it should be something like this (correct the obvious typos xSELECT, xCAST, xINNER):
xSELECT [numeric], 
	balance.currency [currency],
	balance.currency + ' ' + xCAST( AS varchar(20)) [formatted]
FROM	users balance
	xINNER JOIN sub ON balance.account = sub.account

Open in new window

daz1234Author Commented:
want results to show like this

      <formatted>CHF 13.12</formatted>
Anthony PerkinsCommented:
So you want to change the result returned by:
SELECT credit FROM [users] s ...
to instead output an Xml document?

So where is the currency value coming from? Is it hardcoded?
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

daz1234Author Commented:
oh it should be select credit, currency from [users]
Anthony PerkinsCommented:
Which tables do the credit and currency belong to?  Users or Sub?
What are the data types for those same columns?
daz1234Author Commented:
Does xselect works in SQL server 8
daz1234Author Commented:
just ignore last comment
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.