XML Workshop V - Reading Values from XML Columns

If you are new to XQuery, you might find it little tricky to read values from an XML column. Reading a value from an XML variable is slightly different from reading values from an XML column. In an XML variable we have a single XML document. But the case of an XML column is different. When you query a table, each row in the table has an XML document stored in it.

Let us look at an example. In the following example, we assign a value to an XML variable and Query it.

    1 /*

    2 Let us declare an XML variable and store a value.

    3 */

    4 DECLARE @x XML

    5 SET @x =

    6 '<Name>

    7     <First>Jacob</First>

    8     <Last>Sebastian</Last>

    9 </Name>'

   10 

   11 /*

   12 Query the XML variable.

   13 */

   14 SELECT

   15     n.l.value('First[1]','VARCHAR(20)') AS First,

   16     n.l.value('Last[1]','VARCHAR(20)') AS Last

   17 FROM @x.nodes('/Name') n(l)

   18 

   19 /*

   20 OUTPUT

   21 

   22 First                Last

   23 -------------------- --------------------

   24 Jacob                Sebastian

   25 

   26 (1 row(s) affected)

   27 */

That was pretty much simple. You will need a slightly modified version of this query, if you need to read the values from an XML column. We need to use CROSS APPLY so that we can access more than one record unlike the previous example, where we had only one variable.

    1 /*

    2 Let us create a table and insert an XML value.

    3 */

    4 CREATE TABLE Employees ( Emp XML )

    5 INSERT INTO Employees ( Emp )

    6 SELECT

    7 '<Name>

    8     <First>Jacob</First>

    9     <Last>Sebastian</Last>

   10 </Name>'

   11 

   12 /*

   13 Let us try to query the table now

   14 */

   15 SELECT

   16     n.l.value('First[1]','VARCHAR(20)') AS First,

   17     n.l.value('Last[1]','VARCHAR(20)') AS Last

   18 FROM Employees

   19 CROSS APPLY Emp.nodes('//Name') n(l)

   20 

   21 /*

   22 OUTPUT:

   23 

   24 First                Last

   25 -------------------- --------------------

   26 Jacob                Sebastian

   27 

   28 (1 row(s) affected)

   29 */

My XML Workshop V at SQLServerCentral presents a detailed tutorial which explains the usage of CROSS APPLY. It also presents a few different ways to query a table and retrieve different pieces of information.

After the steps of webhosting and domain name registration, internet marketing takes over. The cheap hosting site can thus spend on backup software as well.This can only be done after estimating the web space. Programs like adwords advertising can easily be employed later.

SQLCE WORKSHOP

Microsoft has recently came up with a new lightweight embedded database for desktop applications. This was initially called SQL Server Everywhere and then got renamed to SQL Server Compact Edition or SQLCE for short. I found this tiny database engine to be pretty interesting and after working with it for a while, started writing a series of short articles. This post might serve as an index to find the right article in the series. I will keep updating this post when a new article of this series is published.

SQLCE WORKSHOP I - An Introduction to SQL Server Everywhere

SQLCE was initially named SQL Server Everywhere. This post provides a basic introduction to the product and presents a quick sample application which connects to an SQLCE database file and reads data.

 

SQLCE WORKSHOP II - A Closer look at SQLCE

This post attempts to present a closer view of SQLCE. It looks into evolution of SQLCE and looks at a few interesting facts about the product. Important features of the product are discussed along with a few use cases where the use of SQLCE might be ideal.

 

SQLCE Workshop III - Getting started with 3.5 Beta

Provides a basic introduction to SQL CE 3.5 beta which involves installation, creating a database and tables, connecting to the database file and inserting records to the newly created table from VB.NET as well as C#.NET.

 

SQLCE Workshop IV - Integration with Visual Studio 2005

Provides an introduction to the Integration of SQL CE with Visual Studio 2005. Explains how to create database, tables and populates data from visual studio.

SQL Server 2005 XML and Default Namespaces

