CODEWITHSUNDEEP
sql-serverxml
David Glasser
David Glasser

Reputation: 31

SQL Server XML Parsing

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!

Upvotes: 0

Views: 124

Answers (1)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

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)')

Upvotes: 1

Related Questions