Geek Answers Handbook

SQL Server XML exists ()

I have some problems with the use of methods exist()and value()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 sentence SELECTthat I will use query(), value()and exist()2 times each, at least. I can not even use query(), and exist()in the same SELECT, as seems to be the proposal WHEREhas no effect.

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

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

<branch>, . , . , query(), exist() value() select ( select sql xml?)

+5
3

, XPath "":

query('//branch')

: <branch> . , , , ....

?

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

<branch> <book> node, type="SF" ....

query(), exist() value() ? , ....

: , , .exist() SQL Server XQuery. :

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

, SQL Server dbo.TableA, XML, XMLData, <book type=.....> node - - NOT XMLData...

+7

XML, , exist. XML , - , .

where, , , , , branches, , . , () :

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

, select.

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

XML cross apply, node, . , root. . //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)

exists, ,

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

, . D

0

More articles:


All Articles