-- ============================================================
-- 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")]')
Related posts:
- SQLXML : How to Join Multiple XML Snippets (using query() and UNION ALL) ...
- SQLXML : How to Work With XML Elements (or Nodes) in SQL Server ...
- View or function ’sys.dm_exec_sessions’ has more column names … ...
- SQLXML : How to Create an XML Schema ...
- Valid SQLXML XSD Data Types, and Sample SQL Server XML Schemas ...
- SQL Server XML Red Gate Ebook, XQuery Labs ...
Filed under:
SQLXML
Hi, how can use xml in join clause? e.g. I have a xml variable and want to make something like this
table t1
join @XML.nodes(’/root/id’) as ParamValues(ID)
on t1.id=ParamValues.ID.value(’.',’int’)
??
great post…saved me a lot of time!!!!
Found a small problem … you declare:
DECLARE @xmlSnippet XML
And then assign:
SET @xmlSnippet =
But in the queries you refer to @xml, which doesn’t exist:
SELECT @xml.exist('//ninjaElement')
Oh, whoa, this saved me from a few gray hairs. I was trying to get the value of an XML element that existed in some columns, but not all, and adding it with similar elements in the same column (quantity1, quantity2, etc). In some instances, quantity1 didn’t exist, yet quantity2 or quantity3 would, but the whole addition statement would be null. Now I check if the element exists; if it doesn’t, the case statement returns 0; otherwise, it returns the value of the element.
Thank you!