WebProWorld Dev Forum |
Hiding
file part of URLs for security purposes?
I went on course last year and we were shown how to set up a simple login box
with a password which redirected the approved user to an appropriate page...
Javascript
code needed to show hidden table....? HELP :)
I need a little snipet of code so that I can type a sentence and at the end of
the sentence it will have a hyperlinked word, "answer" - when you click on that,
a tiny table opens up and the answer is there...
formmail
for windows server
I'm desperately in need of a formmail script which collects the info from a form
sends it to an e-mail address and a confirmation e-mail is sent to the customer,
but this must be for...
|
|
 |
Recent
Articles |
Performance
Tuning of a Daffodil DB / One$DB -JDBC Application
This article illustrates the best practices to improve the performance of Daffodil
DB / One$DB JDBC Driver. This article focuses on how to improve the performance
of a Daffodil DB / One$DB JDBC application using Statemen...
Training
Underway for Microsoft SQL Server 2005
Microsoft SQL Server 2005 is due to be released later this year, and training
has already begun for IT professionals in preparation for it. Microsoft SQL Server
2005, which was previously known...
How
to Save an Image in a SQL Server Database?
Most web applications have a lot of images used in them. These images are usually
stored in a web server folder and they are accessed by giving the relative path
to the file with respect to the root folder of the website.
Synchronize
multiple MySQL Databases with PHP
This article could be very usefull for the owers of web service businesses. If
you are offering e-commerce, hosting, live support or ticketing web based services
you have...
Indexes:
An Overview and Maintenance for Performance
Many people know the importance of creating indexes on SQL Server database tables.
Indexes greatly improve the performance of a database...
Change
Password for SQL Server User
Most security experts' recommendations include changing passwords frequently to
enhance security. It is easy to change the password on a SQL Server Username via
Query Analyzer...
|
|
|
09.20.05
How
To Use SQLXML To Retrieve Data From SQL Server Database?
 By
Balaji B
Using SQL Server 2000 and above versions you can retrieve data from SQL Server
in XML format directly from the database.
Many XML related features were incorporated in the SQL Server database. This enables
the development of XML enabled applications very easy and it makes the exchange
of data over the Internet easy. In this article we will discuss how to use SQLXML
to retrieve data directly from the SQL server database in XML format.
There are some SQL commands and clauses that help you to retrieve data from the
SQL Server in the XML format. Syntax for such is given below:
SELECT
... FOR XML mode [, XMLDATA] [, ELEMENTS] [, BINARY BASE64]
Write
10,000 lines of code in 10 minutes!
Iron Speed Designer – Free
Evaluation |
|
There are different modes that take the values of
· AUTO - In this mode
every row of the result of the query forms an element in the returned XML document.
· EXPLICIT - This mode defines how to map the columns of the results
returned by the query to the XML elements and the attributes.
· RAW
- All the fields of the result are identified as attributes of an element.
· NESTED - The result of this mode is the same as the AUTO mode but the formatting
has to be done at the client side.
A simple example using the above syntax is given below:
SELECT
TOP 3 * FROM (SELECT FirstName+' '+LastName as Name FROM Employees) FOR XML AUTO
The query produces this result:
Employees
Name="John Peter" Employees Name="Andrew Dixon" Employees Name="Tom Hanks"
There are optional parameters in the 'FOR XML mode' clause. The XMLDATA option
adds an XSD schema which describes the XML format of the result returned. The
ELEMENTS options make the fields of the tables in the database return as child
elements. If the ELEMENTS option is not there the fields of the tables are returned
as attributes instead of child elements of the XML document. The ELEMENTS option
is allowed only in the AUTO mode. The BINARY BASE64 option is used to retrieve
any binary data. The returned binary data is returned in BASE64 encoded format.
The binary data can be retrieved in the RAW or EXPLICIT mode.
An example of the RAW mode is given below:
SELECT
EmpName, EmpCity from EMP for XML RAW
The above code returns a result like,
row
EmpName="John" EmpCity="New York"
In the above result you can note that the fields of the resultset are returned
as attributes of the element.
.Net provides with managed classes for interacting with SQL Server. The managed
classes of SQLXML provided by .Net are:
· SqlXmlCommand - This is used
to perform queries that are stored in XML documents which are also called XML
templates. This class also provides support for client side XML processing.
· SqlXmlParameter - this is used to provide parameterized values and is used
along with SqlXmlCommand object.
· SqlXmlAdapter - this is the adapter
class for SQLXML provider. This is used to fill the dataset.
· SqlXmlException - this is used to trap the error information from the SQL Server.
A sample code using the managed classes is given below. For detailed information
on the usage of the above managed classes you can refer to the MSDN documentation.
static
string cstr = "Provider=SQLOLEDB;Server=(local);database=EmpPersonal;"; public
static void testingSqlXml () { Stream oStr = Console.OpenStandardOutput(); SqlXmlCommand
sqlcmd = new SqlXmlCommand(cstr); sqlcmd.Root = "Employees"; sqlcmd.CommandType
= SqlXmlCommandType.Sql; sqlcmd.CommandText = "SELECT EmpName, EmpCity FROM Employees
FOR XML AUTO"; strm = sqlcmd.ExecuteToStream(oStr); oStr.Close(); }
Thus we have seen that SQLXML can be used to retrieve data directly from the SQL
Server in XML format. It is easy to handle and transmit data through the internet
if it is in the XML format and this can be easily achieved using the SQLXML.
About the Author:
Want to stay current with the latest technology developments related to .NET?
Visit the ASP.NET
Web Server Controls web page to get your FREE subscription now! |
