SQL Server parse XML column to get a column value if other column value equals certain value

In SQL Server 2014 a table with a CustomColumns column that contains XML data with the following structure:

<CustomColumnsCollection>
  <CustomColumn>
    <Name>Brand</Name>
    <DataType>0</DataType>
    <Value>Duprim</Value>
  </CustomColumn>
  <CustomColumn>
    <Name>LabelGroup</Name>
    <DataType>0</DataType>
    <Value />
  </CustomColumn>
...
</CustomColumnsCollection>

I want to get value of column Value where column Name equals, i.e. 'Brand' (the following code is a part of bigger query, which I saved as VIEW):

MAX(DISTINCT PR.CustomColumns.value('(/CustomColumnsCollection/CustomColumn/Name="Brand"/Value)[0]', 'varchar(max)')) AS Brand

In this case I would like it to return 'Duprim'. How is this achieved?

Solution

Here is another method by using XPath predicate.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, CustomColumns XML);
INSERT INTO @tbl (CustomColumns)
VALUES
(N'<CustomColumnsCollection>
    <CustomColumn>
        <Name>Brand</Name>
        <DataType>0</DataType>
        <Value>Duprim</Value>
    </CustomColumn>
    <CustomColumn>
        <Name>LabelGroup</Name>
        <DataType>0</DataType>
        <Value/>
    </CustomColumn>
</CustomColumnsCollection>');
-- DDL and sample data population, end

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

SELECT  ID
    , c.value('(Value/text())[1]', 'VARCHAR(50)') AS [Value]
FROM @tbl
    CROSS APPLY CustomColumns.nodes('/CustomColumnsCollection/CustomColumn[(Name/text())[1] eq sql:variable("@param")]') AS t(c);

-- hard-coded value
SELECT  ID
    , c.value('(Value/text())[1]', 'VARCHAR(50)') AS [Value]
FROM @tbl
    CROSS APPLY CustomColumns.nodes('/CustomColumnsCollection/CustomColumn[(Name/text())[1] eq "Brand"]') AS t(c);

Output

+----+--------+
| ID | Value  |
+----+--------+
|  1 | Duprim |
+----+--------+

To help you with the view that is consumed by the MS Excel. It would be great if you could 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. (3) Desired output based on the sample data in #1 above.

SQL for Excel

SELECT ID
    , CustomColumns.value('(/CustomColumnsCollection/CustomColumn[(Name/text())[1] eq "Brand"]/Value/text())[1]', 'VARCHAR(50)') AS [Value]
FROM @tbl;