0

I have a block of XML in a column of type xml in SQL Server database that looks like this:

<p>
  <k>field</k>
  <v>user</v>
</p>
<p>
  <k>action</k>
  <v>delete+set</v>
</p>
<p>
  <k>prompt</k>
  <v>smith</v>
</p>

I have no control over how the XML is put into the database, I can only query it.

I would like to write a select statement that returns "smith" - the value in the v tag that is nested within a element that contains a k element with a value of "prompt".

The name of this column is "apple" and the table is "rule".

Any help would be appreciated.

Thanks!

David Glasser
  • 31
  • 4
  • http://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008 Check this answer. – FZE Feb 16 '16 at 23:34
  • You're looking for something like `SELECT A.B.value('v[1]','NVARCHAR(255)') FROM tblName CROSS APPLY col.nodes('/p') A(B) WHERE A.B.value('k[1]','NVARCHAR(255)') = 'prompt'` but it depends on what other stuff is in the XML. – ZLK Feb 16 '16 at 23:41
  • FZE - that returns all the values, I just want one. – David Glasser Feb 16 '16 at 23:43
  • ZLK - that is the entire XML. I'm not sure what the A.B. means. I've updated my post to mention the name of the field is called "apple". – David Glasser Feb 16 '16 at 23:45
  • ZLK got it. SELECT A.B.value('v[1]','NVARCHAR(255)') FROM [rule] CROSS APPLY xml.nodes('/p') A(B) WHERE A.B.value('k[1]','NVARCHAR(255)') = 'prompt' – David Glasser Feb 16 '16 at 23:49
  • The A.B. is just an alias for the nodes (given in the CROSS APPLY here). – ZLK Feb 16 '16 at 23:50

1 Answers1

1

Another way to skin the cat.

DECLARE @x XML = '<p>
  <k>field</k>
  <v>user</v>
</p>
<p>
  <k>action</k>
  <v>delete+set</v>
</p>
<p>
  <k>prompt</k>
  <v>smith</v>
</p>'

SELECT @X.value('((//k)[text()="prompt"]/../v)[1]', 'varchar(100)')
Stuart Ainsworth
  • 12,792
  • 41
  • 46
  • 1
    And this is a shorter way to skin the cat: `.value('(/p[k="prompt"]/v)[1]', 'varchar(100)')`. Should be faster to, because there is no back and forth navigation. – Shnugo Feb 17 '16 at 13:43