-- ============================================================
-- 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')



VN:F [1.9.22_1171]
Rating: 7.7/10 (32 votes cast)
VN:F [1.9.22_1171]
Rating: +3 (from 7 votes)
SQLXML : How to Use SQL Server XML Function exist(), 7.7 out of 10 based on 32 ratings
Be Sociable, Share!