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 Kj?rsgaard