In your XML file (or variable), do you need to know if a certain element exists?
Use Case for exist()
Why would you ever need to do this? Perhaps you are reading in an XML file and you are not sure of the content. It could be one type of XML or the other. If it is type A, you will want to shred it into one set of tables, but if it is type B you will shred it into another set of tables. You therefore could import the XML file into a variable and use exist().
For information on how to read an XML file into a variable, have a look at this post SQL Server XML Shredding Part 2.
Consider the following code example. Here we are focusing on the exist() method, and not on reading an external XML file into a variable. We made a few minor adjustments to the xml from the previous post.
DECLARE @XML XML SET @XML = ' <root> <row1> <value>Apples</value> </row1> <row2> <value>Oranges</value> <amount>2</amount> </row2> </root> '; DECLARE @elementfound bit; SELECT @elementfound = @XML.exist('/root/row1[value="Apples"]'); PRINT @elementfound; SELECT @elementfound = @XML.exist('/root/row2/amount'); PRINT @elementfound; SELECT @elementfound = @XML.exist('/root/mysteryelement'); PRINT @elementfound;
The result is two ones and a zero, which is true, true and false That is not difficult. We got the right answer. Notice that we can check for the existence of an element, and we can check for data in an element.