http://edge.technet.com/Media/Real-World-DBA-Episode-13--SQL-Server-Features-XML/

This week:

In the news, you could win a Windows Home Server, just for evaluating Microsoft products.

In this week’s feature, we’ll continue the introduction to SQL Server Features and talk about SQL Server’s XML features.

The web link is on Internationalization, and the tip this week deals with the Surface Area Configuration tool.

 

In the News:

You can win a Windows Home Server for yourself, just for evaluating Microsoft Server products. Check out the link at http://tinyurl.com/5j539f. 

 

Feature:

In a previous Podcast I started an overview of the major features you can find in SQL Server 2005. I’ll cover each feature in more depth in future Podcasts, but we need a place to start the discussion for those features, so we’ll cover the basics first. This week we’ll take a look at the options you have for XML and SQL Server. Let’s take a very basic look at an XML file, or document.

 

XML, or Extensible Markup Language, isn’t really that special. It’s actually just ASCII text, with “tags” on either end. A tag has the format of an open “less-than” symbol (which is a shifted comma on US keyboards), the text of your tag, which can be anything you want, and then the “greater-than” symbol to finalize the start of the tag. In between the tag you can type anything you want, as long as you “close” the tag by typing it exactly as before, but this time with a forward-slash symbol before the tag text just after the less-than symbol. The combination of the tags and the text between them is called an XML element.  I’ll read off an element that could be used to indicate a name:

 

<name>Buck</name>

 

There are a few keys here. The first is that case matters. Also, every tag must be closed – even if there is no text in between it. You can “nest” tags by opening one and then opening another, and then closing out the inner one before you close the outer one. In fact, you can nest very deep.

 

Other XML documents can be used to format and control the data that an XML document can contain. We won’t go into that right now. In fact, you already know enough to get started creating your own XML documents.

 

What can XML be used for, and why is it important to SQL Server? XML documents work well as “control” files, when you need to read down a set of hierarchical data and process the elements it contains. XML is also a great way to transfer data between systems that use different databases, or even no databases at all. Everything from Microsoft Office to a Web browser is able to read and process an XML document. In fact, the new version of Microsoft Office stores its data in XML.

 

So why store this kind of data in SQL Server? Because you get the same advantages as with other data – security, safety, and speed. Since it’s just data, you can secure it along with everything else, and you can back it up and restore it as well.

 

You have a lot of options in SQL Server 2005 for working with XML data. To start, SQL Server 2005 has a database data type for XML. This means that you can store an entire XML document in a single column. You can also query XML data in five different ways using Transact-SQL, and SQL Server 2005 also supports XQuery, which is a special query language specifically designed for XML data. SQL Server 2005 includes the ability to index XML data so that it can be retrieved quickly.

 

To create or manipulate XML data, you can use the standard INSERT and UPDATE statements. To retrieve the XML data, you can use the FOR XML qualifier on a SELECT statement. But most people who are familiar with XML are more comfortable with the XQuery language. SQL Server 2005 supports the query(), value(), nodes(), exists() and modify() language constructs.

 

To access XML data from SQL Server 2005, you can use not only the standard connections you use for any database application, but you can also talk directly to an HTTP port that is listening for XML calls.

 

Web Link:

The web link for this week is on Internationalization and SQL Server. You can find it http://tinyurl.com/64ctmf. 

 

Tip of the Week:

You can use the SQL Server Surface Area Configuration tool to configure one computer, and then use the sac.exe utility to export the settings of that computer to a file. You can use that file to apply the same settings to SQL Server 2005 components on other computers.