I've seen this question or something similar in many places recently in regards to the XML data type that was introduced in SQL Server 2005.  I've been using XML with SQL Server for a while, going back to SQL Server 2000, and was very pleased with the additions made in 2005.

First, let's create a table to hold the XML data:

IF OBJECT_ID('dbo.xmlStorage', 'U') IS NOT NULL
DROP TABLE dbo.xmlStorage;
GO
CREATE TABLE dbo.xmlStorage
(
ID                           INT IDENTITY(1,1)   
,XMLDocument        XML NOT NULL
);

Simple enough, an ID and an XML column.  Now lets add some data.

INSERT dbo.xmlStorage (XMLDocument) VALUES (
'<Customers>
  <Customer CustomerID="1" CustomerName="Charles Schwab">
    <Accounts>
      <Account AccountID="1" AccountName="Image Consultant" />
      <Account AccountID="501" AccountName="Image Consultant South" />
    </Accounts>
  </Customer>
</Customers>');

INSERT dbo.xmlStorage (XMLDocument) VALUES (
'<Customers>
  <Customer CustomerID="2" CustomerName="Mohawk Industries Inc.">
    <Accounts>
      <Account AccountID="2" AccountName="Geological Engineer" />
    </Accounts>
  </Customer>
</Customers>');

INSERT dbo.xmlStorage (XMLDocument) VALUES (
'<Customers>
  <Customer CustomerID="3" CustomerName="Wondercat Solutions">
    <Accounts>
      <Account AccountID="7" AccountName="Ensley Manufacturing" />
    </Accounts>
  </Customer>
</Customers>');

We have a single Customer node with potential for multiple Account nodes within.  We need to get each Customer and their associated Accounts into a record set.  To do so we will make use of the .value() and .nodes() options.

The following query will return each Customer node ID and the child AccountID and AccountName:

SELECT
    XMLDocument.value('(/Customers/Customer/@CustomerID) [1]', 'INT') AS CustomerID
    ,Tablename.columnname.value('@AccountID', 'INT') AS AccountID
    ,Tablename.columnname.value('@AccountName', 'VARCHAR(100)') AS AccountName
FROM
    dbo.xmlStorage
CROSS APPLY XMLDocument.nodes('/Customers/Customer/Accounts/Account') AS Tablename(columnname);

XMLDocument.value('(/Customers/Customer/@CustomerID) [1]', 'INT') is used to return the first entry in the Customers node.  This is our base if you will.

CROSS APPLY XMLDocument.nodes('/Customers/Customer/Accounts/Account') AS Tablename(columnname) is next and is used to query into the Accounts nodes and return the Account information for each customer. A handle is given for the CROSS APPLY for reference in the SELECT list.

Last we use our handle referencing the columnname.value() for the AccountID and AccountName.  In both cases we are referencing the column name and the data type which should be used for output.

And that does it, XML documents read to a result set.  Pretty easy stuff, right?

Now you can go out and beat down any hierarchical data structure that comes your way.  :)

Enjoy!

Transact-SQL (T-SQL from here on) has many unheralded functions that can make your life easier if you only know of their existence.  Today I'm talking about the NULLIF, QUOTENAME, and PARSENAME functions.  These functions are great for string manipulation and handle some common coding problems.

We will start with NULLIF.  This function allows you to examine an input value for a given expression, returning a NULL if the value is found.  Here's an example:

DECLARE @var VARCHAR(50);
SET @var = '';

SELECT NULLIF(@var, '') AS [@var];

If you run the code in SQL Server Management Studio (SSMS from here on) you will see the output of the query is NULL.  This can be very handy in data cleansing activities where you wish to remove empty strings.

Up next is the QUOTENAME function.  I find that I use this quite a bit in creating dynamic SQL as well as scripting out data from tables for insert scripts.  QUOTENAME takes a character string followed by a quote character.  You may use single quotation marks, double quotation marks, or single brackets.  If no quote character is passed then single brackets are used.

Here's an example:

DECLARE @var2 VARCHAR(50);
SET @var2 = 'Whitney';

SELECT 
    QUOTENAME(@var2, '''') AS [SingleQuotes]
    ,QUOTENAME(@var2, '""') AS [DoubleQuotes]
    ,QUOTENAME(@var2) AS [Brackets];

Running the code in SSMS returns each of the allowed quote characters applied to the variable.

Finally, the PARSENAME function can be used to return the specified parts of an object name. The function takes a string input for the object name and an integer value for the object piece you wish to return. 

The integer values are:

1 = Object name

2 = Schema name

3 = Database name

4 = Server name

The function does not validate the existence of the object but merely looks to return the values to you.

Here's an example:

DECLARE @FullyQualifiedName VARCHAR(100);
SET @FullyQualifiedName = 'MyServer.MyDatabase.MySchema.MyObject';

SELECT
    PARSENAME(@FullyQualifiedName, 1) AS ObjectName
    ,PARSENAME(@FullyQualifiedName, 2) AS SchemaName
    ,PARSENAME(@FullyQualifiedName, 3) AS DatabaseName
    ,PARSENAME(@FullyQualifiedName, 4) AS ServerName;

A quick trip back to SSMS and we see the query returned the pieces of the object name as described.  An additional, if perhaps unintended, use of the PARSENAME function is for breaking apart IP values.

In this example we need each IP octet returned as a separate value.  Here's the code:

DECLARE @IP VARCHAR(20);
SET @IP = '10.11.12.13';

SELECT 
    PARSENAME(@IP, 4) [IPOctet1]
    ,PARSENAME(@IP, 3) [IPOctet2]
    ,PARSENAME(@IP, 2) [IPOctet3]
    ,PARSENAME(@IP, 1) [IPOctet4];

For use with IP addresses you will need to reverse the output order of the IP as the PARSENAME function is looking to work from right to left.

And there you have it, three extremely useful functions that you may or may not have had in your T-SQL arsenal.  Hopefully these will help you out in the future.