T-SQL Tuesday #65 – Learning Computed Columns for XML

It’s T-SQL Tuesday time again, and this month the invite comes from Mike Donnelly (@sqlmd). The topic is Teach Something New and it’s a good one.

I try to learn something all the time, and this post is about something I ran into recently with a submission from a user that I needed to check. I had to dig in a bit and see how XML worked in computed columns and I decided to write about it for this month’s entry.

T-SQL Tuesday is a monthly event on the second Tuesday of each month. There’s a new invite each month, and you need to watch Twitter and the #tsql2sday tag to find the next one. I keep a running list of past topics, and feel free to write on any of them and post something.

Learn Something New

I haven’t done a lot of work with computed columns or XML in my career. At least not in SQL Server. I do remember using VB and traverse XML documents and pull out nodes, but that was years ago.

Recently I had to verify how XML computed columns work and that required me to dig in a bit to the rules for both. This post looks at the way in which I learned how to return part of an XML column from a table.

Imagine that we have a table storing some XML data inside it. For my demo, I have a table storing profile information for users. I’ve actually seen a table like this, grabbing information for a website and then shredding it later if there are changes needed in the database. Here’s the setup of the table with a row of data inserted.

CREATE TABLE UserProfile
(
  UserID INT IDENTITY(1,1)
, profile XML 
);
GO
INSERT INTO dbo.UserProfile
        ( profile
        )
    VALUES
        ( N'<user>
             <username>way0utwest</username>
             <history>
               <pageurl>/default</pageurl>
               <pageurl>/scripts/</pageurl>
               <pageurl>/scripts/id/2433</pageurl>
             </history>
             <points>
                <forums>32432</forums>
                <qotd>1123</qotd>
             </points>
        </user>'
        );

If I want the username from this table, I can use this query:

SELECT
   profile.value('(/user/username)[1]', 'varchar(max)')
    FROM
        dbo.UserProfile AS up;

That will return this data:

However, suppose I don’t want to use that code in all my queries in the application. Certainly it’s not bad for stored procedures, but I don’t want to repeat that over and over either.

Instead I want to use a computed column. I’d think that we could do something like this:

ALTER TABLE dbo.UserProfile
 ADD Username AS (profile.value('(/user/username)[1]', 'varchar(max)'));

However that doesn’t work. I get this error:

Msg 435, Level 16, State 16, Line 1

Xml data type methods are not supported in computed column definitions. Create a scalar user-defined function to wrap the method invocation. The error occurred at column "Username2", table "UserProfile", in the ALTER TABLE statement.

I haven’t seen this documented in Books Online, but it’s a restriction. However, there is a specific document on using XML in computed columns, which gives a workaround. The way to do this is to use a user-defined function. I need to build a function with my query:

CREATE FUNCTION UserProfile_XML_UserName(@xml xml) RETURNS varchar(200)
AS
BEGIN
 RETURN @xml.value('(/user/username)[1]', 'varchar(max)')
END
;
GO

Now, I can use my function in the computed column

ALTER TABLE dbo.UserProfile
 ADD Username AS (dbo.UserProfile_XML_UserName(profile));

Now when I query the table, I have a new column with the username:

SELECT top 10
  *
 FROM dbo.UserProfile AS up

Here are my results

That’s what I learned in the last month in working with T-SQL. This came from a user submission, and it required me to dig into BOL and find out how I could make this work.

References

Here are the references I used to learn about this:

• https://msdn.microsoft.com/en-us/library/bb510426.aspx
• https://msdn.microsoft.com/en-us/library/ms188300.aspx#TsqlProcedure