SQL Server XML Parsing

Question

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!

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
– ZLK, Commented Feb 16, 2016 at 23:41
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
– David Glasser, Commented Feb 16, 2016 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
– David Glasser, Commented Feb 16, 2016 at 23:49

Stuart Ainsworth · Accepted Answer · 2016-02-17 02:26:07Z

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

answered Feb 17, 2016 at 2:26

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gottfried Lesigang Over a year ago

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.

Collectives™ on Stack Overflow

SQL Server XML Parsing

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related