Parsing XML from returned field in SQL

Question

I have a field called xml that contains some data that I need to extract. The contents of the xml field look like this:

<p>
  <k>Attribute Name</k>
  <v>Attribute Value</v>
</p>
<p>
  <k>Attribute Name 2</k>
  <v>Attribute Value 2</v>
</p>
<p>
  <k>Attribute Name 3</k>
  <v>Attribute Value 3</v>
</p>
<p>... and so on ...</p>

Is there a way I can write my query to extract Attribute Value 2 in SQL? Is this a normal practice with vendors?

EDIT:

I'm able to query off the xml field using m.[xml].value('/p[2]','varchar(max)' but I cannot figure out a way to grab the direct neighbor of the node key I am looking for. For example, I want to grab <v>Attribute Value 2</v> but search off <k>Attribute Name 2</k>

I don't think it's `normal practice`, i think it's a `bad practice`. `sqlserver` is a database engine not a parsing engine. Even though, you should look a this question: https://stackoverflow.com/questions/15680259/parse-xml-in-sql-server — ADreNaLiNe-DJ, Jul 28 '17 at 13:15
Yes, this is ultimately a duplicate of [Parse XML in SQL Server](https://stackoverflow.com/questions/15680259/parse-xml-in-sql-server) — underscore_d, Jul 28 '17 at 13:27

Mitch · Answer 1 · 2017-08-15T15:02:00.740

1

Use xml.nodes('/p') as p(x) to retrieve all of the p elements, then use p.x.value(...) to retrieve the key and value.

If you are only looking to extract specific keys, use XPath to filter and retrieve the value: /p[k[1]/text() = "foo"]/v[1]/text()

edited Aug 15 '17 at 15:02

answered Jul 28 '17 at 13:49

Mitch

21,223
6
63
86

score 1 · Answer 2 · answered Aug 15 '17 at 11:13

You did not accept any answer yet, might be, that the existing answers did not really help...

If I get this correctly, you want to read the value (=<v>) to a given key (=<k>). Try it like this:

DECLARE @XML XML = 
  N'<p>
      <k>Attribute Name</k>
      <v>Attribute Value</v>
    </p>
    <p>
      <k>Attribute Name 2</k>
      <v>Attribute Value 2</v>
    </p>
    <p>
      <k>Attribute Name 3</k>
      <v>Attribute Value 3</v>
    </p>';

DECLARE @Search NVARCHAR(100)=N'Attribute Name 2';

SELECT @xml.value(N'(/p[k/text()=sql:variable("@Search")]/v/text())[1]',N'nvarchar(max)');

The key-name you are looking for is set to a variable @Search.

The XQuery is looking for a <p>, where there is a <k> which has got the text you are looking for.

Then the <v> below the <p> is read and returned.

LukStorms · Answer 3 · 2017-08-16T08:28:09.917

You can use the XQuery syntax to query the table directly and the get value out of the xml.

Example :

-- using a table variable for the example
declare @TestTable table (id int identity(1,1), [xml] XML);
insert into @TestTable ([xml]) values (N'<p>
  <k>Attribute Name</k>
  <v>Attribute Value</v>
</p>
<p>
  <k>Attribute Name 2</k>
  <v>Attribute Value 2</v>
</p>
<p>
  <k>Attribute Name 3</k>
  <v>Attribute Value 3</v>
</p>'),
(N'<p>
  <k>Attribute Name 4</k>
  <v>Attribute Value 4</v>
</p>');

DECLARE @AttributeName nvarchar(30) = N'Attribute Name 2';

select id, [xml].query('p[k/text() = sql:variable("@AttributeName")]/v').value('.','nvarchar(max)') as [value]
from @TestTable
where [xml].query('p[k/text() = sql:variable("@AttributeName")]').value('.','nvarchar(max)') != '';

Returns :

id value 
-- -------------------
1  Attribute Value 2

One should not use the *deep search* (with `//`) if there's no reason for this... Your `XQuery` can be done a bit simpler... — Shnugo, Aug 15 '17 at 11:17

Sᴀᴍ Onᴇᴌᴀ · Answer 4 · 2017-08-18T17:51:41.220

There are multiple ways to achieve this.

Filtering key with predicate on xquery expression

Use an xquery expression with a predicate to filter to <p> nodes that contain a <k> child node with the desired string literal (i.e. "Attribute Name 2") using the value() function:

SELECT m.xml.value('(/p[k/text()="Attribute Name 2"]/v/text())[1]',N'varchar(max)')
from m

See it demonstrated in this sqlfiddle.

And if there is a possibility of unicode characters in the data, prefix the xquery string with N and select the value as nvarchar(max):

SELECT m.xml.value(N'(/p[k/text()="Attribute Name 2"]/v/text())[1]',N'nvarchar(max)')
from m

Select key and value as fields and apply WHERE condition

Borrowing from the second query in this answer, CROSS APPLY can be used in a sub-query with .nodes() to select the text of the <k> and <v> nodes as fields using value().

Then in the outer-query, a WHERE condition can be added to restrict <p> nodes to only those with a <k> with text Attribute Name 2.

select [key], value
from (
    select x.value('(k/text())[1]','varchar(max)') as [key],
    x.value('(v/text())[1]','varchar(max)') as value
    FROM m
    CROSS APPLY xml.nodes('//p') as p(x)
) as Result
where [key] = 'Attribute Name 2'

See a demonstration of this in this SQL fiddle.

This could be simplified by removing the sub-query and just selecting the text from the <v> node where the text of the <k> node matches the search string:

SELECT x.value('(v/text())[1]','varchar(max)') as value
FROM m
CROSS APPLY xml.nodes('//p') as p(x)
WHERE x.value('(k/text())[1]','varchar(max)') = 'Attribute Name 2'

See this demonstrated in this SQL fiddle.

With bigger / more complex structures one should not shredd the whole lot into a derived table, just to use a `WHERE` at the the end. This filter should be part of an `XQuery`predicate... — Shnugo, Aug 15 '17 at 11:07

score -1 · Answer 5 · answered Jul 28 '17 at 13:21

Maybe something like this:

DECLARE @xml xml
SET @xml =
'<p>
  <k>Attribute Name</k>
  <v>Attribute Value</v>
<p>
<p>
  <k>Attribute Name 2</k>
  <k>Attribute Value 2</k>
</p>
<p>
  <k>Attribute Name 3</k>
  <k>Attribute Value 3</k>
<p>
<p>... and so on ...</p>'

SELECT
      a.value('@k[1]', 'varchar(max)') 
FROM @xml as a

Parsing XML from returned field in SQL

5 Answers5

Filtering key with predicate on xquery expression

Select key and value as fields and apply WHERE condition