How to Retrieve XML Data from a Database Table: SQL

At times, you need to retrieve the relational data from a table into the XML format for reporting purposes or to share the data across different applications. This involves extracting data from a table in the form of well-formed XML fragments. You can retrieve the XML data in the following ways:

• Using the FOR XML clause in the SELECT statement
• Using XQuery

Using the FOR XML Clause in the SELECT statement

SQL Server allows you to extract data from relational tables into an XML format by using the SELECT statement with the FOR XML clause. You can use the FOR XML clause to retrieve the XML data by using the following modes:

• RAW
• AUTO
• PATH
• EXPLICIT

Using the RAW Mode

The RAW mode is used to return an XML file with each row representing an XML element. The RAW mode transforms each row in the query result set into an XML element with the element name row. Each column value that is not NULL is mapped to an attribute with the same name as the column name.

The following statements display the details of employees with employee ID as 1 or 2:
SELECT EmployeeID, ContactID, LoginID, Title
FROM HumaneResources.Employee
WHERE EmployeeID=1 OR EmployeeID=2
FOR XML RAW

The preceding query displays the employee details in the following format:

<row EmployeeID=”1” ContactID=”1209” LoginID=”adventure-works/guy1” Title=”Production Technician – WC60” />
<row EmployeeID=”2” ContactID=”1030” LoginID=”adventure-works/kevin0” Title=”Marketing Assistant” />

Using the AUTO Mode

The AUTO mode is used to return query results as nested XML elements. Similar to the RAW mode, each column value that is not NULL is mapped to an attribute that is named after either the column name or the column alias. The element that these attributes belong to is named to the table that they belong to or the table alias that is used in the SELECT statement, as shown in the following query:

SELECT EmployeeID, ContactID, LoginID, Title
FROM HumanResources.Employee Employee
WHERE EmployeeID=1 OR EmployeeID=2
FOR XML AUTO

If the optional ELEMENTS directive is specified in the FOR XML clause, the columns listed in the SELECT clause are mapped to sub-elements, as shown in the following query:

SELECT EmployeeID, ContactID, LoginID, Title
FROM HumanResources.Employee Employee
WHERE EmployeeID=1 OR EmployeeID=2
FOR XML AUTO, ELEMENTS

Using the PATH Mode

The PATH mode is used to return specific values by indicating the column names for which you need to retrieve the data, as shown in the following query:

SELECT EmployeeID “@EmpID”,
FirstName “EmpName/First”,
MiddleName “EmpName/Middle”,
LastName “EmpName/Last”
FROM HumanResources.Employee e JOIN Person.Contact c
AND e.EmployeeID=1
FOR XML PATH

The preceding query displays the output in the following format:
<row EmpID=”1”>
<EmpName>
<First>Guy</First>
<Middle>R</Middle>
<Last>Gilbert</Last>
</Employee>
</row>

In the preceding result set, the EmployeeID column is mapped to the EmpID attribute with @ sign. The FirstName, MiddleName, and LastName columns are mapped as subelements of the EmpName element with the slash mark(/)

You can also use the optional ElementName argument with the PATH mode query to modify the name of the default row element, as shown in the following query:

SELECT EmployeeID “@EmpID”,
FirstName “EmpName/First”,
MiddleName “EmpName/Middle”,
LastName “EmpName/Last”
FROM HumanResources.Employee e JOIN Person.Contact c ON e.ContactID = c.ContactID
AND e.EmployeeID=1
FOR XML PATH (‘Employee’)

Using the EXPLICIT Mode

The EXPLICIT mode is used to return an XML file that obtains the format as specified in the SELECT statement. Separate SELECT statement can be combined with the UNION ALL statement to generate each level/element in the resulting XML output. Each of these SELECT statements requires the first two tags to be called Tag and Parent. The Parent element is used to control the nesting of elements. It contains the tag number of the parent element of the current element. The top-level element in the document should have the Parent value set to 0 or NULL.

For example, the managers of AdventureWorks want to access the information regarding products through their mobile devices. These devices cannot directly connect to the SQL Server, but can read the data provided in the XML format. Therefore, you need to convert the details of the products from the Product table into the XML document. To perform this task, you need to create an XML document with <Product> as the parent tag. The <Product> tag will contain ProductID as an attribute and <ProductName> and <Color> as child elements.

To perform this task, the database developer can create the following query:

SELECT 1 AS Tag,
NULL AS Parent,
ProductID AS [Product!1!ProductID],
Name AS [Product!1!ProductName!element],
Color AS [Product!1!Color!elementxsinil]
FROM Production.Product
FOR XML EXPLICIT

How to Store Typed XML Data in XML Columns: SQL

