-- ============================================================
-- 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')
No related posts.
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!
Good Sample. In the select statement,
SELECT @xml.exist(‘/ninjaElement[@id=sql:variable("@id")]‘)
can I give the condition itself in a variable? Have you tried out?
[...] SQLXML : How to Use SQL Server XML Function exist() [...]
yeah i did. unfortunately that doesn’t work.
[...] SQLXML : How to Use SQL Server XML Function exist() [...]
Passing in the whole criteria to .exist() as a variable doesn’t work as she said. What does work is building the SQL for use in a call to sp_ExecuteSQL. It’s the only way around it I’ve found. I have a generic content table that has an Xml column to hold specific content properties serialized from our C# app. With that change from multiple specific content tables to one generic content table still comes the desire to filter and sort on values in the Xml column for any given content type as known by the business layer of the app. The idea is to pass to a ReadContentByCriteria an optional parameter that can be used in an .exist() on ContentPropertiesXml column. I was dismayed and annoyed at the inflexibility in having to have a literal or sql:variable[] as the main choices. So I ended up concatenating the Results temp table with @PropertiesXQuery as part of the WHERE clause. It’s a hack but it works.