Value method of xml data type not deterministic, xml column not accessible in 'deleted' table

I am presently taking my first, tremling and insecure steps in Sql Server as a whole - specifically Sql Server 2005. I am involved in a proposal for a huge data convserion project (banking) here in Denmark and presently trying to present small mockups to illustrate the potential of XML in general and specifically promote Sql Server 2005.

I have studied tons of documents on msdn including 'XML best practices ...' and found them very helpful. Stumbling along I have come to a complete stop ion the following 2 issues:

--------------------------
Issue 1.

I the mockup I am doing now, I have cloned the scenario for promoting an xml property to a computed column - it works fine. But when I try to create a primary index i get an error informing me that the index cannot be created because my user defined function computing the column is not deterministic.

Here my sql:

/* These ones are apparently not deterministic - how do I make them so */
create function dbo.getObjectType (@xData xml)
returns char(30)
as
begin
   declare @Ret char(30)
   select @Ret = @xData.value('/object[1]/@type', 'char(30)')
   return @Ret
end

create function dbo.getObjectId (@xData xml)
returns char(30)
as
begin
   declare @Ret char(30)
   select @Ret = @xData.value('/object[1]/@id', 'char(30)')
   return @Ret
end

CREATE TABLE [dbo].[tbBusinessObject](
   [Type] AS ([dbo].[getObjectType]([externalXml]))
   ,[Id] AS ([dbo].[getObjectId]([externalXml]))
   ,[externalXml] [xml] NOT NULL
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [idx_tbBusinessObject_Primary] ON [dbo].[tbBusinessObject]
(
[Type] ASC,
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]

The error reported is:
Msg 2729, Level 16, State 1, Line 1
Column 'Type' in table 'dbo.tbBusinessObject' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

I am unable to create a unque index on the columns Type and Id - can anybody help

------------------------
Issue 2:

Following the examples in XML best practise I have succeeded in creating a trigger to insert reference rows in another table when a new row is inserted in the tbBusinessObject table above - works fine. However, I can't get the delete trigger to compile. The xml column (externalXml) is not accessible.

Here is my sql:

/* Insert trigger works as a dream */
create trigger trg_tbBusinessObject_Ins_Reference
on dbo.tbBusinessObject
for insert
as
begin
   insert into dbo.tbBusinessObjectReference
   select
      r.parentType
      ,r.parentId
      ,r.childType
      ,r.childId
      ,r.referenceType
   from
      inserted as i cross apply dbo.getReferences(i.Type, i.Id, i.externalXml) as r
end

/* Delete trigger, doesn't compile */
create trigger trg_tbBusinessObject_Del_Reference
on dbo.tbBusinessObject
for delete
as
begin
   delete dbo.tbBusinessObjectReference
   where
         childType = dbo.getObjectType(deleted.externalXml)
   and childId = dbo.getObjectId(deleted.externalXml)
end

The errors are:
Msg 4104, Level 16, State 1, Procedure trg_tbBusinessObject_Del_Reference, Line 7
The multi-part identifier "deleted.externalXml" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_tbBusinessObject_Del_Reference, Line 7
The multi-part identifier "deleted.externalXml" could not be bound.

What do I do wrong

-----------------------------
The xml involved in both issues:

insert into dbo.tbBusinessObject (externalXml) values (
'<object type="KONTO" id="0000000001">
   <system>
      <references>
         <object type="KUNDE" id="0987654321">OWNER</object>
         <object type="KUNDE" id="0987654322">EMPOWERED</object>
      </references>
   </system>
   <data>
      <kokode>010101</kokode>
      <balance>100000</balance>
      <periods>
         <balance type="STMT">100000</balance>
         <balance type="INTEREST">999900</balance>
      </periods>
   </data>
</object>'
)

Any help much appreciated. I am in a great hurry to get this working.

Thx in advance
Lars Kjarsgaard

Answer this question