To store the typed XML data, programmer need to first register the schema associated with the data in the XML schema collection objects in the database. The XML schema collection is an object on the SQL Server that is used to save one or more XML schemas. You can create an XML schema collection object by using the following SQL statement:

CREATE XML SCHEMA COLLECTION <Name> as Expression
Where,

• Name specifies an identifier name with which the SQL Server will identify the schema collection.
• Expression specifies an XML value that contains one or more XML schema documents

For example, the customer details are associated with the following schema:
<?XML version=”1.0” ?>
<xsd:schema targetNamespace=”http//schemas.adventure-
Works.com/Customers” xmlns=”http//shemas.adventure-
Works.com/Customers” elementFormDefault=”qualified”
attributeFormDefault=” unqualified”
xmlns:xsd=”http://www.w3.org/2001/XMLSchema”?>
<xsd:element name =”Customer” type=”xsd:string” />
<xsd:attribute name=”City” type=”xsd:string” />
</xsd:schema>

You can use the following statements to register the preceding schema, named as CustomerSchemaCollection, with the database:

CREATE XML SCHEMA COLLECTION CustomerSchemaCollection AS n’<?xml version= “1.0” ?>
<xsd:schema targetNamespace=”http://schemas.adventure-
Works.com/Customers” xmlns=”http://schemas.adventure-
Works.com/Customers” elementFormDefault=”qualified”
attributeFormDefault=”unqualified”
xmlns:xsd=”http://www.w3.org/2001/XMLSchema”.>
<xsd:element name =”Customer” type=”xsd:string” />
<xsd:attribute name=”Name” type=”xsd:string” />
<xsd:attribute name=”City” type=”xsd:string” />
</xsd:schema>’

You can view information about the registered schemas in a database by querying the sys.XML_schema_collection catalog view, as shown in the following statement:
SELECT * FROM sys.XML_schema_collections

After registering the XML schema, you can use the schemas to validate typed XML values while inserting records into the tables. You need to specify this while creating a table that will store the XML data. In the preceding example, if you need to validate the customer details with the CustomerSchemaCollection schema, you need to create the CustDetails table by using the following statement:

CREATE TABLE CustDetails
(
CustID int,
CustDetails XML
)

You can insert data into this table by using the following statement:
INSERT INTO CustDetails VALUES (2, ‘<?xml version=”1.0”?> <CustomerName=”Abrahim Jones” City=”Selina” />’)

While executing the preceding statement, the SQL Server will validate the values for the CustDetails column against the CustomerSchemaCollection schema.

How to Store XML Data in XML Columns: SQL

At times, Programmer need to store the XML data in its original state in a column of a database table. For example, you need to save the details of customers in the database. The details of individual customers are maintained by a website. The website saves the details of each customer in an XML file. As a database developer, you need to save this data in the SQL Server. For this, you can create the following table to store the customer details:

CREATE TABLE CustDetails ( CUST_ID int, CUST_DETAILS XML )

You can save the following types of data in the columns with the XML data types:
Untyped XML data: is also a well-formed data, but is not associated with a schema. The SQL Server does not validate this data, but ensures that the data being saved with the XML data type is well-formed.

Typed XML data: is a well-formed data that is associated with a schema defining the elements and their attributes. It also specifies a namespace for the data. When you save the typed XML data in a table, the SQL Server validates the data against the schema and assigns the appropriate data type to the data based on the data types defined in the schema. This helps in saving the storage space.
As a database developer, you should know how to store both types of data on the SQL Server.

Staring Untyped XML Data

To store the untyped XML data, you can use columns or variables with the XML data type. For example, to store customer data in the CustDetails table, you can use the following INSERT statement:

INSERT INTO CustDetails VALUES (2, ‘<Customer Name=’Abrahim Jones” City= “Selina” />’)

In the preceding statement, the string value that contains an XML fragment is implicitly converted to XML. However, you can also convert a string value to XML by using the CONVERT or CAST functions. In this example, you can use the following statement to convert the data type of the string value to XML before inserting the record into the table.

INSERT INTO CustDetails VALUES (2, convert (XML, ‘<CustomerName=”Abrahim Jones” City=”Selina” />’) )

Similarly, you can also use the CAST function, as shown in the following statement:

INSERT INTO CustDetails VALUES (4, cast (‘<Customer Name=”Abrahim Jones” City=”Selina” />’ as XML) )

How to Store Data from Rowset: SQL

Programmer can use the rowset created by openxml to store the data, in the same way that you would use any other rowset. You can insert the rowset data into permanent tables in a database. For example, you can insert the data received by a supplier in the XML format into the SalesOrderHeader and SalesOrderDetail tables.

Clearing the Memory

