Home

Introduction to the Extensible Markup Language

 

The Extensible Markup Language

 

Databases as Computer Files

Databases used to be created as regular computer file. One of the problems was that they were not transportable. That is, you couldn't take a database file created in one environment to be used in another environment. For example, one company could create a database file and give it any random extension like .dbs. Another company could create another database file and name it with a file extension of .dab for example. Of course, these two databases could not collaborate and in fact could not understand each other.

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.

Introduction to XML

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.

Opening XML

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

The Fundamental Structure of an XML Document

     

Introduction to the Markup

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.

The Document Type Declaration (DTD)

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"?>

Encoding Declaration

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"?>

The Name of a Tag

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:

  • The name of a tag must be in one word, no space in the name
  • The name must start with an alphabetic letter or an underscore - Examples are <Country> or <_salary>
  • The first letter or underscore that starts a name can be followed by:
    • Letters - Example: <OperatingSystem>
    • Digits - Example: <L153>
    • Hyphens - Example: <TV-Rating>
    • Underscores - Example: <Chief_Accountant>
  • The name of a tag cannot start with xml, XML or any combination of X (uppercase or lowercase), followed by M (uppercase or lowercase), and followed by L (uppercase or lowercase)

In our lessons, here are the rules we will apply:

  • Sometimes a name will be made of lowercase only
  • Sometimes a name will start in uppercase or lowercase
  • When a name is a combination of words, such as [hourly salary], we may start each part in uppercase. Examples will be HourlySalary or DateOfBirth

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.

Closing a Tag

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.

XML Well-Formed

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.

The Text or Value of a 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>

Empty Tags

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.

The Root of an XML Document

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:

XML in a Browser

White Spaces

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.

Nesting Tags

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:

An ill-formed XML file in a Browser

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:

Good Nested Tags

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:

Preview

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.

XML Variables

     

Declaring an XML Variable

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

Initializing an XML Variable

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

SELECTing the Value of an XML Variable

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:

Selecting an XML Variable

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:

Selecting an XML Variable

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:

Selecting an XML Variable

You can then click the result to see the value in another window. If the tag contains a value, it would show it:

Selecting an XML Variable

XML and Data Tables

 

Introduction

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:

Introduction to Transact-SQL Support for XML

If you are programmatically creating the column, specify its data type as XML. Here is an example:

CREATE TABLE Students
(
    FullName xml,
);
GO

Creating a Value of an XML Field

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

The Text or Value of a Tag

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

Creating and Using XML Records in a Table

     

Introduction

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.

The Default Value of an XML-Based Column

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>'
);

Creating Various Tags

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.

SELECTing an XML-Based Field

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:

Selecting an XML Variable

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

  • Edit Top 200 Rows: In this case, the records would appear disabled:
     
    Opening XML

    This means that you cannot edit the records and you cannot change any value in a record
  • Select Edit Top 1000 Rows. In this case, each record appears a link. You can click a linked record to open it

Introduction to XML Attributes

 

Fundamentals

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:

  • Both (must) have a name
  • Each may or may not have a value

The differences between an element and an attribute are:

  • An attribute is considered a characteristic of an element. This means that an attribute belongs to an element
  • While an element can have one or more attributes, an attribute can neither have an element nor have another or more attributes
  • An attribute must be created in the start-tag of an element
  • An element cannot be defined as part of an attribute. That is, an attribute is subject to an element and the element owns the attribute

Creating an Attribute

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">

Topics on SELECTing Records in a Table

 

Introduction

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.

SELECTing With a Path

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>

SELECTing Raw Columns

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" />

SELECTing Auto Columns

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.

 
 
 

XML Elements

 

Introduction

An element in an XML document is an object that begins with a start-tag, may contain a value, and may terminate with an end-tag. Based on this, the combination of a start-tag, the value, and the end-tag is called an element. An element can be more than that but for now, we will consider that an element is primarily characterized by a name and possibly a value.

xml as a Composite Data Type

As mentioned in the previous section, xml is primarily a data type. As such, it can be used for a variable or in a table that uses any of the other data types. That is, you can add an xml-based column to a table that contains fields of the other types. Here is an example:

CREATE TABLE Employees
(
    EmployeeID int not null,
    FirstName nvarchar(20),
    LastName nvarchar(2) not null,
    HourlySalary money,
    Notes xml
);
GO

When adding records, you can provide the values of the other fields as seen in previous lessons. The value of the xml column can be any value of your choice. Here are examples:

Opening XML

