Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-05-11
7
Medium Priority
?
251 Views
Last Modified: 2012-05-11
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>
0
Comment
Question by:daz1234
  • 4
  • 3
7 Comments
 

Author Comment

by:daz1234
ID: 35737205
want results to show like this

<balance>
      <numeric>13.12</numeric>
      <currency>CHF</currency>
      <formatted>CHF 13.12</formatted>
</balance>
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35737481
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
 

Author Comment

by:daz1234
ID: 35737510
oh it should be select credit, currency from [users]
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35737756
Which tables do the credit and currency belong to?  Users or Sub?
What are the data types for those same columns?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 35737834
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
 

Author Comment

by:daz1234
ID: 35738197
Does xselect works in SQL server 8
0
 

Author Comment

by:daz1234
ID: 35738369
just ignore last comment
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question