CODEWITHSUNDEEP
xmlsql-server-2008
joanna
joanna

Reputation: 773

SQL Server XML exist()

I have some problems using the exist() and value() methods in SQL Server 2008.

My XML looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<library>
    <branches>
        <branch>
            <codelib>1</codelib>
            <name>Campus</name>
        </branch>
        <branch>
            <codelib>2</codelib>
            <name>47th</name>
        </branch>
        <branch>
            <codelib>3</codelib>
            <name>Mall</name>
        </branch>              
    </branches>
    <books>
        <book type="SF">
            <codb>11</codb>
            <title>Robots</title>
            <authors>
                <author>author1 robots</author>
                <author>author2 robots</author>
            </authors>
            <price>10</price>
            <stocks>
                <branch codelib="1" amount="10"/>
                <branch codelib="2" amount="5"/>
                <branch codelib="4" amount="15"/>
            </stocks>
            <from>20</from>
            <to>30</to>
        </book>
        <book type="poetry">
            <codb>12</codb>
            <title>Poetry book</title>
            <authors>
                <author>AuthorPoetry</author>
            </authors>
            <price>14</price>
            <stocks>
                <branch codelib="1" amount="7"/>
                <branch codelib="2" amount="5"/>
            </stocks>
            <from>25</from>
            <to>40</to>
        </book>       
        <book type="children">
            <codb>19</codb>
            <title>Faitytales</title>
            <authors>               
                <author>AuthorChildren</author>             
            </authors>
            <price>20</price>
            <stocks>
                <branch codelib="1" amount="10"/>
                <branch codelib="3" amount="55"/>
                <branch codelib="4" amount="15"/>
            </stocks>
            <from>70</from>
            <to>75</to>
        </book>       
        <book type="literature">
            <codb>19</codb>
            <title>T</title>
            <authors>
                <author>A</author>                
            </authors>
            <price>17</price>
            <stocks>
                <branch codelib="1" amount="40"/>
            </stocks>
            <from>85</from>
            <to>110</to>
        </book>
    </books>
</library>

Given this XML, I have to write a SELECT clause that will use query(), value() and exist() 2 times each, minimum. I can't even use query() and exist() in the same SELECT, as it appears that the WHERE clause has no effect whatsoever.

For example, I want to retrieve all the <branch> elements that are children of the book with the type SF, but the select statement

  declare @genre varchar(15)
  set @genre = 'SF'
  SELECT XMLData.query('//branch') from TableA
  WHERE XMLData.exist('//book[./@type = sql:variable("@genre")]') = 1

retrieves all the <branch> elements, not just the ones from the targeted book. I can't figure out what's wrong with my select. Also, I would appreciate a small example with query(), exist() and value() in the same select (is it possible to have nested select statements in sql xml?)

Upvotes: 10

Views: 37589

Answers (3)

NuckingFuts
NuckingFuts

Reputation: 31

It's more efficient with XML to filter using the cross apply operator to filter to the required node then select the query from the returned nodes. To query the child nodes you also need to include the root . in the query so in this case .//branch rather than //branch. 

declare @genre varchar(15) = 'SF'
select l.query('.//branch') from TableA
cross apply XmlData.nodes('library/books/book[@type=sql:variable("@genre")]') n (l)

You can still add the exists clause if you want but this will actually add additional unnecessary overhead

WHERE XMLData.exist('//book[./@type = sql:variable("@genre")]') = 1

Hope this helps. D

Upvotes: 3

Asher
Asher

Reputation: 1026

The XML you supplied does not lend itself to an exist statement. If you had multiple XML statements and needed to find the one where it contained some value, then the statement would have been more relevant.

The where clause you supplied just checks if the condition exists and if it does, selects all the branches elements, not just the one where the condition is true. For example, the following (obviously) does not return anything:

SELECT @xmldata.query('//branch') from TableA
 WHERE @xmldata.exist('//book[./@type = "BLAH"]') = 1

But here is something to show you can use all three in one select statement.

SELECT T.c.query('./title').value('.', 'varchar(250)') as title, 
       T.c.exist('.[@type eq "SF"]') as IsSF
  from @xmldata.nodes('//book') T(c)

Upvotes: 4

marc_s
marc_s

Reputation: 754538

Well, your XPath expression here is the "culprit":

query('//branch')

This says: select all <branch> nodes from the entire document. It is just doing what you're telling it to do, really....

What's wrong with this query here??

SELECT 
    XMLData.query('/library/books/book[@type=sql:variable("@genre")]//branch')
FROM dbo.TableA

That would retrieve all the <branch> subnodes for the <book> node that has type="SF" as an attribute....

What are you trying to achieve with your query(), exist() and value() all in the same statement?? Quite possibly, it can be done a lot easier....

Also: I think you're misinterpreting what .exist() in SQL Server XQuery does. If you have your statement here:

 SELECT (some columns)
 FROM dbo.TableA
 WHERE XMLData.exist('//book[@type = sql:variable("@genre")]') = 1

you're basically telling SQL Server to retrieve all rows from dbo.TableA where the XML stored in XMLData contains a <book type=.....> node - you are selecting rows from the table - NOT applying a selection to the XMLData column's content...

Upvotes: 8

Related Questions