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

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