Returning data from XML data in a column in a SQL Server database

Trying to extract data from a column in a SQL Server table. The column however is not of type XML but the data is in XML format (see below). I can extract the data I need via the CAST function, but looking to see if there’s a more efficient query to do so.

The data below exists in the details column of table FoodTable. I would like to query the table to get a list of all ‘prod’ (i.e. apple, blueberry, cherry) from <FoodCity>. The data is much larger in reality so would like help with more efficient query if possible.

I have the following:

SELECT
    CAST(details AS xml).value('(//GROCER/prod[@key="apple"]/@key)[1]', 'nvarchar(max)') AS 'apple',
    CAST(details AS xml).value('(//GROCER/prod[@key="blueberry"]/@key)[1]', 'nvarchar(max)') AS 'blueberry',
    CAST(details AS xml).value('(//GROCER/prod[@key="cherry"]/@key)[1]', 'nvarchar(max)') AS 'cherry'
FROM 
    ABC.FoodTable

XML looks like this:

<GROCER>
    <FoodCity>
        <prod key = 'apple' value = '1.00'>
        <prod key = 'blueberry' value = '2.00'>
        <prod key = 'cherry' value = '5.00'>
    </FoodCity>
    <DiscountGrocer>
        <prod key = 'pear' value = '3.00'>
        <prod key = 'peach' value = '4.00'>
        <prod key = 'kiwi' value = '6.00'>
    </DiscountGrocer>
</GROCER>

Although my query above works, just looking to make it more efficient and simpler if possible.

Output expected | product |

| apple | | blueberry | | cherry |

Thank you.

Answer

While waiting for your DDL and sample data population. Here is a conceptual example.

It is using XQuery methods .nodes() and .value() to shred XML and convert it into a rectangular/relational format.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, details NVARCHAR(max));
INSERT INTO @tbl (details) VALUES
(N'<GROCER>
    <FoodCity>
        <prod key="apple" value="1.00"/>
        <prod key="blueberry" value="2.00"/>
        <prod key="cherry" value="5.00"/>
    </FoodCity>
    <DiscountGrocer>
        <prod key="pear" value="3.00"/>
        <prod key="peach" value="4.00"/>
        <prod key="kiwi" value="6.00"/>
    </DiscountGrocer>
</GROCER>');
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT id, CAST(details AS XML) AS xmldata
    FROM @tbl
)
SELECT c.value('@key', 'VARCHAR(20)') AS product
    , c.value('@value', 'DECIMAL(5,2)') AS price
FROM rs CROSS APPLY xmldata.nodes('/GROCER/*/prod') AS t(c);

Output

+-----------+-------+
|  product  | price |
+-----------+-------+
| apple     |  1.00 |
| blueberry |  2.00 |
| cherry    |  5.00 |
| pear      |  3.00 |
| peach     |  4.00 |
| kiwi      |  6.00 |
+-----------+-------+