XML and Default Namespaces

An XML document can have multiple namespace declarations. Elements inside an XML document may be linked to dozens of different namespaces. When you have several namespaces declared in your XML document, you can make one of them as default namespace. When you have a default namespace declared, you can omitt the namespace prefix from all the elements that belong to the default namespace.  

SQL Server 2005 and Default Namespaces

SQL Server 2005 supports default namespaces in XML documents. You can create XML documents with default namespaces. You can query XML documents which contains default namespace declarations too. The TSQL keyword WITH NAMESPACES will be helpful while working with XML namespaces.

 I have presented some examples for reading XML documents as well as Generating XML results in XML Workshop XI - Default Namespaces at SQL Server Central. WITH NAMESPACES is a very little known TSQL keyword but it is very powerful to provide namespace related support.

One wonders that with the advent of wireless internet how things have changed. Now internet marketers can get internet phone software in their sites as everyone has access to wireless internet. These steps can be taken after domain registration on any web hosting service, preferably a business hosting.

XML Namespaces

The primary purpose of namespaces is to resolve ambiguity. An XML document can contain more than one element with the same name, but each of them may have different meaning. For example, an XHTML document may have a <table> element  which refers to an HTML table. But it can also have a <table> element which refers to a wooden table.

Similarly, think of an XML document which uses the element <provider> for an Internet Provider as well as for a Database connection provider. When an XML parser finds such an ambiguous element, it needs to understand in which context the element has to be interpreted. This is when a namespace declaration can come at help. By adding a namespace prefix to the element, we can resolve ambiguity. With the addition of a namespace the elements will become <html:table> and <furniture:table>. Similarly <provider> becomes <Internet:provider> and <database:provider>.

I am pretty sure that most of you out there, must have came across cases where you need to resolve some kind of ambiguity. If you are a database developer you must be using table aliases to avoid ambiguity when two tables are joined. If you are a .NET developer you must be putting your classes into different namespaces so that "Database.Connection" object is differentiated from "Internet.Connection", where "Database" and "Internet" stand as namespaces. Each programming language has its own syntax which the developer should follow to avoid ambiguity while accessing objects or variables.

Namespaces were added to XML for the same purpose. With the help of a namespace, an XML document can be extended. You can add additional information to your XML documents without breaking the validation rules. With the advent of Web 2.0, RSS and ATOM feeds have become a "must have" in every Web Site. There are specifications which strictly controls the structure of the XML. An RSS feed will be considered valid only if it follows the structure defined by the specification. This means that the structure of an XML document is very rigid most of the times. Though we call it 'eXtensible Markup Language' (XML), we cant simply go ahead and extend it by adding our own elements. If I add '<PhoneNumber>' to an RSS document, this will break the specification rules the feed will be considered as invalid. Almost all feed parsers and readers will reject my RSS feed because it contains an element that is not permitted.

So how do I 'eXtend' my XML document? Why do you call it 'eXtensible'? Well, we can extend an XML document by using a namespace. I can declare a namespace which points to my own information and can add '<my:PhoneNumber>' and it will not break the specification defined for the given XML document. So we could say that, with the help of a namespace, one can add additional information to an XML document. The additional information will not disturb parsers which expects the previous XML structure. Those parsers will not even see the new information. The new information will be seen by only parsers which will specifically look for the additional namespace. This will make our XML documents 'eXtensible'.

We have seen many of the XML features exposed by SQL Server 2005. We had seen how to generate XML results using FOR XML keyword in the early sessions of XML Workshop. Chapter 10 of XML Workshop explains the usage of namespaces. This session shows an example which generates an XML Result Set having namespace information with TSQL.

XML in SQL SERVER 2005

SQL Server 2005 came up with a suite of exciting XML features. I am pretty sure that most developers working with SQL Server 2005 must be using the XML support. In the past few years, I had seen a lot of discussion (or would you call it a debate?) about XML support at the database level. Some people argue that XML is not needed at all, where as some argue that XML can make your life better.

