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:
Pingback: T-SQL Tuesday #065 – Teach Something New (Roundup) | Mike Donnelly, SQLMD
This is pretty awesome! We’ve got some tables that stores XML as Text. To get the result out of the column to the front end was always a bit of a mission. With your SQL code I could modify the T-SQL to something like this:
select (cast(dataxml as xml)).value(‘(/DOC/HEAD/JOB_NAME)[1]’, ‘varchar(max)’)
from MyTableName.
I would like to add that the “tags” in the XML is case sensitive, otherwise you will get a NULL value back.
LikeLike
Thanks, need to emphasize the case sensitive part.
No idea on performance, so I’d be sure you test a bit, but I’d think this is as good as querying in real time.
LikeLike