-- ============================================================
-- 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")]')
Share :
  • Digg
  • del.icio.us
  • Google
  • description
  • StumbleUpon
  • Technorati
  • TwitThis

Related posts:

  1. SQLXML : How to Join Multiple XML Snippets (using query() and UNION ALL)
  2. SQLXML : How to Work With XML Elements (or Nodes) in SQL Server
  3. View or function ’sys.dm_exec_sessions’ has more column names …
  4. SQLXML : How to Create an XML Schema
  5. Valid SQLXML XSD Data Types, and Sample SQL Server XML Schemas
  6. SQL Server XML Red Gate Ebook, XQuery Labs