Pages

SQL XMl Using SQL Server XMl Exists() Function to fing Element in XML

Thursday, October 27, 2011
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
-- ============================================================
-- sample code on using the SQL Server xml method exist()
-- several samples shown
-- Donabel Santos
-- ============================================================
 
DECLARE @xmlSnippet XML
DECLARE @id SMALLINT
DECLARE @value VARCHAR(20)
 
SET @xmlSnippet =
'
<ninjaElement id="1">SQL Server Ninja</ninjaElement>
<ninjaElement id="2">SharePoint Ninja</ninjaElement>
<ninjaElement id="3">ASP.NET Ninja</ninjaElement>
'
 
-- this is what we will look for
SET @id    = 2
SET @value ='SQL Server Ninja'
 
-- note exist() will return only either :
-- 1 (true) or 0 (false)
 
-- check if a node called ninjaElement exists
-- at any level in the XML snippet
SELECT @xml.exist('//ninjaElement')
 
-- check if a node called bar exists
SELECT @xml.exist('//bar')
 
-- check if attribute id exists anywhere
SELECT @xml.exist('//@id')
 
-- check if attribute id exists within a ninjaElement tag
SELECT @xml.exist('//ninjaElement[@id]')
 
-- check if the id attribute equals to what we saved
-- in the @id variable
SELECT @xml.exist('/ninjaElement[@id=sql:variable("@id")]')
 
-- check if the node text equals to what
-- we saved in the @value variable
SELECT @xml.exist('/ninjaElement1')

No comments:

Post a Comment

Post Your Comment...