XML DTD – Document Type Definition

To define the valid XML , DTD is being used. The Tutorial provides the details on how to Validate XML using DTD.

What is DTD in XML ?

DTD in XML means Document Type Definition which defines the XML Structure for the XML elements in the XML Document. A well -formed or valid XML is validated against the XML DTD for its correctness.

XML DTD Structure

The need of DTD ( Document Type Definition) is to ensure that the created XML Structure is Valid and can be parsed. Consider the below XML Data for the student which includes “student.dtd”. The DTD file defines the structure for each used element ( firstName, lastName, registered) in the XML Document.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE note SYSTEM "student.dtd">
<student>
    <firstName>Mohit</firstName>
    <lastName>Sharma</lastName>
  	<registered>Yes<registered>
</student>

Another common example of notification 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE note SYSTEM "notification.dtd">
<notification>
 <to>Mohit Sharma</to>
 <from>oracleappshelpadmin</from>
 <subject>New Tutorial on XML DTD </subject>
 <message>We have published the new turotial on XML DTD, XML Document Validation Process</message>
</notification>

XML DTD Structure Definition

The below Student DTD is interpreted as per the below provided definition.

#PCDATA” stands for Parsed Character Data which means that the data element is parsable by XML parser

!DOCTYPE student – depicts the root element of the document which is “student”
!ELEMENT student – depicts that the “Student” element should have the elements ( firstName, lastName, registered)
!ELEMENT firstName – depicts that the element to be of type “#PCDATA”
!ELEMENT lastName – depicts that the element to be of type “#PCDATA”
!ELEMENT registered – depicts that the element to be of type “#PCDATA”

