«
»

There are a few ways how to write Xpath expression. Not every expression may return expected data. Here is a review and Subtree cost in execution plan (SQL Server 2008 R2, 64bit) for each tested method. Xml contains 100 nodes, some of them are empty:

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156

declare @x xml
set @x = N'<root>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>			
		  </root>'
 
--With defined singletons:
--First node
select @x.value(N'(root[1]/item[1]/text()[1])', N'UniqueIdentifier') 
--Select estimated subtree cost: 0.42
--If in the element is empty value, it is converted sucesfully to Null due text() 
--at the end of Xpath expression.
--more here http://www.xquery.com/tips_and_tricks/using_text_in_XPath.html
 
select @x.value(N'(root[1]/item[1]/text()[1])[1]', N'UniqueIdentifier') 
--Select estimated subtree cost: 0.42
--If in the element is empty value, it is converted sucesfully to Null due text()
--at the end of Xpath expression.
 
select @x.value(N'(root[1]/item[1])', N'UniqueIdentifier') 
--Select estimated subtree cost: 1.23 
--If in the element is empty value, the statement fails with message 
--"Conversion failed when converting from a character string to uniqueidentifier."
 
--Node 93:
select @x.value(N'(root[1]/item[93]/text()[1])', N'UniqueIdentifier') 
--Select estimated subtree cost: 1.68
--This Subtree cost stay the same with node 990.
 
select @x.value(N'(root[1]/item[93]/text()[1])[1]', N'UniqueIdentifier') 
--Select estimated subtree cost: 1.69
 
select @x.value(N'(root[1]/item[93])', N'UniqueIdentifier') 
--Select estimated subtree cost: 4.31
 
--------------------------------------------------------------------------
 
--There are not defined singletons, it may cause not expected behaviour.
--First node
select @x.value(N'(root/item/text())[1]', N'UniqueIdentifier') 
--Select estimated subtree cost: 0.005 
--May return unexpected data, more bellow.
 
select @x.value(N'(root/item)[1]', N'UniqueIdentifier') 
--Select estimated subtree cost: 1.02
--If in the element is empty value, the statement fails with message 
--"Conversion failed when converting from a character string to uniqueidentifier."
 
--Node 93:
select @x.value(N'(root/item/text())[93]', N'UniqueIdentifier') 
--Select estimated subtree cost: 1.004 
--XPath selects only nodes with text. This XPath will contains Null, because 
--93th node does not exist.
 
select @x.value(N'(root/item)[93]', N'UniqueIdentifier') 
--Select estimated subtree cost: 55.405
--If in the element is empty value, the statement fails with message 
--"Conversion failed when converting from a character string to uniqueidentifier."
Share

This entry was posted by Jan Novak on January 3, 2012 at 20:49:19 under T-SQL. You can leave a response, or trackback from your own site. Follow any responses to this entry through the RSS 2.0 feed.

Leave a Reply