How to query SQL Server XML column

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.

Solution

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 |
+----+---------------+---------------+