<!DOCTYPE student
[
<!ELEMENT student (firstName,lastName,registered)>
<!ELEMENT firstName (#PCDATA)>
<!ELEMENT lastName (#PCDATA)>
<!ELEMENT registered (#PCDATA)>
]>

We can also provide DTD within the XML Document

<?xml version="1.0" encoding="UTF-8"?>
<!-- XML DTD declaration  -->
<!DOCTYPE student
[
<!ELEMENT student (firstName,lastName,registered)>
<!ELEMENT firstName (#PCDATA)>
<!ELEMENT lastName (#PCDATA)>
<!ELEMENT registered (#PCDATA)>
]>	
<!-- XML DTD declaration  -->
<student>
    <firstName>Mohit</firstName>
    <lastName>Sharma</lastName>
  	<registered>Yes<registered>
</student>

Difference between JSON and XML

The XMl and JSON are the medium for the data exchange among different applications. The tutorial provides the commonalities and comparison between JSON and XML data attributes.

Sample JSON data

{"student":[  
    {"firstname":"Mohit", "lastname":"Sharma"},  
    {"firstname":"Rahul", "lastname":"Jain"},  
	{"firstname":"Pankaj", "lastname":"Verma"}
	]}

Sample XML Data

To understand the XML Basics, please refer the below published blog

XML Overview

<student>
    <firstName>Mohit</firstName>
    <lastName>Sharma</lastName>
    <registered>Yes<registered>
</student>

Commonalities between XML and JSON

  1. JSON and XML Data supports Unicode and thus widely used by multiple technology languages like Java, php,phython,.Net
  2. JSON and XML data supports XMLHttpRequest
  3. JSON and XML data supports parsing
  4. JSON and XML are both human readable language

Comparison between JSON and XML

  1. XML represents data in XML tags whereas JSON does not require tag for data representation
  2. JSON format is more easier and simple to READ & WRITE when compared with XML data
  3. XML does not supports ARRAY Data representation whereas JSON supports array and makes it easier to combine homogeneous data as a common set
  4. XML data parsing is difficult when compared to JSON data
  5. JSON data is short and precise compared to XML. XML document is lengthy, redundant and includes additional XML tags for namespace and other XML validation.
  6. JSON is data -oriented whereas XML is document- oriented
  7. XML data is more secured in comparison to JSON data

Top XML Tutorial for beginners

Introduction

XML (Extensible Markup Language) is derived from Standard Generalized Markup Language (SGML) and is a text based markup language used for exchanging messages among different applications
XMl markup language is independent of platform and technology languages and primarily used to store and transfer data among systems irrespective of their hardware and software compatible.
The Tutorial provides the insight for XML Basics, Advanced XML, and XML tools by providing the relevant XML examples.

What is a Markup Language ?

XML being the markup language provides the certain set of rules for encoding the documents. XML is the Simple Document with representation of application data structured in the used defined self-descriptive XML tags.

Characteristics of XML

  1. XML is developed by World Wide Web Consortium (W3C) and is available as the Open standard.
  2. XML does not provide pre-defined tags like HTML
  3. XMl markup language is independent of platform and software and thus makes it a common and most popular way of interacting and sharing message data.
  4. XML provides the mechanism to store and transport data rather than how data is presented
  5. XML is Extensible as it provides developers to create self-descriptive tags

How XML can be useful ?

  1. XML can be used to store and arrange the data as per the application need
  2. Applications uses XML for exchanging message data among different applications
  3. Style Sheet can be applied to XML data
  4. XML uses the Public Standard and thus supported by many languages ( java,.Net, Php, Python,Perl,etc)
  5. XML document syntax correction can be validated using DTD or XML schema
  6. XML supports Unicode and thus makes it easier to communicate the information
  7. XML being the common standard for data exchange eliminates the data conversion issue with incompatible formats.

XML Prolog

The below mention line should be the first line in the XML and termed as XML Prolog. This depicts the XML version and the encoding used in the XML document.

<?xml version="1.0" encoding="UTF-8"?>

XML Syntax

XML Document is case-sensitive and thus cannot be considered Valid. In the below XML document if the <firstName>Mohit Sharma</FirstName> are not same then XML validation fails. Consider the 2nd tag, where the 1st letter is mentioned as Capital.

XML should have a open and close tag

The XML Document follows the Tree Structure where each element should have and open and close tag. This requires to ensure the correct interpretation of the XML data for each specified tag. 

<student>
    <firstName>Mohit</firstName>
    <lastName>Sharma</lastName>
    <registered>Yes<registered>
</student>

Comments in XML

The bellow syntax allows to provide comments in the XML Document.

<!-- This is just a comment -->

There is another way to comment the XML Tag

<student>
    <firstName>Mohit</firstName>
    <lastName>Sharma</lastName>
   	<!--registered>Yes<registered-->
</student>

Let’s consider the sample student XML Record. XML Records looks like a Tree structure or document where the top most element in the XML Tree is termed as the ROOT element and other element(s) are termed as CHILD element(s) also. The CHILD element could have sib-child elements.

In the below given XML Document, lets categorize the elements

root element– oracleappshelp.

Child element – student, firstName, lastName, Address,registered

Sub-Child element – street, city,state, pincode, country

<?xml version="1.0" encoding="UTF-8"?>
<oracleappshelp>
<student>
    <firstName>Mohit</firstName>
    <lastName>Sharma</lastName>
    <address>
	<street>347 Pitt St </street> 
    <city>Sydney</city>city>
    <state>New South Wales </state>
	<pincode>2000</pincode>
	<country>Australia<country>
	</address>
	<registered>Yes<registered>
</student>
<student>
    <firstName>Rahul</firstName>
    <lastName>Jain</lastName>
    <address>
	<street>243 George St</street>
    <city>Sydney</city>city>
    <state>New South Wales </state>
	<pincode>2000</pincode>
	<country>Australia<country>
	</address>
	<registered>Yes<registered>
</student>
</oracleappshelp>

SQL Server Functions Min() and Max() on table result set

SQL Server provides some SQL Functions to manipulate the table result data. SQL Server Min() Function allows to retrieve the table result set based on the condition of smallest / low value . SQL Server Max() Function allows to retrieve the table result set based on the condition of largest / high value.

The below given are the SQL Server Min() and Max () examples.

SQL Server Min () Syntax:

SELECT MIN(column_name)
FROM table_name
WHERE condition;

SQL Server Max() Syntax:

SELECT MAX(column_name)
FROM table_name
WHERE condition;

UserIdGiveNameMiddleNameFamilyNameAgeStatus
1Mohit Sharma 28New
2Rohit Kumar Jain 34Closed
3Snehashish Gupta 31Pending
4Rajeev Malhotra 34Closed
SELECT MIN(age)
FROM oracleappshelpUsers;
Output :
Age 
28
SELECT MIN(age)
FROM oracleappshelpUsers 
WHERE Status ='Pending';
Output: 
Age 
31
SELECT MAX(age)
FROM oracleappshelpUsers;
Output: 
Age
34
SELECT MAX(age)
FROM oracleappshelpUsers
where status ='New';
Output: 
Age
28

SQL Server SELECT, SELECT TOP , SELECT DISTINCT Queries

SELECT SQL Query is used to retrieved the result data from the SQL Server Database Table stored in the form of rows and columns.

Lets see the different usage of SELECT SQL Query for data retrieval. Consider the below table – oracleappshelpUsers.

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 				NOT NULL IDENTITY PRIMARY KEY,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10)					,
    FamilyName 			VARCHAR(128) 		NOT NULL,
	Age 				INT 				CHECK (Age>=18),
	Status 				VARCHAR(20)			NOT NULL	DEFAULT 'NEW'	
)

Below given is the result set data for the table.

SELECT * 
FROM 
oracleappshelpUsers
UserIdGiveNameMiddleNameFamilyNameAgeStatus
1Mohit Sharma 28New
2Rohit Kumar Jain 34Closed
3Snehashish Gupta 31Pending
4Rajeev Malhotra 34Closed
SELECT UserId, GivenName, FamilyName
FROM 
oracleappshelpUsers
UserIdGivenNameFamilyName
1Mohit Sharma
2Rohit Jain
3Snehashish Gupta
4Rajeev Malhotra

SQL Server Select DISTINCT result data

The use of DISTINCT keyword is applicable when DUPLICATE data rows to be removed. Consider the first example where we have 4 rows and Status -‘Closed’ is repeated twice.

If we need to get the result set from the data table to identify the UNIQUE count of values, then DISTINCT is useful. 

SELECT DISTINCT Status 
FROM 
oracleappshelpUsers
New
Closed
Pending

SELECT COUNT(DISTINCT Status) FROM oracleappshelpUsers;
         OR 
SELECT Count(*) AS DistinctStatus
FROM (SELECT DISTINCT Status FROM oracleappshelpUsers);
Ouput - 3

SQL Server TOP SQL Query

The data retrieval could be required in different forms . We might have a requirement to get the data in the specific limit range. SQL Serer TOP statement could be useful in such cases and limits the number of rows to be returned as the result set.

But this does not depict the order of rows stored in the database thus ORDER BY clause is used with the TOP SQL Statement 

SELECT TOP 10
From 
oracleappshelpUsers
Order By UserId Desc;

SQL Server Auto Increment Column value in the table

We have learnt about the UNIQUE and Primary Key values for the columns in our previous blogs.

SQL Server Not Null Constraint in the table

SQL Server Primary Key Constraint in the table

The Primary Key which is a UNIQUE field value needs to be inserted every time when a record is inserted into the SQL Server table. To initiate the value to be generated automatically , we make the use of AUTO INCREMENT feature provided by SQL Server.

SQL Server Auto Increment Default value . The Automatic generation number will start with the value of 1 with the increment of 1 every time whenever a record is inserted.

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 				NOT NULL IDENTITY PRIMARY KEY,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10)					,
    FamilyName 			VARCHAR(128) 		NOT NULL,
	Age 				INT 				CHECK (Age>=18),
	Status 				VARCHAR(20)			NOT NULL	DEFAULT 'NEW'	
)

SQL Server Auto Increment can be provided with the custom value for start of specified Sequence value and Increment value . The Automatic generation number will start with the value of 1000 with the increment of 1 every time whenever a record is inserted. 

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 				NOT NULL IDENTITY(1000,1) PRIMARY KEY,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10)					,
    FamilyName 			VARCHAR(128) 		NOT NULL,
	Age 				INT 				CHECK (Age>=18),
	Status 				VARCHAR(20)			NOT NULL	DEFAULT 'NEW'
)

