0
DECLARE @xml xml=' <?xml version="1.0" encoding="UTF-8"?><text>
              <table>
                 <thead>
                    <tr>
                       <th>a</th>
                       <th>b</th>
                       <th>c</th>
                       <th>d</th>
                       <th>e</th>
                       <th>f</th>
                    </tr>
                 </thead>
                 <tbody>
                    <tr>
                       <td>testa1</td>
                       <td>testb1</td>
                       <td>testc1</td>
                       <td>testd1</td>
                       <td>teste1</td>
                       <td>testf1</td>
                    </tr>
                    <tr>
                      <td>testa1</td>
                       <td>testb1</td>
                       <td>testc1</td>
                       <td>testd1</td>
                       <td>teste1</td>
                       <td>testf1</td>
                    </tr>
                 </tbody>
              </table>
           </text>'

SELECT 
    T.c.value('(tbody/tr/td)[1]','VARCHAR(100)') AS a
FROM 
    @xml.nodes('text/table[thead[tr[th="b"]]]') AS t(c)

This query selects only the first body contents from xml.

Here I want to select only the values under header b

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Saravana Kumar
  • 11
  • 1
  • 2
  • What **RDBMS** are you using? Please add a relevant tag, like `mysql`, `postgresql`, `oracle`, `db2`, `sql-server` since those database systems all support XML - yet all slightly differently ... – marc_s Feb 18 '15 at 14:44

1 Answers1

0

Modifying the xpath as follows will do it:

 SELECT T.c.value('/text/table/tbody/tr/td[position()=2]','VARCHAR(100)') AS a

Explanation:

Selects the second td element among a list of siblings.

Note:

Combine that with this SO answer to get:

    SELECT T.c.value('/text/table/tbody/tr/td[position() = count(/text/table/tbody/tr/th[text() = "b"]/preceding-sibling::*)+1]','VARCHAR(100)') AS a
Community
  • 1
  • 1
collapsar
  • 17,010
  • 4
  • 35
  • 61