Selecting data from XML field in SQL

Question

I am attempting to extract data from an XML field in a MSSQL server database, sample XML below

<Results>
    <Result name="A" ID="1" category="C" resultLevel="">
        <Value>01/01/1900</Value>
    </Result>

Ideally the select statement will return the following as query results

Name	Value
A	01/01/1900
B	01/01/1910

Then based on the query results, add a where clause to return specific values based on the name.

Answer 1

It is a trivial task.

XQuery .nodes() and .value() methods to the rescue.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<Results>
    <Result name="A" ID="1" category="C" resultLevel="">
        <Value>2021-03-10</Value>
    </Result>
    <Result name="B" ID="1" category="C" resultLevel="">
        <Value>2021-03-12</Value>
    </Result>
</Results>');
-- DDL and sample data population, end

SELECT c.value('@name', 'VARCHAR(20)') AS [name]
    , c.value('(Value/text())[1]', 'DATE') AS [value]
FROM @tbl CROSS APPLY xmldata.nodes('/Results/Result') AS t(c)
WHERE c.value('(Value/text())[1]', 'DATE') = '2021-03-10';

Output

+------+------------+
| name |   value    |
+------+------------+
| A    | 2021-03-10 |
+------+------------+