From xml in SQL Server, get next sibling value

sql, sql-server, xml

Solution

Try this (edited to filter by Name):

DECLARE @Name VARCHAR(50)
SET @Name = 'XYZ'

SELECT Name, AVG(Value) 
FROM (
SELECT 
    c.value('Name[1]', 'VARCHAR(50)') AS Name
    , c.value('Value[1]', 'DECIMAL') AS Value
FROM CustomerXmlData
CROSS APPLY CustomerData.nodes('//Product[Name = sql:variable("@Name")]') AS t(c)
) T
GROUP BY Name

Problem

I've got a table that looks like this: ``` CREATE TABLE CustomerXmlData (CustomerId int, CustomerData xml) ``` The CustomerId links to the main customer table and the CustomerData is an xml document that looks like this ``` <Person> <Product> <Name>ABC</Name> <Value>500</Value> </Product> <Product> <Name>XYZ</Name> <Value>600</Value> </Product> </Person> ``` There are several hundred thousand such rows. In pseudocode, what I want to do is "Find the average `Value` of `Product` where `Name` = 'XYZ'". I know how to get values out based on element attributes or based on having a parent that is unique in the document, but neither of those is any use in this situation. I can't find anything that will let me locate the `Name` that I want and then get the value of the next sibling. I can use `FOR XML` and create an edge table, but would I then have to loop through that with a cursor? I'm hoping for a more efficient solution.

Original source