今度は、XML型のScoreにチェック制約をつける例を書きます。
まずは、XML型を持つテーブルの定義です。
| 列名 |
型 |
| StudentId |
decimal(8, 0) |
| Score |
xml |
またチェック制約で参照するTestsテーブルの定義とデータです。
| TestId(decimal(10,0)) |
TestName(varchar(50)) |
| 20071001 |
前期中間 現代文 |
| 20071002 |
前期中間 古典 |
| 20072001 |
前期期末 古典 |
| 20072002 |
前期期末 数学 |
ここから以下の操作をしていきます。
- チェックを行う関数の作成
チェック内容を以下の条件にします。
- Tests.TestIdの件数とSocre@TestIdで値の重複しない件数が一致すること
- Tests.TestIdとScore@TestIdが同一件数であること。
これを、Transact-SQLで記述すると以下のようになります。
declare @var xml;
declare @count int;
declare @TestIdCountNotDis int;
declare @TestIdCount int;
set @var = '<Score TestId="20071001">89</Score><Score TestId="20071001">89</Score><Score TestId="20071002">87</Score><Score TestId="20072001">95</Score><Score TestId="20072002">100</Score>';
select
@count = (select count(*) from Tests),
@TestIdCountNotDis = count(@var.query('distinct-values(/Score/@TestId)'))
from
Tests
where
@var.exist('/Score[@TestId=sql:column("TestId")]') = 1
select
@TestIdCount = count(t.value('@TestId','decimal(8,0)'))
from
@var.nodes('/Score' ) D(t) ;
select @count as '@count', @TestIdCountNotDis as '@TestIdCountNotDis',@TestIdCount as '@TestIdCount';
| @count |
@TestIdCountNotDis |
@TestIdCount |
| 4 |
4 |
5 |
上記例では、Score@TestIdに"20071001"を持つデータが2つあるので、上記のように、
count = @TestIdCountNotDis < @TestIdCount
となります。
@varの値を以下のようにすると全部が4になります。
set @var = '<Score TestId="20071001">89</Score><Score TestId="20071002">87</Score><Score TestId="20072001">95</Score><Score TestId="20072002">100</Score>';
| @count |
@TestIdCountNotDis |
@TestIdCount |
| 4 |
4 |
4 |
以下のように、Score@TestIdが存在しないデータがあると、 count > @TestIdCountNotDis = @TestIdCountとなります。
set @var = '<Score>89</Score><Score TestId="20071002">87</Score><Score TestId="20072001">95</Score><Score TestId="20072002">100</Score>';
| @count |
@TestIdCountNotDis |
@TestIdCount |
| 4 |
3 |
3 |
さてこの処理を実際に関数として登録します。
CREATE FUNCTION [dbo].[CheckTestId](@var xml) returns bit
AS
BEGIN
Declare @count int ;
Declare @TestIdCount int ;
Declare @ans bit ;
Declare @TestIdCountNotDis int ;
set @ans = 0 ;
select
@count = (select count(*) from Tests),
@TestIdCountNotDis = count(@var.query('distinct-values(/Score/@TestId)'))
from
Tests
where
@var.exist('/Score[@TestId=sql:column("TestId")]') = 1
select
@TestIdCount = count(t.value('@TestId','decimal(8,0)'))
from
@var.nodes('/Score' ) D(t)
if( @TestIdCount = @TestIdCountNotDis)
begin
if( @count = @TestIdCount )
begin
set @ans = 1 ;
end
end
return @ans ;
end
distinct-valuesは重複しない値をとるという指定です。
sql:column("TestId")はXQueryの中でRDBのTests.TestIdと連結する指定です。
登録した後に実行して、結果を確認してみます。
declare @var xml;
set @var = '<Score TestId="20071001">89</Score><Score TestId="20071002">87</Score><Score TestId="20072001">95</Score><Score TestId="20072002">100</Score>';
select dbo.CheckTestId(@var);
declare @var xml;
set @var = '<Score TestId="20071001">89</Score><Score TestId="20071001">89</Score><Score TestId="20071002">87</Score><Score TestId="20072001">95</Score><Score TestId="20072002">100</Score>';
select dbo.CheckTestId(@var);
declare @var xml;
set @var = '<Score>89</Score><Score TestId="20071002">87</Score><Score TestId="20072001">95</Score><Score TestId="20072002">100</Score>';
select dbo.CheckTestId(@var);
作成した関数を使用してxml型に対するチェック制約を作成します。
- XML型の列を選択し、右クリックをして制約のチェックを選択する。
-
- 追加ボタンを押下ののち、「式」に「dbo.CheckTestId(Score) = 1」を入力し、ダイアログを閉じ変更を反映させる。
上記手順をSQLにすると以下のとおりです。
ALTER TABLE dbo.ScoreXML ADD CONSTRAINT
CK_ScoreXML CHECK (dbo.CheckTestId(Score) = 1)
#勉強会でうまくいかなぁいっと思っていたのは、
#TestsのTestIdとカウントが一致しなきゃいけない条件をつけたことを忘れていたからでした…orz