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: (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
– Yitzhak Khabinsky, Commented Feb 1, 2022 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
– David.Warwick, Commented Feb 1, 2022 at 15:47

Charlieface · Accepted Answer · 2022-02-02 01:19:37Z

3

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

edited Feb 2, 2022 at 1:19

Charlieface

78.4k8 gold badges35 silver badges77 bronze badges

answered Feb 1, 2022 at 16:02

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

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

2 Comments

Charlieface Over a year ago

You could remove the WHERE and just use nodes('/ArrayOfItem/Item[Key[text()=sql:variable("@param")]]') AS t(c)

Yitzhak Khabinsky Over a year ago

@Charlieface, I concur

Collectives™ on Stack Overflow

How to query SQL Server XML column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related