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))
                        {
                              conn.Open();

                              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=string.Format("Balance:{0}",credit);
                                    }
                                    else{
                                          balance.Text="Balance: 0";
                                          }
                              }

                              conn.Close();
                        }

                  }
</script>
daz1234Asked:
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	balance.credit [numeric], 
	balance.currency [currency],
	balance.currency + ' ' + xCAST(balance.credit AS varchar(20)) [formatted]
FROM	users balance
	xINNER JOIN sub ON balance.account = sub.account
FOR XML AUTO, ELEMENTS

Open in new window

0
 
daz1234Author Commented:
want results to show like this

<balance>
      <numeric>13.12</numeric>
      <currency>CHF</currency>
      <formatted>CHF 13.12</formatted>
</balance>
0
 
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?
0
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]
0
 
Anthony PerkinsCommented:
Which tables do the credit and currency belong to?  Users or Sub?
What are the data types for those same columns?
0
 
daz1234Author Commented:
Does xselect works in SQL server 8
0
 
daz1234Author Commented:
just ignore last comment
0
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.