Feeds:
Posts
Comments

Posts Tagged ‘SELECT @X.exist’

Quick one today:

XML DML is interesting feature of Sql Server. It is vast and has lots of features in manipulating XML files and its elements & attributes. Today, we’ll look at exists function to check if a particular element exists or an element with a specific attribute exists.

Let’s use this below XML as a sample:

DECLARE   @X XML = '

                Marco
                Polo

                Hello

                Hello

        '

 

XMLDML_Exists_Sample.PNG

Let’s look at a way to check if

  • If a “ParenetElement” element with “Style” attribute exists
  • If a “Child” element with “Polo” as element text value exists

Check at Attribute Level

--
-- Check if ParentElement with Attribute value of "Style" exists
--
DECLARE   @X XML = '

                Marco
                Polo

                Hello

                Hello

        '
SELECT @X.exist('(/Root/ParentElement[@Att1="Style"])') AS [Exists_Or_Not]
GO

XMLDML_Exists

Check Element Text Level

--
-- If a "Child" element with "Polo" as element text value exists
--
DECLARE   @X XML = '

                Marco
                Polo

                Hello

                Hello

        '
SELECT @X.exist('(/Root/ParentElement/Child2[(text()[1] = "Polo" )])') AS [Polo_Exists?]
GO

XMLDML_Exists2.PNG

Hope this helps,
_Sqltimes
Advertisements

Read Full Post »