SQL Server Create and Drop Index on table

We have gone through the blogs to understand the Creation of Tables , Constraints to be applied, data to be inserted but it is also important to understand how the data retrieval will happen when you have thousands of lakhs of records in the table.

For that we should ensure that the data retrieval should be fast and thus INDEX comes into picture.

INDEXES are the tables columns combined together to let the table use them to fetch the data faster. 

CREATE INDEX index_name
ON table_name (column1, column2, ...);

The below given syntax removes the duplicate values 

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

Let take example of table – oracleappshelpUsers and create Index on the table

 CREATE INDEX indx_uname
 ON oracleappshelpUsers (FamilyName, GivenName);

The SQL Query should be like 

DROP INDEX oracleappshelpUsers.indx_uname;

SQL Server Default Constraint Check

The blog covers the topic on default constraint in SQL Server which can be applied on Table column for validations.

SQL Server Check Constraint

Lets consider we need to put some validation on some columns like records should be inserted

if the Age is >= 18

if City =’New Delhi’

if Status =’New’

The SQL Server Check Constraint allows you to add these condition on the specific column .

CREATE TABLE oracleappshelpUsers
(
    UserId			INT 			NOT NULL UNIQUE,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10)					,
    FamilyName 			VARCHAR(128) 		NOT NULL,
    Age 			INT 			CHECK (Age>=18)				
);

