How to query SQL Server XML column

Question

I have a table which contains a XML column and I need to get a value from the XML.

<ArrayOfItem>
  <Item>
    <Key>Member_Claim_Id</Key>
    <Value>1802538</Value>
  </Item>
  <Item>
    <Key>Reverify</Key>
    <Value>0</Value>
  </Item>
  <Item>
    <Key>RequestNumber</Key>
    <Value>First Request</Value>
  </Item>
</ArrayOfItem>

Sometimes Reverify key will be present in the XML document, and other times it won't. The document can contain other key / value pairs as well.

But RequestNumber key / value pair will always be present, but it might be the second or third key / value item in the document. So I could have:

<ArrayOfItem>
  <Item>
    <Key>Member_Claim_Id</Key>
    <Value>1802538</Value>
  </Item>  
  <Item>
    <Key>RequestNumber</Key>
    <Value>First Request</Value>
  </Item>
</ArrayOfItem>

Currently I am using this:

SELECT TOP 10 * 
FROM dbo.myTable
WHERE Parameters.value('(/ArrayOfItem/Item/Value)[2]', 'varchar(max)') LIKE '%revision%'
ORDER BY Id DESC

But I was assuming that RequestNumber was always the 2nd Key/Value Item in the document, but I just learned that that is not always the case.

Let's say the table looks like:

CREATE TABLE dbo.myTable
(
    Parameters XML NOT NULL,
    Field1 VARCHAR(50) NULL
)

INSERT INTO dbo.myTable (Parameters, Field1)
VALUES
(   '<ArrayOfItem>
  <Item>
    <Key>Member_Claim_Id</Key>
    <Value>1802538</Value>
  </Item>
  <Item>
    <Key>Reverify</Key>
    <Value>0</Value>
  </Item>
  <Item>
    <Key>RequestNumber</Key>
    <Value>First Request</Value>
  </Item>
</ArrayOfItem>', -- XMLParameters - xml
    'myText'  -- Field1 - varchar(50)
    )

and I want the value of /ArrayOfItem/Item/Value where the key is RequestNumber.

Thank you for your help.

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Feb 01 '22 at 15:42
Thank you @YitzhakKhabinsky! I think I hit all of that except the version of SQL Server. We are on 2019. Also, I didn't provide an insert statement. I'll do that. — David.Warwick, Feb 01 '22 at 15:47

score 3 · Accepted Answer · edited Feb 02 '22 at 01:19

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Parameters XML NOT NULL);
INSERT INTO @tbl (Parameters) VALUES
(N'<ArrayOfItem>
  <Item>
    <Key>Member_Claim_Id</Key>
    <Value>1802538</Value>
  </Item>
  <Item>
    <Key>Reverify</Key>
    <Value>0</Value>
  </Item>
  <Item>
    <Key>RequestNumber</Key>
    <Value>First Request</Value>
  </Item>
</ArrayOfItem>');
-- DDL and sample data population, end

DECLARE @param VARCHAR(30) = 'RequestNumber';

SELECT ID
    , c.value('(Key/text())[1]', 'VARCHAR(30)') AS [Key]
    , c.value('(Value/text())[1]', 'VARCHAR(30)') AS [Value]
FROM @tbl
CROSS APPLY Parameters.nodes('/ArrayOfItem/Item[Key[text()=sql:variable("@param")]]') AS t(c);

Output

+----+---------------+---------------+
| ID |      Key      |     Value     |
+----+---------------+---------------+
|  1 | RequestNumber | First Request |
+----+---------------+---------------+

You could remove the `WHERE` and just use `nodes('/ArrayOfItem/Item[Key[text()=sql:variable("@param")]]') AS t(c)` — Charlieface, Feb 01 '22 at 21:48

How to query SQL Server XML column

1 Answers1