In reality, database file were not using the same language and had very little in common. To standard the way databases are created and managed, the structured query language (SQL) was created. Unfortunately, each company uses its own implementation of SQL and sometimes there are many differences among them. In other words, even though some structures are similar, SQL code used in one environment cannot be used in another environment (data types in Oracle, Microsft SQL Server, IBM DB2, MySQL, and Microsoft Access are sometimes different; there are many other differences). A possible good alternative would be a common language that any programming environment or database system can understand.
The Extensible Markup Language, or XML, is a language that uses a series of tags in a regular text-based document so that any programming environment that can read the document can also interpret it and use it as it juges necessary. Of course, there are rules that govern how the document's contain is, what is in it and how the contents can be used. As mentioned already, an XML document is just a regular text-based document that uses some standards with regards to it contents. In fact, you can create an XML document using any text editor of your choice, such as Notepad in Microsoft Windows. After creating the document, you should save it. If you do, you must give it a normal computer file name but you should use the extension .xml. To keep the standard going, universal, and out of random influence, the World Web Consortium (W3C) (http://www.w3c.org) iis the regulatory body that documents XML and describes the language in an appropriate web site organization. Because XML is an evolutionary language that can change regularly, it is released through an XML Recommendation document with a version.
As mentioned already, XML is primarily created as a normal file. As such, you can save it, open it, read it or view it, or manipulate it. You have various options. To normally open an XML document to modify it, you can use any any text editor, like Notepad. To open an XML document for viewing, you can use any Web browser of your choice or any appropriate application: Microsoft SQL Server (SQL Server Managerment Studio), Microsoft Word, Microsoft Visual Studio, etc. To programmatically open an XML document in Microsoft SQL Server, open a Query window. Create a SELECT statement. Call FROM OpenRowset() and pass two arguments. The first is BULK followed by the file name or its complete path. The second argument is SINGLE_BLOB. Outside the parentheses, type a letter followed by the XML variable you had declared in parentheses. Here is an example: SELECT CAST(doc as XML) AS [XML Document] FROM OpenRowset(BULK 'C:\Exercises\videos.xml', SINGLE_BLOB) R(doc); GO A markup is an instruction that defines XML. The fundamental formula of a markup is: <something> The left angle bracket "<" and the right angle bracket ">" are required. Inside of these symbols, you type a word or a group of words of your choice, using regular characters of the English alphabet and sometimes non-readable characters such as ?, !, or [. The combination of a left angle bracket "<", the right angle bracket ">", and what is inside of these symbols is called a markup. There are various types of markups we will learn. As mentioned above, XML is released as a version. Because there can be various versions, the first line that can be processed in an XML file must specify the version of XML you are using. At the time of this writing, the widely supported version of the .NET Framework is 1.0. When creating an XML file, you should (should in 1.0 but must in 1.1) specify what version your file is conform with, especially if you are using a version higher than 1.0. For this reason, an XML file should start (again, must, in 1.1), in the top section, with a line known as an XML declaration. It starts with <?xml version=, followed by the version you are using, assigned as a string, and followed by ?>. An example of such a line is: <?xml version="1.0"?> XML tags are created using characters of the alphabet and conform to the ISO standard. This is known as the encoding declaration. For example, most of the characters used in the US English language are known as ASCII. These characters use a combination of 7 bits to create a symbol (because the computer can only recognize 8 bits, the last bit is left for other uses). Such an encoding is specified as UTF-8. There are other standards such as UTF-16 (for wide, 2-Byte, characters). In fact, Microsoft SQL Server uses Unicode UTF-16. To specify the encoding you are using, type encoding followed by the encoding scheme you are using, which must be assigned as a string. The encoding is specified in the first line. Here is an example: <?xml version="1.0" encoding="utf-8"?>
Earlier, we mentioned that XML worked through markups. A simple markup is made of a tag created between the left angle bracket "<" and the right angle bracket ">". Between those two symbols, you must include the name of the tag. XML is case-sensitive. This means that CASE, Case, and case are three different words. Therefore, from now on, you must pay close attention to what you write inside of the < and the > delimiters. Besides case sensitivity, there are some rules you must observe when naming the tags of your markups:
In our lessons, here are the rules we will apply:
In future sections, we will learn that, with some markups, you can include non-readable characters between the angle brackets. In fact, you will need to pay close attention to the symbols you type in a markup. We will also see how some characters have special meaning.
When creating a tag, you must close it. You have two alternatives. To close a tag, use the same formula of creating a tag with the left angle bracket "<", the tag, and the right angle bracket ">" except that, between < and the tag, you must type a forward slash. The formula to use is: <tag></tag> The item on the left side, in this case <tag>, is called the opening or start-tag. The item on the right side, in this case </tag>, is called the closing or end-tag. Like <tag> is a markup, </tag> also is called a markup. Both an XML document and an XML tag must follow some strict rules in order to be valid. When an XML document or tag is submitted to Microsoft SQL Server, it is in fact relayed to an application named an XML parser. That parser "scans" the document or the tag to check it. If there is a problem, the document or tag is flagged as invalid and cannot be processed. If everything in the document is fine or the tag is valid, the document or the tag is said to be well-formed. The parser doesn't specify or care what the document or the tag is used for. It only checks that the document or the tag is valid. Another application or another means must decide how to use the document or the tag.
Just creating a tag is not particularly significant. You should give it meaning. To do this, you can type a number, a date, or a string between the start and the end tags. What you type between those tags is referred to as the tag's text or its value. Here is an example: <Student>Paul Bertrand Yamaguchi</Student>
We mentioned that, unlike HTML, every XML tag must be closed. We also saw that the value of a tag was specified on the right side of the right angle bracket of the start tag. In some cases, you will create a tag that doesn't have a value or, may be for some reason, you don't provide a value to it. Here is an example: <Student></Student> This type of tag is called an empty tag. Since there is no value in it, you may not need to provide an end tag but it still must be closed. Although this writing is allowed, an alternative is to close the start tag itself. To do this, between the tag name and the right angle bracket, type an empty space followed by a forward slash. Based on this, the above line can be written as follows: <Student /> Both produce the same result or accomplish the same role.
Every XML document must have one particular tag that, either is the only tag in the file, or acts as the parent of all the other tags of the same document. This tag is called the root. Here is an example of a document that has only one tag: <rectangle>A rectangle is a shape with 4 sides and 4 straight angles</rectangle> This would produce:
When creating an XML document, you can type various items on the same line. If you are creating a long XML document, although creating various items on the same line is acceptable, this technique can make it (very) difficult to read. One way you can solve this problem is to separate tags with empty spaces. Here is an example: <title>The Distinguished Gentleman</title> <director>Jonathan Lynn</director> <length>112 Minutes</length> Yet a better solution consists of typing each item on its own line. This would make the document easier to read. Here is an example: <title>The Distinguished Gentleman</title> <director>Jonathan Lynn</director> <length>112 Minutes</length> All these are possible and acceptable because the XML parser doesn't consider the empty spaces or end of line. Therefore, to make your code easier to read, you can use empty spaces, carriage-return-line-feed combinations, or tabs inserted in various sections. All these are referred to as white spaces.
Most XML documents contain more than one tag. We saw that a tag must have a starting point and a tag must be closed. One tag can be included in another tag: this is referred to as nesting a tag. A tag that is created inside of another tag is said to be nested. A tag that contains another tag is said to be nesting. Consider the following example: <Smile>Please smile to the camera</Smile> <English>Welcome to our XML Class</English> <French>Bienvenue à notre Cours XML</French> In this example, you may want the English tag to be nested in the Smile tag. To nest one tag inside of another, you must type the nested tag before the end-tag of the nesting tag. For example, if you want to nest the English tag in the Smile tag, you must type the whole English tag before the </Smile> end tag. Here is an example: <Smile>Please smile to the camera<English>Welcome to our XML Class</English></Smile> To make this code easier to read, you can use white spaces as follows: <smile>Please smile to the camera <English>Welcome to our XML Class</English> </smile> When a tag is nested, it must also be closed before its nesting tag is closed. Based on this rule, the following code is not valid: <Smile>Please smile to the camera <English>Welcome to our XML Class </Smile> </English> The rule broken here is that the English tag that is nested in the the Smile tag is not closed inside the Smile tag but outside. Normally in XML, if there are more than one tag in an XML document, one of them must serve as the parent or root (but this rule is not valid in Microsoft SQL Server because the parser takes care of it behind the scenes). Otherwise, you would receive an error. Based on this rule, the following XML code is not valid: <rectangle>A rectangle is a shape with 4 sides and 4 straight angles</rectangle> <square>A square is a rectangle whose 4 sides are equal</square> This would produce:
To correct this type of error, you can change one of the existing tags to act as the root. In the following example, the <rectangle> tag acts as the parent: <rectangle>A rectangle is a shape with 4 sides and 4 straight angles <square>A square is a rectangle whose 4 sides are equal</square></rectangle> This would produce:
Alternatively, you can create a tag that acts as the parent for the other tags. In the following example, the <geometry> tag acts as the parent of the <rectangle> and of the <square> tags: <geometry><rectangle>A rectangle is a shape with 4 sides and 4 straight angles </rectangle><square>A square is a rectangle whose 4 sides are equal</square></geometry> This would produce:
As mentioned already, a good XML file should have a Document Type Declaration: <?xml version="1.0" encoding="utf-8"?><geometry><rectangle>A rectangle is a shape with 4 sides and 4 straight angles</rectangle><square>A square is a rectangle whose 4 sides are equal</square></geometry> Once you know how to nest tags, you can create better looking markups. Here is an example: <album>
<shelfnumber>FJ-7264</shelfnumber>
<title>Symphony-Bantu</title>
<artist>Vincent Nguini</artist>
<copyrightyear>1994</copyrightyear>
<publisher>Mesa Records</publisher>
</album>
<album>
<shelfnumber>MR-2947</shelfnumber>
<title>None</title>
<artist>Debbie Gibson</artist>
<copyrightyear>1990</copyrightyear>
<publisher>Atlantic</publisher>
</album>
It is important to know that the whole XML code can be created as one line of text and the code would be valid.
To give you the ability to use XML in your databases, Transact-SQL provides a data type named XML. You can primarily use it like any of the data data types we have seen so far. For example, you can declare a variable of type XML. Here is an example: DECLARE @Student xml;
GO
We already that, to initialize a variable, you can use the SET operator followed by the name of the variaable and assign the desired value to it. If you had declared the variable as a string (type char, varchar, text, and their variants), you can create its value as an XML markup and assign it to the variable. You can make it an empty tag. Here is an example: DECLARE @StudentName nvarchar(50)
SET @StudentName = N'<name />';
GO
Here is the same type of example: DECLARE @StudentName nvarchar(50) SET @StudentName = N'<name></name>'; GO Just creating a tag is not particularly significant. You should give it meaning. To do this, you can type a number, a date, or a string between the start-tag and the end-tag. What you type between those tags is referred to as the tag's text or its value. Here is an example: DECLARE @StudentName nvarchar(50) SET @StudentName = N'<name>David Salomon</name>'; GO In this case, since the variable is a string, its value can contain anything. On the other hand, if your had declared the variable as XML, then you should (must) create its value as a well-formed tag. The above codes can be written as: DECLARE @StudentName xml SET @StudentName = N'<name />'; SET @StudentName = N'<name></name>'; SET @StudentName = N'<name>David Salomon</name>'; GO After declaring and initializing an XML variable, if you want to display it, you can create a SELECT statement that selects the variable. You can do this even if the variable was declared as a string. Here is an example: DECLARE @StudentName nvarchar(50) SET @StudentName = N'<name />'; SELECT @StudentName; GO You can also SELECT the value of an XML variable. Here is an example: DECLARE @StudentName xml SET @StudentName = N'<name />'; SELECT @StudentName; GO If you execute a SELECT statement that selects the value of a variable, if the variable was declared as a string, it would simply appear "as is" iin the Results section. If the variable was declared as XML, the result would appear as a link. Here is an example:
If you click that link, it would display as an XML tag in a new window. If the tag is empty, it would show it only. Here is an example:
The result displays in the Query window with a file name and the .xml extension. This means that you can save the record as an XML file. As mentioned already, you can specify a value for an XML-based variable by including that value in its tag. After doing this, you can also SELECT that variable. Here is an example:
You can then click the result to see the value in another window. If the tag contains a value, it would show it:
As mentioned already, Transact-SQL supports XML through the xml data type. When creating a table, you can use that data type to create or add a column of type XML. If you are visually creating the table and if you want a column to use XML as its data type, in its corresponding Data Type combo box, select xml:
If you are programmatically creating the column, specify its data type as XML. Here is an example: CREATE TABLE Students
(
FullName xml,
);
GO
If you had created an XML-based column, when specifying the value of that column using code, in the placeholder of the column, add the tag as a string. As done for variables, the tag can be empty or made of a start-tag and an end-tag. Here is an example: USE Exercise;
GO
CREATE TABLE Students
(
FullName xml not null,
);
GO
INSERT INTO Students VALUES(N'<Student></Student>');
GO
Just as done for variable, to specify the text/value of an XML-based column, typer it between the start and end tags. Here is an example: INSERT INTO Students VALUES(N'<Student>Paul Bertrand Yamaguchi</Student>');
GO
We now know how to create a column that uses XML as its data type and how to create a record for it. In the same way, you can create as many records as you want, each using its own tag and an optional value. Here are examples: INSERT INTO Students
VALUES(N'<Student>Paul Bertrand Yamaguchi</Student>'),
(N'<Student>Marcial Engolo</Student>'),
(N'<Student>Jeannette Holms</Student>'),
(N'<Student>Harry Euls</Student>');
GO
As alternative, instead of creating each tag as its own record, you can add many tags as one. Here is an example: INSERT INTO Students
VALUES(N'<Student>Ernest Marshalls</Student><Student>Ann Sandt</Student>' +
N'<Student>Paul Motto</Student><Student>Anselme Jones</Student>');
GO
Everything will depend on how you plan to use the XML document; that is, how you will use the XML records of the table. As done with the other fields, when creating an XML-based column on a table, you can specify a default value for it. To do this, on the right side of the XML data type, type default followed by an XML tag. You can make the tag empty or specify its value. Here is an example: CREATE TABLE Topics
(
StudentNumber nchar(10) not null,
Gender xml default N'<gender>Unknown</gender>'
);
Instead of one XML-based column in a table, as done in a regular XML document, you can create different tags. In fact, when setting up a table, you can create various columns that use the XML data type. Here is an example: CREATE TABLE Employees
(
EmployeeNumber xml,
FirstName xml,
LastName xml,
Title xml
);
GO
When performing data entry, create a complete tag for each column, with the tag name and an optional value. Here are examples. Here is an example: CREATE TABLE Employees
(
EmployeeNumber xml,
FirstName xml,
LastName xml,
Title xml
);
GO
INSERT INTO Employees
VALUES(N'<EmployeeNumber>29730</EmployeeNumber>',
N'<FirstName>Philippe</FirstName>',
N'<LastName>Addy</LastName>',
N'<Title>General Manager</Title>'),
(N'<EmployeeNumber>28084</EmployeeNumber>', N'<FirstName>Joan</FirstName>',
N'<LastName>Shepherd</LastName>', N'<Title>Accountant</Title>');
GO
As you can see, each XML-based column can be a normal representative of any of the primitive data types (char and its variants (nchar, varchar, nvarchar), bit, date/time-based, and number-based types) we used in previous lessons.
From our study of data analysis, we know how to use a SELECT statement to select one or more columns from a table. This is also valid if the table has one or more XML-based columns. Remember that when you execute a SELECT statement, the value appears as a link. Here is an example:
You can then click that link to show the result in a new window. Instead of writing your own SELECT statement, if the table was already created, you can still show its field(s) in a Query window. To do this in Microsoft SQL Server, to visually open a table that contains at least one XML-based column, in the Object Explorer, right-click the table and click either
We saw that XML elements constituted the main objects of an XML document. We also saw that an element could be nested inside of another element. Instead of nesting an element, you can transform the nested element into being part of the nesting element and thereby giving away its element qualities. This is the basis of an attribute. An attribute is a characteristic of an element. As a characteristic, an attribute is created as a member of an element. There are similarities and differences between an element and an attribute. An element and an attribute have these in common:
The differences between an element and an attribute are:
Imagine you have an ISBN element as a child of a video element as follows: <video> <ISBN>0-7888-1623-3</ISBN> </video> An attribute must be created inside the start-tag of an element. To manually create an attribute, type the left angle bracket of the element, followed by the name of the element, an empty space, and the name of the attribute. The name follows the same rules we defined for names in XML. Here is an example: <video ISBN... An attribute should have a value that can be used to distinguish it. To specify the name of an attribute, assign a value as a string to its name. In the case of the above code fragment, since ISBN is simply a child of the video element, you can change the ISBN element to become an attribute of the video element as follows: <video ISBN="0-7888-1623-3"> Now, ISBN is an attribute of the video element. In the same way, you can create more than one attribute inside a tag. Here are examples of attributes in a tag: <student number="826-773" name="Willie Minko" gender="female" age="14">
Consider the following table: USE Exercise;
GO
CREATE TABLE Employees
(
EmployeeNumber int NOT NULL,
FirstName nvarchar(20),
LastName nvarchar(20) NOT NULL,
HourlySalary money
);
GO
INSERT INTO Employees
VALUES(62480, N'James', N'Haans', 28.02),
(35844, N'Gertrude', N'Monay', 14.36),
(24904, N'Philippe', N'Guillon', 18.05),
(48049, N'Eddie', N'Monsoon', 26.22),
(25805, N'Peter', N'Mukoko', 22.48),
(58405, N'Chritian', N'Allen', 16.45);
GO
When SELECTing the values of such a table, you can ask the SQL interpreter to produce the values as XML. To do this, declare an XML variable. Assign it a SELECT statement that selects the desired columns of the table. The statement must be included in parenthses. Before closing the parentheses, add FOR XML followed by an option. If you use FOR XML PATH, the interpreter would create a tag for each record. Each tag would use the name row. The value for each field would be created as child tag, using the name of the column and its own value. Here is an example: DECLARE @EmplRecords xml
SET @EmplRecords = (SELECT EmployeeNumber, FirstName, LastName
FROM Employees
FOR XML PATH)
SELECT @EmplRecords AS [Employees Records];
This would produce: <row> <EmployeeNumber>62480</EmployeeNumber> <FirstName>James</FirstName> <LastName>Haans</LastName> </row> <row> <EmployeeNumber>35844</EmployeeNumber> <FirstName>Gertrude</FirstName> <LastName>Monay</LastName> </row> <row> <EmployeeNumber>24904</EmployeeNumber> <FirstName>Philippe</FirstName> <LastName>Guillon</LastName> </row> <row> <EmployeeNumber>48049</EmployeeNumber> <FirstName>Eddie</FirstName> <LastName>Monsoon</LastName> </row> <row> <EmployeeNumber>25805</EmployeeNumber> <FirstName>Peter</FirstName> <LastName>Mukoko</LastName> </row> <row> <EmployeeNumber>58405</EmployeeNumber> <FirstName>Chritian</FirstName> <LastName>Allen</LastName> </row> A better idea is to specify a meaning name to each parent tag. To do this, add the parentheses to PATH and, in the parentheses, pass a string of your choice. Here is an example: DECLARE @EmplRecords xml
SET @EmplRecords = (SELECT EmployeeNumber, FirstName, LastName
FROM Employees
FOR XML PATH(N'Employee'))
SELECT @EmplRecords AS [Employees Records];
This would produce: <Employee> <EmployeeNumber>62480</EmployeeNumber> <FirstName>James</FirstName> <LastName>Haans</LastName> </Employee> <Employee> <EmployeeNumber>35844</EmployeeNumber> <FirstName>Gertrude</FirstName> <LastName>Monay</LastName> </Employee> <Employee> <EmployeeNumber>24904</EmployeeNumber> <FirstName>Philippe</FirstName> <LastName>Guillon</LastName> </Employee> <Employee> <EmployeeNumber>48049</EmployeeNumber> <FirstName>Eddie</FirstName> <LastName>Monsoon</LastName> </Employee> <Employee> <EmployeeNumber>25805</EmployeeNumber> <FirstName>Peter</FirstName> <LastName>Mukoko</LastName> </Employee> <Employee> <EmployeeNumber>58405</EmployeeNumber> <FirstName>Chritian</FirstName> <LastName>Allen</LastName> </Employee> If you use FOR XML RAW, the interpreter would create a parent tag for each record. Here is an example: DECLARE @EmplRecords xml
SET @EmplRecords = (SELECT EmployeeNumber, FirstName, LastName
FROM Employees
FOR XML RAW)
SELECT @EmplRecords AS [Employees Records];
In the result, the name of the tag would be row. Each column value would be created as an attribute. The above code would produce: <row EmployeeNumber="62480" FirstName="James" LastName="Haans" /> <row EmployeeNumber="35844" FirstName="Gertrude" LastName="Monay" /> <row EmployeeNumber="24904" FirstName="Philippe" LastName="Guillon" /> <row EmployeeNumber="48049" FirstName="Eddie" LastName="Monsoon" /> <row EmployeeNumber="25805" FirstName="Peter" LastName="Mukoko" /> <row EmployeeNumber="58405" FirstName="Chritian" LastName="Allen" /> Just as done for the PATH option, an alternative is to replace row with a better name. To do this, add parentheses to RAW and pass the desired name. Here is an example: DECLARE @EmplRecords xml
SET @EmplRecords = (SELECT EmployeeNumber, FirstName, LastName
FROM Employees
FOR XML RAW(N'Employee'))
SELECT @EmplRecords AS [Employees Records];
This would produce: <Employee EmployeeNumber="62480" FirstName="James" LastName="Haans" /> <Employee EmployeeNumber="35844" FirstName="Gertrude" LastName="Monay" /> <Employee EmployeeNumber="24904" FirstName="Philippe" LastName="Guillon" /> <Employee EmployeeNumber="48049" FirstName="Eddie" LastName="Monsoon" /> <Employee EmployeeNumber="25805" FirstName="Peter" LastName="Mukoko" /> <Employee EmployeeNumber="58405" FirstName="Chritian" LastName="Allen" /> If you use FOR XML AUTO, the interpreter would create an XML tag for each record. Here is an example: DECLARE @EmplRecords xml
SET @EmplRecords = (SELECT EmployeeNumber, FirstName, LastName
FROM Employees
FOR XML AUTO)
SELECT @EmplRecords AS [Employees Records];
In the result, the name of each tag is the name of the table. Inside this tag, each column is created as an attribute and assigned its value. The above code would produce: <Employees EmployeeNumber="62480" FirstName="James" LastName="Haans" /> <Employees EmployeeNumber="35844" FirstName="Gertrude" LastName="Monay" /> <Employees EmployeeNumber="24904" FirstName="Philippe" LastName="Guillon" /> <Employees EmployeeNumber="48049" FirstName="Eddie" LastName="Monsoon" /> <Employees EmployeeNumber="25805" FirstName="Peter" LastName="Mukoko" /> <Employees EmployeeNumber="58405" FirstName="Chritian" LastName="Allen" /> In the above examples, we selected all records. As an alternative, you can use a condition that restrict the records to include in the result. |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