SQL Server Default Constraint

It is sometimes required to setup the DEFAULT value for a particular Column . 

CREATE TABLE oracleappshelpUsers
(
    UserId			INT 			NOT NULL UNIQUE,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10)					,
    FamilyName 			VARCHAR(128) 		NOT NULL,
    Age 			INT 			CHECK (Age>=18),
    Status 			VARCHAR(20)		NOT NULL	DEFAULT 'NEW'	
);

SQL Server Primary Key , Foreign Key Constraints in the table

The SQL Server Table might contain multiple fields but for the data Retrieval we need to consider an UNIQUE key through which the complete record row(s) can be fetched. The SQL Server RDBMS provides the concept of Primary Key (Unique field). It also helps in executing the queries faster.

Refer SQL Server Unique and Not Null Constraints in the previous blog – http://oracleappshelp.com/2020/04/22/sql-server-unique-not-null-constraint-in-table/

SQL Server Primary Key – Points to Consider

  1. Primary Key should have UNIQUE field data
  2. Primary Key CANNOT be NULL
  3. Each Table Contains one Primary Key
  4. Primary Key could be based on single column or combination of multiple columns.
CREATE TABLE oracleappshelpUsers
(
    UserId			INT 			NOT NULL PRIMARY KEY,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10),
    FamilyName 			VARCHAR(128) 		NOT NULL
)

The below example shows creation of PRIMARY KEY which includes column combination of UserId + GivenName

CREATE TABLE oracleappshelpUsers
(
    UserId			INT 			NOT NULL ,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10),
    FamilyName 			VARCHAR(128) 		NOT NULL,
    CONSTRAINT PK_Users PRIMARY KEY (UserId,GivenName)
);

SQL Server Drop Constraint

The SQL Server Primary Key Constraint can be removed using the below SQL Query

ALTER TABLE oracleappshelpUsers
DROP INDEX PK_Users;

SQL Server Foreign Key – Points to Consider

Foreign Key concept is related to the Reference of another table where the reference column is the PRIMARY KEY.

Foreign Key – Points to Consider

  1. Foreign Key should be the Primary Key of the another table
  2. The table which actually contains the Foreign Key column as Primary Key is the Parent Table
  3. Foreign Key applies the linkage between two tables
  4. The table which has reference of Column as Foreign Key is the Child Table or Reference Table

Let’s discuss in detail

We consider another table for storing the students data which also includes the field “UserId” from the table – oracleappshelpUsers

CREATE TABLE oracleappshelpStudents
(
    StudentId 		INT 			IDENTITY PRIMARY KEY,
    UserId 		INT 			FOREIGN KEY REFERENCES oracleappshelpUsers (UserId),
    Email 		VARCHAR(256) ,
    CreatedBy 	        VARCHAR(128) 		NOT NULL,
    UpdatedBy 	        VARCHAR(128) 		NOT NULL,
    CreatedDate	        DATETIME		NOT NULL,
    UpdatedDate		DATETIME		NOT NULL
)

