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

Question

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?

Yitzhak Khabinsky · Accepted Answer · 2020-07-07 18:50:32Z

2

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;

edited Jul 7, 2020 at 18:50

answered Jul 7, 2020 at 16:05

Yitzhak Khabinsky

22.9k2 gold badges19 silver badges25 bronze badges

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

5 Comments

Elen Over a year ago

thanks for the answer, but in this instance I'm using views and cannot declare variables, as far as I know...

Yitzhak Khabinsky Over a year ago

@Elen. In such case you can use just a hard-coded value. I updated the answer.

Elen Over a year ago

thanks @Yizhak, perhaps I can just use Excel to parse it then, not sure how to hard code it

Elen Over a year ago

I see what you did there. That would work for me, however when I applied it to my query as MAX(DISTINCT PR.CustomColumns.nodes('/CustomColumnsCollection/CustomColumn[(Name/text())[1] eq "Brand"]')) AS Spec0, SQL server gives me error nodes is not valid function... sorry I'm new to parsing XML within sql queries....

Gottfried Lesigang Over a year ago

@Elen, if allowed you might switch from the VIEW to an inline function. Such a function will accept your input as parameter and will allow for the code in this answer using sql:variable(). Within Excel you can pass the needed value into the function easily.

marc_s · Accepted Answer · 2020-07-07 14:33:28Z

0

Try something like this:

SELECT
    xc.value('(Value)[1]', 'varchar(50)') 
FROM
    PR
CROSS APPLY
    PR.CustomColumns.nodes('/CustomColumnsCollection/CustomColumn') AS XT(XC)
WHERE   
    xc.value('(Name)[1]', 'varchar(50)') = 'Brand'

The .nodes() returns a list of XML fragments, each representing a <CustomColumn> node. Select the one with the Name value of Brand in the WHERE clause, and get the value of Value for that XML node

answered Jul 7, 2020 at 14:33

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

1 Comment

Elen Over a year ago

I ran this as subquery with FROM dbo.Products as PR, but it returned nothing....

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related