Many characteristics set the xml data type apart from the other types. For example, it is the only data type whose field can be used in place of any of the primitive types. Consider the following table:

CREATE TABLE Employees
(
    EmployeeID xml not null, -- Integer
    FirstName xml,	     -- String
    LastName xml not null,   -- String
    HourlySalary xml,	     -- Decimal Number
    IsFullTime xml	     -- Boolean Value
);
GO

Still, when performing data entry, make sure you provide the appropriate value for each field (there are ways you can make the interpreter valid the value of a field to accept or reject it. Here are examples of adding two records to the above table:

Opening XML

Probably the most important characteristic that is unique to the xml data type is that, while the fields of the other types can contain only one value, the value of an xml-based field can contain sub-values. That is, the value of an xml-based field can be made of various internal values. The main rule is that the value must be well-formed, using a parent tag and child tags nested in it.

Here is an example for a variable:

DECLARE @Students xml
SET @Students = N'
<Student>
  <fullname>
      <firstname>Justin</firstname>
      <mi>W</mi>
      <lastname>Santorn</lastname>
  </fullname>
  <payroll>
      <title>Regional Manager</title>
      <status>Full Time</status>
      <salary>20.22</salary>
  </payroll>
</Student>'
GO

Here are two examples of records for XML-based columns:

CREATE TABLE Employees
(
    FullName xml,
    PayrollInformation xml,
);
GO

INSERT INTO Employees
VALUES(N'
<fullname>
    <firstname>Justin</firstname>
    <mi>W</mi>
    <lastname>Santorn</lastname>
</fullname>', N'
<payroll>
    <title>Regional Manager</title>
    <status>Full Time</status>
    <salary>20.22</salary>
</payroll>'), (N'
<fullname>
    <firstname>Frank</firstname>
    <mi>D</mi>
    <lastname>Swanson</lastname>
</fullname>', N'
<payroll>
    <title>Intern</title>
    <status>Part Time</status>
    <salary>12.47</salary>
</payroll>
');
GO

The Value of an Element

As seen in our introduction to XML, an element must have a name. For example, in <Director>, the word Director is the name of the element. An element must have at least a start-tag. All of the tags we have seen so far were created as elements.

Besides the name, an element may have text or a value. In the case of <director>Jonathan Lynn</director>, the "Jonathan Lynn" string is the value of the director element.

While the value of one element can be a number, the value of another element can be a date. Yet another element can use a regular string as its value. Consider the following example:

DECLARE @Videos xml

SET @Videos = N'
<videos>
    <video>
	    <title>The Distinguished Gentleman</title>
	    <director>Jonathan Lynn</director>
	    <LengthInMinutes>112</LengthInMinutes>
	    <format>DVD</format>
	    <rating>R</rating>
	    <price>14.95</price>
    </video>
    <video>
	    <title>Her Alibi</title>
	    <director>Bruce Beresford</director>
	    <LengthInMinutes>94</LengthInMinutes>
	    <format>VHS</format>
	    <rating>PG-13</rating>
	    <price>9.95</price>
    </video>
</videos>';
GO

Notice that the price elements contain numbers that look like currency values and the LengthInMinutes elements use an integer as value.

An element may not have a value but only a name. Consider the following example:

DECLARE @Videos xml

SET @Videos = N'
<videos>
  <video>
    <title>The Distinguished Gentleman</title>
    <director>Jonathan Lynn</director>
  </video>
</videos>'

In this case, the video element doesn't have a value. It is called an empty element.

Character Entities in an Element Value

Besides the obvious types of values, you may want to display special characters as values of elements. Consider the following example:

DECLARE @Employees xml

SET @Employees = N'
<Employees>
    <Employee>
	<FullName>Sylvie <Bellie> Aronson</FullName>
	<Salary>25.64</Salary>
	<DepartmentID>1</DepartmentID>
    </Employee>
    <Employee>
	<FullName>Bertrand Yamaguchi</FullName>
	<Salary>16.38</Salary>
	<DepartmentID>4</DepartmentID>
    </Employee>
</Employees>';
GO

If you try using this XML document, for example, if you try displaying it in a browser, you would receive an error:

XML Error

The reason is that when the parser reaches the <FullName>Sylvie <Bellie> Aronson</FullName> line, it thinks that <Bellie> is a tag but then <Bellie> is not closed. The parser concludes that the document is not well-formed, that there is an error. For this reason, to display a special symbol as part of a value, you can use its character code. For example, the < (less than) character is represented with &lt and the > (greater than) symbol can be used with &gt;. Therefore, the above code can be corrected as follows:

DECLARE @Employees xml

SET @Employees = N'
<Employees>
    <Employee>
	<FullName>Sylvie &lt;Bellie&gt; Aronson</FullName>
	<Salary>25.64</Salary>
	<DepartmentID>1</DepartmentID>
    </Employee>
    <Employee>
	<FullName>Bertrand Yamaguchi</FullName>
	<Salary>16.38</Salary>
	<DepartmentID>4</DepartmentID>
    </Employee>
</Employees>'
GO 

Here is a list of other codes you can use for special characters:

Code Symbol Code Symbol Code Symbol Code Symbol Code Symbol
&apos; ' &#067; C &#106; j &#179; ³ &#218; Ú
&lt; < &#068; D &#107; k &#180; ´ &#219; Û
&gt; > &#069; E &#108; l &#181; µ &#220; Ü
&amp; & &#070; F &#109; m &#182; &#221; Ý
&quot; " &#071; G &#110; n &#183; · &#222; Þ
&#033; ! &#072; H &#111; o &#184; ¸ &#223; ß
&#034; " &#073; I &#112; p &#185; ¹ &#224; à
&#035; # &#074; J &#113; q &#186; º &#225; á
&#036; $ &#075; K &#114; r &#187; » &#226; â
&#037; % &#076; L &#115; s &#188; ¼ &#227; ã
&#038; & &#077; M &#116; t &#189; ½ &#228; ä
&#039; ' &#078; N &#117; u &#190; ¾ &#229; å
&#040; ( &#079; O &#118; v &#191; ¿ &#230; æ
&#041; ) &#080; P &#119; w &#192; &#231; ç
&#042; * &#081; Q &#120; x &#193; Á &#232; è
&#043; + &#082; R &#121; y &#194; Â &#233; é
&#044; , &#083; S &#122; z &#195; Ã &#234; ê
&#045; - &#084; T &#123; { &#196; Ä &#235; ë
&#046; . &#085; U &#125; } &#197; Å &#236; ì
&#047; / &#086; V &#126; ~ &#198; Æ &#237; í­
&#048; 0 &#087; W &#160; empty &#199; Ç &#238; î
&#049; 1 &#088; X &#161; ¡ &#200; È &#239; ï
&#050; 2 &#089; Y &#162; ¢ &#201; é &#240; ð
&#051; 3 &#090; Z &#163; £ &#202; Ê &#241; ñ
&#052; 4 &#091; [ &#164; ¤ &#203; Ë &#242; ò
&#053; 5 &#092; \ &#165; ¥ &#204; Ì &#243; ó
&#054; 6 &#093; ] &#166; ¦ &#205; Í &#244; ô
&#055; 7 &#094; ^ &#167; § &#206; Î &#245; õ
&#056; 8 &#095; _ &#168; ¨ &#207; Ï &#246; ö
&#057; 9 &#096; ` &#169; © &#208; Ð &#247; ÷
&#058; : &#097; a &#170; ª &#209; Ñ &#248; ø
&#059; ; &#098; b &#171; « &#210; Ò &#249; ù
&#060; < &#099; c &#172; ¬ &#211; Ó &#250; ú
&#061; = &#100; d &#173; ­­ &#212; Ô &#251; û
&#062; > &#101; e &#174; ® &#213; Õ &#252; ü
&#063; ? &#102; f &#175; ¯ &#214; Ö &#253; ý
&#064; @ &#103; g &#176; ° &#215; × &#254; þ
&#065; A &#104; h &#177; ± &#216; Ø &#255; ÿ
&#066; B &#105; i &#178; ² &#217; Ù &#256; Ā

There are still other codes to include special characters in an XML file.

File: videos.xml
<?xml version="1.0" encoding="utf-8"?>
<videos>
    <video>
	<title>The Distinguished Gentleman</title>
	<director>Jonathan Lynn</director>
	<length>112 Minutes</length>
	<format>DVD</format>
	<rating>R</rating>
    </video>
    <video>
	<title>Her Alibi</title>
	<director>Bruce Beresford</director>
	<length>94 Mins</length>
	<format>DVD</format>
	<rating>PG-13</rating>
    </video>
    <video>
	<title>Chalte Chalte</title>
	<director>Aziz Mirza</director>
	<length>145 Mins</length>
	<format>DVD</format>
	<rating>N/R</rating>
    </video>
</videos>
 

Exercises

   

Lesson Summary Questions

Answers

 
 
     
 

Previous Copyright © 2008-2011 FunctionX.com Home