Here in this example UserId is the PRIMARY KEY for the table oracleappshelpUsers but it is being referenced in the New table oracleappshelpStudents and thus becomes the FOREIGN KEY.

SQL Server Alter table Add Foreign Key Constraint

The Foreign Key can be added to the existing table by using the SQL Server ALTER Table command. 

ALTER TABLE oracleappshelpStudents
ADD CONSTRAINT FK_UserId
FOREIGN KEY (UserId) REFERENCES oracleappshelpUsers (UserId);

SQL Server Drop Foreign Key Constraint

The below SQL Server Query can be used to Drop the Foreign Key Constraint

ALTER TABLE oracleappshelpStudents
DROP FOREIGN KEY FK_UserId;

SQL Server UNIQUE and NOT NULL Constraint in table

We have covered the overview related to SQL Server Constraint in the blog – SQL Server Constraint in the table

Lets discuss each constraint in Detail

SQL Server NOT NULL Constraint

The SQL Server NOT NULL Constraint is applied on the Column level in the table. It depicts that the particular column where NOT NULL is applied, that column CANNOT have the NULL values.

Whenever a record is being inserted or updated in the table, this specific column should have the value always.

In some cases when we do not get value in the record, we can impose DEFAULT value also to ensure that record is always inserted with the value.

SQL Server UNIQUE Constraint

As the name suggest UNIQUE constraint applied on the Table Column ensures that all values stored in the column with Constraint UNIQUE have different values.

Creating the UNIQUE Constraint on Table Creation by specifying at column level

CREATE TABLE oracleappshelpUsers
(
    UserId				INT 				NOT NULL UNIQUE,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10),
    FamilyName 			VARCHAR(128) 		NOT NULL
);

Creating the UNIQUE Constraint on Table Creation by adding CONSTRAINT as part of Table

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 				NOT NULL ,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10),
    FamilyName 			VARCHAR(128) 		NOT NULL,
	CONSTRAINT UC_Users UNIQUE (UserId,GivenName)
);

SQL Server DROP Constraint

The below SQL Query allows to DROP the applied constraint on the table

ALTER TABLE oracleappshelpUsers
DROP INDEX UC_Users;

SQL Server Constraints in the table

A SQL Server Database could have multiple tables and each table that has to be stored based on some rules. These rules are the possible checks & conditions which allows you to ensure the data validations before it is being inserted into a specific table.

SQL Server Constraints Types

SQL server constraints can be divided primarily into :

  1. SQL Server Table level constraints
  2. SQL Server Column level constraints
S. NoSQL Server Constraint Table / Column LevelDescription
1NOT NULL Column Level Column value cannot be specified as NULL
2UNIQUE Column Levelall values in the column need to be UNIQUE
3CHECKColumn Level all values in a column satisfies a specific condition
4DEFAULTColumn Levelspecifies a DEFAULT value for the column
5INDEXTable Levelapply indexes make the data retrieval fast
6 PRIMARY KEY Column LevelUnique identifier for the column. Combination of NOT NULL and UNIQUE values
7 FOREIGN KEY Column LevelIdentifies the Column with Unique values in another table referenced in another table.

SQL Server Constraints can be applied :

  1. Create Table
  2. Alter Table

Syntax: SQL Server ADD Constraints in Table

CREATE TABLE table_name (
table_col1 datatype constraint,
table_col2 datatype constraint,
table_col3 datatype constraint,n
….

table_col3 datatype constraint
);

CREATE TABLE oracleappshelpStudents
( StudentId INT IDENTITY PRIMARY KEY,
UserId INT REFERENCES oracleappshelpUsers (UserId),
Email VARCHAR(256) ,
CreatedBy VARCHAR(128) NOT NULL,
UpdatedBy VARCHAR(128) NOT NULL,
CreatedDate DATETIME NOT NULL,
UpdatedDate DATETIME NOT NULL )

We will be discussing in separate blog for each Constraint . Please refer the below given links

SQL Server Not Null Constraint in the table

SQL Server Primary Key / Foreign Key Constraint in the table

SQL Server Default Constraint Check