Cross Row Referential Integrity

In SS05 XML typed columns, is it possible to have cross row constraints and referential integrity?

Say I have a column with two schemas: Author and Book. Each has an ID. Each row in the table has only one author or book. Can I enforce that all Author ID are unique across all rows and that there is an author for every book?

Is it possible now? If yes, how? If no, is there a plan to add this feature in the future? Is there any work around now?

I am appending the script to demonstrate what I want to achieve.

Thanks in advance.

-

USE [pubs]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Drop the table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyXMLTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MyXMLTable]
GO

if exists (select * from sys.xml_schema_collections where name = N'XmlSchemaCollection')
DROP XML SCHEMA COLLECTION XmlSchemaCollection
GO

--Create the schema
CREATE XML SCHEMA COLLECTION XmlSchemaCollection AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.test.com/XmlTest"
xmlns ="http://www.test.com/XmlTest"
elementFormDefault="qualified"
attributeFormDefault="unqualified" >

<xs:element name="Author">
<xs:complexType>
<xs:sequence>
<xs:element name="auId" type="xs:long" />
<xs:element name="auName" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.test.com/XmlTest"
xmlns ="http://www.test.com/XmlTest"
elementFormDefault="qualified"
attributeFormDefault="unqualified" >

<xs:element name="Book">
<xs:complexType>
<xs:sequence>
<xs:element name="bookId" type="xs:long" />
<xs:element name="title" type="xs:string" minOccurs="0" />
<xs:element name="bookAuID" type="xs:long"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>' ;
GO

--Create the table
CREATE TABLE [dbo].[MyXMLTable](
i int primary key,
recType varchar(max),
[XMLData] xml (XmlSchemaCollection)
) ON [PRIMARY]
GO

DECLARE @.s varchar(2048)

-- insert records into Author table
SET @.s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest">
<xns:auId>1</xns:auId><xns:auName>Tom</xns:auName></xns:Author>'
INSERT INTO [dbo].[MyXMLTable] VALUES (1, 'Author', @.s)
SET @.s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest">
<xns:auId>2</xns:auId><xns:auName>Dick</xns:auName></xns:Author>'
INSERT INTO [dbo].[MyXMLTable] VALUES (2, 'Author', @.s)
SET @.s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest">
<xns:auId>3</xns:auId><xns:auName>Harry</xns:auName></xns:Author>'
INSERT INTO [dbo].[MyXMLTable] VALUES (3, 'Author', @.s)

-- insert records into Book table
SET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest">
<xns:bookId>1</xns:bookId>
<xns:title>Butterflies</xns:title>
<xns:bookAuID>1</xns:bookAuID>
</xns:Book>'
INSERT INTO [dbo].[MyXMLTable] VALUES (9, 'Book', @.s)
SET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest">
<xns:bookId>2</xns:bookId>
<xns:title>Tigers</xns:title>
<xns:bookAuID>3</xns:bookAuID>
</xns:Book>'
INSERT INTO [dbo].[MyXMLTable] VALUES (10, 'Book', @.s)
SET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest">
<xns:bookId>3</xns:bookId>
<xns:title>Elephants</xns:title>
<xns:bookAuID>2</xns:bookAuID>
</xns:Book>'
INSERT INTO [dbo].[MyXMLTable] VALUES (11, 'Book', @.s)
SET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest">
<xns:bookId>4</xns:bookId>
<xns:title>Eagles</xns:title>
<xns:bookAuID>3</xns:bookAuID>
</xns:Book>'
INSERT INTO [dbo].[MyXMLTable] VALUES (12, 'Book', @.s);

