SQL Server XML Function exist()

  07.DECLARE @xml XML

     08.DECLARE @id SMALLINT
     09.DECLARE @value VARCHAR(20)
     10.  
     11.SET @xml=
     12.'
     13.<ninjaElement id="1">SQL Server Ninja</ninjaElement>
     14.<ninjaElement id="2">SharePoint Ninja</ninjaElement>
     15.<ninjaElement id="3">ASP.NET Ninja</ninjaElement>
     16.'
     17.  
     18.-- this is what we will look for
     19.SET @id    = 2
     20.SET @value ='SQL Server Ninja'
     21.  
     22.-- note exist() will return only either :
     23.-- 1 (true) or 0 (false)
     24.  
     25.-- check if a node called ninjaElement exists
     26.-- at any level in the XML snippet
     27.SELECT @xml.exist('//ninjaElement')
     28.  
     29.-- check if a node called bar exists
     30.SELECT @xml.exist('//bar')
     31.  
     32.-- check if attribute id exists anywhere
     33.SELECT @xml.exist('//@id')
     34.  
     35.-- check if attribute id exists within a ninjaElement tag
     36.SELECT @xml.exist('//ninjaElement[@id]')
     37.  
     38.-- check if the id attribute equals to what we saved
     39.-- in the @id variable
     40.SELECT @xml.exist('/ninjaElement[@id=sql:variable("@id")]')
     41.  
     42.-- check if the node text equals to what
     43.-- we saved in the @value variable
     44.SELECT @xml.exist('/ninjaElement[text()=sql:variable("@value")]')
     45.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s