SQL to XML:How to Use SQLXML to Retrieve Data from SQL Server Database?

Using SQL Server 2000 and earlier versions can retrieve data from SQL Server in XML format directly from the database.Many XML related features were incorporated into the SQL Server sql to xml enables the development of XML applications very easy permits and enables the exchange of data via Internet easily .sql to xml 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 SQL Server in XML format. Example syntax is given below :

SELECT ... FOR XML mode [ , XMLDATA ] [, ELEMENTS ] [ , BINARY BASE64 ]

There are different ways and taking values

· AUTO - In this mode ,sql to xml each row of the query result is an element in the XML document returned.

· EXPRESS - This mode defines how to map the columns in the results returned by the query for XML elements and attributes .

· RAW - All fields are identified as a result of attributes of an element .

· NEST - sql to xml The result of this mode is the same as the AUTO mode,sql to xml but the format needs to be done on the client side .

A simple example of using the above syntax is :

SELECT TOP 3 *

FROM ( SELECT name + '' + NAME as Name FROM Employees )

FOR XML AUTO

The query produces this result :

Employees Name = " Jean-Pierre"

Employees Name = " Andrew Dixon"

Employees Name = " Tom Hanks "

There are optional parameters in the FOR XML clause mode.sql to xml The XMLDATA option adds an XSD schema that describes the XML format of the returned result. Options elements make table fields of data are returned as child elements database .sql to xml If the option is not there ELEMENTS tables fields are returned as attributes instead of elements in the XML document the child. The ELEMENTS option is allowed only in the AUTO mode.sql to xml The BINARY BASE64 option is used to retrieve binary data .sql to xml The binary data is returned back to BASE64 format. Binary data can be recovered in RAW or explicit mode.

An example of the RAW mode is as follows:

SELECT EmpName ,sql to xml EmpCity EMP for XML RAW

The above code returns the result as

row EmpName = " John" EmpCity = "New York"

In the above result ,sql to xml we can see that the fields in the result set are returned as attributes of the element.

. Net provides managed to interact with SQL Server classes. . SQLXML managed classes provided by Net are :

· SqlXmlCommand -sql to xml It is used for queries that are stored in the XML documents are also called XML models. This class also provides support for XML processing on the client.

· SqlXmlParameter -sql to xml it is used to provide parameter values ​​and is used in conjunction with the object SqlXmlCommand .

· SqlXmlAdapter - this is the kind of adapter for SQLXML provider . It is used to complete the data set .

· SqlXmlException - it is used to control the error SQL server.

An example of code using the managed classes is given .sql to xml For more details on the use of courses above can refer to the MSDN documentation.

static string cstr = " Provider = SQLOLEDB ; server = (local ) database = EmpPersonal ; "

testingSqlXml public static void ()

{

Current Ostr Console.Console.OpenStandardOutput = ();

Sqlcmd = new SqlXmlCommand SqlXmlCommand (CSTR ) ;

sqlcmd.Root = "sql to xml employees " ;

sqlcmd.CommandType = SqlXmlCommandType.Sql ;

sqlcmd.CommandText = "SELECT EmpName ,sql to xml EmpCity FROM Employees FOR XML AUTO" ;

strm = sqlcmd.ExecuteToStream ( Ostr ) ;

oStr.Close ( ) ;

}

Thus, we have seen that you can use SQLXML to retrieve data directly from SQL Server in XML format. It is easy to use and transmit data via the Internet ,sql to xml be it in the XML format , which can be achieved using the SQLXML .