-- Table View with a join
WITH XMLNAMESPACES ('http://www.test.com/XmlTest' AS xns)
SELECT A.i, A.[XMLData].value('
(/xns:Book/xns:title)[1]', 'nvarchar(max)') as Title,
B.[XMLData].value('
(/xns:Author/xns:auName)[1]', 'nvarchar(max)') as [Author Name]
FROM dbo.MyXMLTable A inner join dbo.MyXMLTable B on
A.[XMLData].value('(/xns:Book/xns:bookAuID)[1]', 'int') =
B.[XMLData].value('(/xns:Author/xns:auId)[1]', 'int')
WHERE (B.[XMLData].exist('/xns:Author[xns:auId=3]')=1)

This may work, I haven't tried it:

Create a persisted computed column which promotes out the author id as the primary key for the table, and another persisted computed column which promotes out the bookId. Then create a 1:n PK-FK relationship between those two computed columns.

To create the computed columns you will have to create a UDF which wraps the XQuery invocation. SQL Server 2005 does not support XQuery directly in comptued column definitions or check constraints.

|||? Why do you want to put authors and books in the same column? This violates basic relational modeling fundamentals; I'd be very interested in knowing what you feel you'd gain by doing something like that. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Hemant K@.discussions.microsoft.com> wrote in message news:d06f449c-15af-4a81-96a9-f32a06aa5366@.discussions.microsoft.com... In SS05 XML typed columns, is it possible to have cross row constraints and referential integrity? Say I have a column with two schemas: Author and Book. Each has an ID. Each row in the table has only one author or book. Can I enforce that all Author ID are unique across all rows and that there is an author for every book? Is it possible now? If yes, how? If no, is there a plan to add this feature in the future? Is there any work around now? I am appending the script to demonstrate what I want to achieve. Thanks in advance. - USE [pubs]GO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO --Drop the tableif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyXMLTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[MyXMLTable]GO if exists (select * from sys.xml_schema_collections where name = N'XmlSchemaCollection')DROP XML SCHEMA COLLECTION XmlSchemaCollection GO --Create the schemaCREATE XML SCHEMA COLLECTION XmlSchemaCollection AS N'<?xml version="1.0" encoding="UTF-16"?><xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"targetNamespace="http://www.test.com/XmlTest" xmlns ="http://www.test.com/XmlTest" elementFormDefault="qualified" attributeFormDefault="unqualified" > <xs:element name="Author"><xs:complexType><xs:sequence><xs:element name="auId" type="xs:long" /><xs:element name="auName" type="xs:string" minOccurs="0" /></xs:sequence></xs:complexType></xs:element></xs:schema> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"targetNamespace="http://www.test.com/XmlTest" xmlns ="http://www.test.com/XmlTest" elementFormDefault="qualified" attributeFormDefault="unqualified" > <xs:element name="Book"><xs:complexType><xs:sequence><xs:element name="bookId" type="xs:long" /><xs:element name="title" type="xs:string" minOccurs="0" /><xs:element name="bookAuID" type="xs:long"/></xs:sequence></xs:complexType></xs:element></xs:schema>' ;GO --Create the tableCREATE TABLE [dbo].[MyXMLTable](i int primary key, recType varchar(max),[XMLData] xml (XmlSchemaCollection)) ON [PRIMARY]GO DECLARE @.s varchar(2048) -- insert records into Author tableSET @.s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest"><xns:auId>1</xns:auId><xns:auName>Tom</xns:auName></xns:Author>'INSERT INTO [dbo].[MyXMLTable] VALUES (1, 'Author', @.s)SET @.s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest"><xns:auId>2</xns:auId><xns:auName>Dick</xns:auName></xns:Author>'INSERT INTO [dbo].[MyXMLTable] VALUES (2, 'Author', @.s)SET @.s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest"><xns:auId>3</xns:auId><xns:auName>Harry</xns:auName></xns:Author>'INSERT INTO [dbo].[MyXMLTable] VALUES (3, 'Author', @.s) -- insert records into Book tableSET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest"><xns:bookId>1</xns:bookId><xns:title>Butterflies</xns:title> <xns:bookAuID>1</xns:bookAuID></xns:Book>'INSERT INTO [dbo].[MyXMLTable] VALUES (9, 'Book', @.s)SET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest"><xns:bookId>2</xns:bookId><xns:title>Tigers</xns:title> <xns:bookAuID>3</xns:bookAuID></xns:Book>'INSERT INTO [dbo].[MyXMLTable] VALUES (10, 'Book', @.s)SET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest"><xns:bookId>3</xns:bookId><xns:title>Elephants</xns:title> <xns:bookAuID>2</xns:bookAuID></xns:Book>'INSERT INTO [dbo].[MyXMLTable] VALUES (11, 'Book', @.s)SET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest"><xns:bookId>4</xns:bookId><xns:title>Eagles</xns:title> <xns:bookAuID>3</xns:bookAuID></xns:Book>'INSERT INTO [dbo].[MyXMLTable] VALUES (12, 'Book', @.s); -- Table View with a joinWITH XMLNAMESPACES ('http://www.test.com/XmlTest' AS xns)SELECT A.i, A.[XMLData].value('(/xns:Book/xns:title)[1]', 'nvarchar(max)') as Title,B.[XMLData].value('(/xns:Author/xns:auName)[1]', 'nvarchar(max)') as [Author Name]FROM dbo.MyXMLTable A inner join dbo.MyXMLTable B on A.[XMLData].value('(/xns:Book/xns:bookAuID)[1]', 'int') =B.[XMLData].value('(/xns:Author/xns:auId)[1]', 'int')WHERE (B.[XMLData].exist('/xns:Author[xns:auId=3]')=1)|||

Well, First I wanted to have a constraint that works across all the rows e.g. all books in all rows have unique book id and all authors in all rows have unique author id.

Then, since XML type is a schema collection, it can hold both authors and books in the same column to make my table generic. So, I thought may be I can extend the concept and create a PK-FK relationship between authors/books.

|||

Thank you John.

I was hoping that SQL Server 2005 and XML provide some support for cross-row constrains.