CODEWITHSUNDEEP
sqlsql-serverxml
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

SQL Server Where Clause Path for XML Value

Main problem is how to use a where clause in SQL for XML.

I need to return the row of XML where the "team" appears in.

You can view my example fiddle here: http://sqlfiddle.com/#!18/de221e/2

Table:

CREATE TABLE test
(
  id int,
  value xml
  )

  INSERT INTO test (id, value)
  VALUES 
  ('1', N'<?xml version="1.0" encoding="utf-16" standalone="yes"?> <Atts>
        <Att>
            <Name>test</Name>
        </Att>
                <Att>
            <Name>team</Name>
        </Att>
                <Att>
            <Name>test</Name>
        </Att>
        </Atts>'),
          ('2', N'<?xml version="1.0" encoding="utf-16" standalone="yes"?> <Atts>
        <Att>
            <Name>test</Name>
        </Att>
                <Att>
            <Name>test</Name>
        </Att>
                <Att>
            <Name>test</Name>
        </Att>
        </Atts>');

query:

select * from test
where value.value('(/Atts/Att/Name)[1]','varchar(max)') = 'team'

This doesn't return anything.

However if you do where clause on first name that appears in the XML it works e.g.

select * from test
where value.value('(/Atts/Att/Name)[1]','varchar(max)') = 'test'

returns:

| id |                                                                                             value |
|----|---------------------------------------------------------------------------------------------------|
|  1 | <Atts><Att><Name>test</Name></Att><Att><Name>team</Name></Att><Att><Name>test</Name></Att></Atts> |
|  2 | <Atts><Att><Name>test</Name></Att><Att><Name>test</Name></Att><Att><Name>test</Name></Att></Atts> |

Expected results is this query should return:

select * from test
where value.value('(/Atts/Att/Name)[1]','varchar(max)') = 'team'

| id |                                                                                             value |
|----|---------------------------------------------------------------------------------------------------|
|  1 | <Atts><Att><Name>test</Name></Att><Att><Name>team</Name></Att><Att><Name>test</Name></Att></Atts> |

Any ideas how I can return "team" if it appears in XML but isn't in the first

Upvotes: 1

Views: 121

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22157

It is better to use exist() method. It will check for the 'team' value regardless of its position. exist() Method (xml Data Type)

SQL

select * from test
where value.exist('/Atts/Att/Name[./text()="team"]') = 1;

Upvotes: 1

Hogan
Hogan

Reputation: 70513

It is the second one. You are looking at the first one.

This gives you the results you want

select * from test
where value.value('(/Atts/Att/Name)[2]','varchar(max)') = 'team'

Upvotes: 0

Related Questions