0

I am trying to query the XML below in a SQL select statement to find the type for book3.

<Book>
  <Title>Book1</Title>
  <Type>fiction</Type>
  <Cost>9.99</Cost>
</Book>

<Book>
  <Title>Book2</Title>
  <Type>non-fiction</Type>
  <Cost>5.99</Cost>
</Book>

<Book>
  <Title>Book3</Title>
  <Type>fiction</Type>
  <Cost>14.99</Cost>
</Book>

<Book>
  <Title>Book4</Title>
  <Type>non-fiction</Type>
  <Cost>19.99</Cost>
</Book>
...

I am trying to select the title "book 3" in my select statement by searching for this as a text, and the type which follows i.e book3,fiction

So far I can perform a select by the index as below but I really want to find the node by incorporating some kind of text='book 3' statement.

SELECT XML.value('(/*//Book/Title/node())[3]', 'nvarchar(max)') as 'result'

I am not familiar on how to do this (I'm quite new to this approach)

Can anyone give me some pointers on how to do this? Or if there are any good resources online I can read up on (I can only find bits on pieces on how to do this in SQL)

Thanks!

user7575401
  • 57
  • 1
  • 4
  • Possible duplicate of [How can I query a value in SQL Server XML column](https://stackoverflow.com/questions/10344553/how-can-i-query-a-value-in-sql-server-xml-column) – SaggingRufus Dec 20 '17 at 13:17
  • The actual used database management system should be mentioned or added to tags. Just to make sure if we a really talking about SQL Server. – Markus Dec 20 '17 at 13:18
  • Try `//Book[Title="Book3"]/Type` – Andersson Dec 20 '17 at 13:30
  • This one is slightly different to that thread SaggingRufus, in this case I want to search by the text rather than the node index. – user7575401 Dec 20 '17 at 13:40
  • Andersson was right it was entering that text part that I needed to do! Thanks so much! – user7575401 Dec 20 '17 at 13:41

1 Answers1

0

This query should work: //Book[Title="Book3"]/Type/text()

Tip: this tool might be useful to test your XPATH queries: http://www.xpathtester.com/xpath

t1gor
  • 1,244
  • 12
  • 25