Computed Column based on XML Data Type

We have discussed computed columns quite a bit in our posts at this blog. You can search for “computed column” and get to all those posts – the basic ones are here and here. A few days ago, a reader had asked whether we can create a computed column based on an XML Data Type column and if so, how can we go ahead and do it. In this post, we will present such a solution along with a deterministic function that we will use to extract out the value for making the computed column.

We will use the same example for the XML that we had used in our post here to demonstrate the nodes() method. The function is pretty simple and is based on the same XML as was demonstrated in the post above with some changes:

create function dbo.fn_xml_computed
(
@x xml,
@va_lkup varchar(10)
)
returns bigint
as
begin
declare @shid bigint
set @shid = (select
T.VA.value(‘Key[1]‘, ‘bigint’) as Share_Holder_ID
from @x.nodes(‘//Details’) as T(VA)
where T.VA.exist(‘VoteAction/text()[.=sql:variable("@va_lkup")]‘) = 1)
return @shid
end
go

Here, we will be taking in two parameters – one would be the actual vote XML and the second one will be the vote action performed and we will use the function to extract out the information on the Shareholder_ID value. So, our table definition would look something like this:

create table dbo.testcase
(
vote_xml xml,
vote_action varchar(10),
shareholder_id AS dbo.fn_xml_computed(vote_xml, vote_action)
)

And now let’s do the insert into the table now:

insert into dbo.testcase (vote_xml, vote_action) values
(
'<VoteAgent>
                        <InstructAck>
                                          <MtgNtfcnId>444</MtgNtfcnId>
                                          <Details>
                                                <Key>0987654321</Key>
                                                <Shares>100</Shares>
                                                <VoteAction>FOR</VoteAction>
                                          </Details>
                        </InstructAck>
         </VoteAgent>',
'FOR'
);

Let’s check the results:

select * from dbo.testcase

vote_xml    vote_action shareholder_id
----------- ----------- --------------------
<....>      FOR         987654321

As you can see from above, the shareholder_id value got extracted and populated in the column automatically. So, the computed column concept applies to the XML data type columns as well.