How to Use SQLXML to Retrieve Data from SQL Server Databases?
Posted in WordPress on Jul 16, 2014
Making use of SQL Server 2000 and previously mentioned versions you can retrieve details from SQL Server in XML structure immediately from the database. A lot of XML relevant features were included in the SQL Server database. This allows the development of XML enabled applications pretty simple and it helps make trade of information more than world-wide-web easy. In this post we will go over about how to use SQLXML to retrieve knowledge straight from SQL server database in XML structure.
There are some SQL instructions and clauses that enable you to retrieve information from the SQL Server in the XML format. Syntax for these kinds of is specified underneath:
Find … FOR XML method [, XMLDATA] [, Components] [, BINARY BASE64]
There are different modes and that just take the values of
· AUTO – In this mode each and every row of the result of the query kinds an element in the returned XML document.
· EXPLICIT – This manner defines how to map the columns of the success returned by the question to the XML components and the characteristics.
· RAW – All the fields of the consequence are determined as attributes of an component.
· NESTED – The consequence of this method is the exact as the Car mode but the formatting has to be done at the shopper facet.
A simple illustration of applying the earlier mentioned syntax is supplied down below:
Choose Major 3 *
FROM (Choose FirstName+’ ‘+LastName as Name FROM Workforce)
FOR XML Auto
The query makes this result:
Employees Identify=”John Peter”
Employees Identify=”Andrew Dixon”
Staff Title=”Tom Hanks”
There are optional parameters in the ‘FOR XML mode’ clause. The XMLDATA option adds an XSD schema which describes the XML structure of the final result returned. The Things options make the fields of the tables in the database to be returned as baby elements. If the Components choice is not there the fields of the tables are returned as characteristics in its place of child things of the XML document. The Features choice is allowed only in the Vehicle mode. The BINARY BASE64 alternative is employed to retrieve any binary details. The returned binary knowledge is returned in BASE64 encoded structure. The binary facts can be retrieved in the Uncooked or Explicit mode.
An example of the Raw method is specified beneath:
Choose EmpName, EmpCity from EMP for XML Raw
The over code returns a final result like,
row EmpName=”John” EmpCity=”New York”
In the above end result you can note that the fields of the resultset are returned as characteristics of the factor.
.Net delivers with managed courses for interacting with SQL Server. The managed classes of SQLXML delivered by .Web are:
· SqlXmlCommand – This is used to accomplish queries that are stored in XML paperwork which are also identified as XML templates. This course also presents assist for customer side XML processing.
· SqlXmlParameter – this is made use of to give parameterized values and is utilised along with SqlXmlCommand object.
· SqlXmlAdapter – this is the adapter course for SQLXML company. This is employed to fill the dataset.
· SqlXmlException – this is utilized to lure the mistake facts from the SQL Server.
A sample code employing the managed courses is offered underneath. For detailed facts on the use of the above managed courses you can refer to the MSDN documentation.
static string cstr = “Service provider=SQLOLEDBServer=(local)databases=EmpPersonal”
public static void testingSqlXml ()
Stream oStr = Console.OpenStandardOutput()
SqlXmlCommand sqlcmd = new SqlXmlCommand(cstr)
sqlcmd.Root = “Staff members”
sqlcmd.CommandType = SqlXmlCommandType.Sql
sqlcmd.CommandText = “Decide on EmpName, EmpCity FROM Workers FOR XML Automobile”
strm = sqlcmd.ExecuteToStream(oStr)
As a result we have noticed that SQLXML can be utilised to retrieve data right from the SQL Server in XML format. It is quick to deal with and transmit knowledge through the net if it is in the XML format and this can be conveniently accomplished making use of the SQLXML.
By Balaji B