I am from the latter group. I admire the XML capabilities of SQL Server 2005. My team makes a good use of the XML capabilities of SQL Server 2005. However, the intention of this post is NOT to advocate for the use of XML. I do believe that there is no single solution to any given problem. The solution varies depending on the angle that you take to analyze it and the circumstances, availability of resources etc.

If you are in-charge of designing a solution to a given problem, you do you have your freedom to take the approach that you feel is better. You must be having your own reason for that. And you will be right, as long as the problem is solved in the acceptable level. A given problem may be solved by 10 different people in 10 different ways. But if the quality of the result is acceptable, we should count all of them to be correct.

I found several interesting comments on this topic at the discussion forums of SQL Server Central. Many of my XML articles witnessed interesting comments where many database experts expressed their views on the usage of XML. I think the answer is so simple. If XML helps you to solve a certain problem better, go for it. Otherwise, stick to a non-XML approach. Whenever you find a problem which can be better solved with the usage of XML, use it. I have come across several situations where the usage of XML was found to be better choice for a variety of reasons. Some of my articles at SQL Server Central covers a few such scenarios.

I think before we argue on "XML" or "NO XML" we should look at the XML capabilities of SQL Server 2005 from a neutral angle. It makes sense to understand the XML features in detail. Once we have a very good understanding of what SQL Server 2005 can do with XML, we will be able to decide whether some of those capabilities can be useful to the problems that we are trying to solve in our day-to-day programming life.

I would classify the XML support extended by SQL Server 2005 into 4 broad categories.

1. Generating data in XML format. Many applications today need to transform relational data in to XML format. The best example is web sites which generates XML feeds. Almost all Web 2.0 web sites today publish RSS or ATOM feeds. This involves reading relational data from the databases and generating the XML out put as per the specified feed format. Service Oriented Architecture (SOA) is getting more and more popularity and applications that communicate with other applications need to exchange data in XML format. This is shows the importance of having the capability to generate XML result sets. SQL Server 2005 supports generating XML results from relational data by using FOR XML keyword. This may be used with AUTO, RAW, PATH or EXPLICIT. Each of the above keywords give you different level of control over the structure of XML output being generated.
2. Validating XML data. Once you start using XML data extensively, the validation requirements will arise. You need to validate the structure of the XML as well as the quality of the data. If your application is accepting XML data from others, you need to make sure that the data exchanged is as per the required structure. Further, the values need to be validated as well. With SQL Server 2005, you can define an XSD schema to validate the structure and quality of the XML data being processed. You can create XML SCHEMA COLLECTIONS in SQL Server 2005. An XML column or variable can be associated to an XML schema which validates the XML.
3. Indexing XML Columns. An XML column can store large XML documents. XML documents are stored as BINARY objects. Searching for values in an XML column might be time consuming if the XML documents are big and the table has too many records. By creating correct indexes on XML columns, you can make your XML Queries faster. SQL Server 2005 supports XML indexes.
4. Querying XML data. If you have columns of XML data type, it is apparent that you will have to query the XML data and extract specific values from the XML document. SQL Server 2005 supports XQuery, the most popular XML query language. XQuery gives you enough freedom to query the XML data and extract the specific piece of data that you need.

It would be helpful to have a closer look into each of those areas and you might realize that some of those features can make your life easier. For example, if your application generates RSS or ATOM feeds, you might find it easier to work at the database level, rather than at the application level. Most application that generates feeds today, read data from the database and generate XML data at the application level. A typical .NET application might retrieve an ADO.NET DataSet from the database and might use the .NET XML classes to generate the specific version of the feed. Some of you might feel that it would be better to do this at the data base level. Instead of returning a normal result set to the application and having the application do the transformation, some of you might find it interesting to do with FOR XML.

So friends, I invite all of you to dive more into the XML capabilities of SQL Server 2005 and see if you can find something that will make your day-to-day programming life easier and make you more productive.