After saving the data permanently in the database, you need to release the memory where you stored the rowset. For this, you can use the sp_xml_removedocument stored procedure.
Consider an example where customers shop online and the order given by the customers are transferred to the supplier in the form of an XML document. Following is the data available in the XML document:

DECLARE @Doc int
DECLARE @XMLDoc nvarchar (1000)
SET @XMLDoc = N’ <ROOT>
<Customer CustomerID=”JH01” ContactName=”John Henriot”>
<Order OrderID=”1001 CustomerID=”JH01”
<OrderDate=”2006-07-04T00:00:00”>
<OrderDetail ProductID=”11” Quantity=”12”/>
<OrderDetail ProductID=”22” Quantity=”10”/>
<Order>
</Customer>
<Customer CustormerID=”SG01” ContactName=”Steve Gonzlez”>
<Order OrderID=”1002” CustomerID=”SG01”
OrderDate=”2006-08-16T00:00:00”>
<OrderDetail ProductID=”32” Quantity=”3”/>
</Order>
</Customer>
</ROOT>’

To view this XML data in a rowset, you need to execute the following statements:

• Create an internal representation of the XML document by executing the following statement:
  EXEC sp_xml_preparedocumnt @Doc OUTPUT, @XMLDoc
• Execute the following query to store the data in a table by using the OPENXML function:
  INSERT INTO CustomerDetails
  SELECT *
  FROM openxml (@Doc, ‘/ROOT/Customer’, 1)
  WITH (CustomerID varchar (10), ContactName varchar (20) )

The data that will be displayed as shown in the following table.
CustomerID ContactName
JH01 John Henriot
SG01 Steve Gonzlez

• Remove the internal tree from the memory by executing the following statement:
  EXEC sp_xml_removedocument @Doc

You can also specify the column pattern to map the rowset columns and the XML attributes and elements. You can use the following OPENXML statement with the preceding statements to specify the column pattern:

SELECT *
FROM openxml (@Doc, ‘/ROOT/Customer/Order/OrderDetail’,1)
WITH (CustomerID varchar (10) ‘../../@CustomerID’,
ContactName vchar (20)’../../@ContactName’, OrderID int ‘../@OrderID’,
OrderDate datetime ‘ ../@OrderDate’, ProdID int ‘@ProductID’, Quality int)

Mapping Browser Request with Routing: Asp.Net MVC

An important feature, Asp.Net MVC Routing, is used for mapping incoming browser requests to particular action written in MVC Controller. By default MVC used default route table that is set up in two places i.e. Web.Config file and Global.asax file.

In application’s Web.Config file, configuration file, there are four sections related to mapping browser requests listed below. If programmer delete these sections, routing will not work for the application.

• system.web.httpModules section
• system.web.httpHandlers section
• system.webserver.modules section
• system.webserver.handlers section

Global.asax file, contains event handlers for application life cycle events, have following lines of code to be used for mapping the requests.

RouteConfig.RegisterRoutes(RouteTable.Routes);
In above line RouteConfig class have a method RegisterRoutes to define the default route table to be used to default mapping. The class having following line of code which may be changed by programmer as per requirements.

public class RouteConfig
{
public static void RegisterRoutes(RouteCollection routes)
{
routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
);
}
}

To register new route, we have to write the like as code written in RouteConfig.cs file with our new route name. The following code will create a new route with name Route1 by using the following code written in RouteConfig.cs file:

routes.MapRoute(
name: "Route1",
url: "Route1/{id}",
defaults: new { controller = "Home", action = "Route1", id = UrlParameter.Optional }
);

Now create an action in Home controller with a view having same name as below:

public ActionResult Route1()
{
return View();
}
Just run the MVC application and write only the action name in the address bar and it will show the view without any error, as shown in following image:

RadioButton List In MVC

How to Declare Classes with Members: JAVA

In order to bring a class into existence in Java program, it should be declared. A class is declared using keyword class. The generic syntax for class declaration in Java is:

Class   <class_name>
{
    Statements defining class come here
}
The angle-brackets < > mean these names are to be provided by the programmer and [ ] brackets mean, this part is optional. The class name must be provided while declaring a class. This is used to refer to the class whenever an instance (the object) of the class is created. The class name must be a legal identifier in Java. While naming classes, generally nouns are used and the first letter is given in uppercase e.g., City or Date etc. the curly brackets { } mark the beginning of a class and/or a method. The curly brackets are also used to specify block statements.

A class is a blueprint or prototype that you can use to create many objects. The implementation of a class is comprised of two components: the class declaration and the class body.

classDeclaration   {
    // classBody
}

When a class is declared, no memory space is allotted to it. This is because a class is simply a blueprint or logical placeholder containing objects and method. Memory space is allocated when objects of a class type are created.

