-- ============================================================
-- 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('/ninjaElement[text()=sql:variable("@value")]')
VN:F [1.9.22_1171]
Rating: 7.5/10 (41 votes cast)
VN:F [1.9.22_1171]
Rating: +7 (from 11 votes)
SQLXML : How to Use SQL Server XML Function exist(), 7.5 out of 10 based on 41 ratings  
Be Sociable, Share!
  • Tweet