In the past few sessions of XML Workshop, we had a good discussion about schema validation. We saw that, by defining a SCHEMA, we could strictly validate the data being assigned to an XML column or variable. When a schema is bound to a column or variable, the XML value should EXACTLY match the schema definition. This is what we need almost always.
There are times when we need to allow data with different XML structures. Let us take an example. Assume that we are working on a billing application and we need to store the payment details. Our application supports the following payment methods: Check, Cash and Credit Card. Based on the payment method, we will have to store additional information into the tables. If it is Check, then we need to store the Bank Name, Branch and Check Number. If the payment method is Cash then we need to store the Currency and the denomination. If the payment is made by Credit Card, then we need to store Card Type, Number, Expiry date etc.
The XML that our application should support are the following.
Cash Payment
1
2
3
4
5
6
7
8
9
10
11
Payment By Check
1
2
3
4
5
6
7
8
Payment By Credit Card
1
2
3
4
5
6
7
8
9
How do we define a schema which supports all these different XML structures? Well, there are many ways to do it. The easiest way is to use the xsd:choice sequence indicator.
Step 1 - Define the Payment Method
Let us start defining the schema. The first step is to create the Payment Method enumeration.
1
2
3
4
5
6
7
Step 2 - Define Cash Details
Now let us define the schema for the cash payment details.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Step 3 - Define Check Details
Here is the schema for the Check Details
1
2
3
4
5
6
7
Step 4 - Define Credit Card Details
And finally, let us define the schema for Credit Card Details
1
2
3
4
5
6
7
8
Step 5 - The final schema
Here is the complete schema. [code]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Creating the Schema Collection
Let us create the schema collection now. [code]
1 CREATE XML SCHEMA COLLECTION PaymentType
2 AS
3 '
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Testing the schema
We have created the schema that we needed. Now it is time to test it. Let us try to assign the different XML data that we discussed earlier and see if SQL Server 2005 validates them correctly. [1, 2, 3]
1 DECLARE @x XML(PaymentType)
2 SET @x = '
3
4
5
6
7
8
9
10
11
12 '
1 DECLARE @x XML(PaymentType)
2 SET @x = '
3
4
5
6
7
8
9
10
11 '
1 DECLARE @x XML(PaymentType)
2 SET @x = '
3
4
5
6
7
8
9
10
11
12
13
14 '
Conclusions
This session of XML Workshop focussed on explaining how to create variable content containers. The schema that we defined in this session, supports 3 different XML structures. Based on the payment type, a different XML structure can be assigned to the XML variable or column bound to the schema that we defined. You can find the other articles in this series here.