The Class Declaration

The class declaration component declares the name of the class along with other attributes such as whether the class is public or not.

The Class Body

The class body follows the class declaration and is embedded with in curly braces ‘{‘ and ‘}’. The class body contains declaration for all instance variable and class variable (known collectively as member variable) for the class. In addition, the class body contains declarations and implementations for all instance methods and class methods (known collectively as methods) for the class. The following template shows the form of a class definition that is most commonly used; however, it is legal to intermix the method definitions and the instance variable declaration.

Public class class_Name    {
Instance_Variable_Declaration_1
Instance_Variable_Declaration_2
. . . . . .
Instance_Variable_Declaration_Last
Method_Definition_1
Method_Definition_2
     . . . . .
    Method_Definition_Last
}

How to Implement Object Oriented Design in JAVA

The basic unit of object-orientation in Java is the class. The class is often is described as a blueprint for an object. You can think of an object as an entity having a unique identity, characteristics and behaviour. For instance, a ceilingFan is an object : it has a unique identity ; its characteristics are : it has 3 or 4 blades, it has a motor, a colour etc. ; its behaviour is : it rotates the air at some specific speed. Similarly, a Student is an object. Its characteristics are: its rollno, name class, marks etc. Its behaviour is: it takes test, it attends classes etc.

To create an object in Java, you need a class. It allows a programmer to define all of the properties (i.e. characteristics) and methods (i.e. behaviour) that internally define an object, all of the API (Application programming interface) method that externally define an object. Therefore, we can say that a class is the BLUEPRINT of an object. A class define the types of shared characteristics, such as:

• The set of attributes i.e. characteristics through data.
• The set of behaviour i.e. behaviour through method/functions

In its role as a blueprint, the class specifies what an actual object will look like. But it is not an object. Objects are actually instances of classes. You can think of a class as a cookie cutter and an instance as an actual cookie. Similarly, you can think of a class as a blueprint of a house and an instance as an actual house.

Class as Basis of all Computation 

In Java, the class forms, the basis of all computation. Anything that has to exist as a part of a Java program has to exist as a part of class, whether that is a variable or a function or any other code-fragment. Unlike other OOP languages such as C++ that allows the existence of variables and functions outside any class. The reason being that Java is a pure Object Oriented Language. Here all functionality revolves around classes and object, as in real world. Therefore, if you want to use certain variable and functions in Java, you have to make them part of a class. ALL JAVA programs consist of objects (data and behaviour) that “interact” with each other by calling methods. All data is stored within objects which are instances of a class.

See, without classes can be no objects and without objects, no computation can take place in Java. Thus, classes form the basis of all computation in Java.

Defining Classes

A Java program consists of objects, from various classes, interacting with one another. Before we go into the details of how you can define classes, let’s review some of the general properties of classes. A value of class type is called an object. An object is usually referred to as an instance of the class rather than as a value of the class, but it is a value of the class type. An object is a value of the class type much like a value, such as 5, of a primitive type, like int, is a value of a variable of that type. 

However, an object typically has multiple pieces of data and has methods (action) it can take. Each object can have different data but all objects of the class have the same types of data and all objects in a class have the same methods. We generally say that data and methods belong to the object, and that is an acceptable point of view. The data certainly does belong to the object, but since all objects in a class have the same methods, it also would be correct to say that the methods actually belong to the class.

In this section we are going to learn about how to define classes in Java. Now consider the code fragment shown below that defines a class called City.

Public class City {
String name ;    // variable name will be name of the city
Long population ;    // will hold City’s population
Void display( )    {
Label1.setText(“city name :” + name) ;
Label2.setText(“population :” + population) ;
}
}
Let us examine this code line by line. The firstline Public class City Defines the name of the class which is City. The keyword class ensures that it is a class and the keyword public means it is available in entire program. When you add a top-level container frame in your GUI application, then a public class having the frame name is created in your application. In other words, the top-level frame is represented through a public class.

The brace following the public Class City
    { marks the beginning of class’ block.

The next two lines
    String name;
    Long population; declares the data members of the class to define its characteristics.

Each of these lines declares one instance variable name. You can think of and object of the class as a complex item with instance variables inside of it. So, you can think of an instance variable as a smaller variable inside each object of the class. In this case, the instance variables are called name and population.

The copy of instance variables is created for each object of the class. The next four lines
    void display ( )
    {
        Label1.setText(“City name :” + name);
        Label2.setText(“population :” + population);
    }

Define a method of the class which defines the behaviour of the class. The name of the method is display( ). By looking at its code, you can easily make out what its functionality is like. The last line
    }
marks the end of the class’ block.