using xml .nodes() functionality to return all instances

Question

I have the following code but it only returns the first instance of the MonthDate and 'Value' values. I have been experimenting with the .nodes() and Cross Apply but can't figure out the syntax, I keep getting the error of;

Invalid object name 'xmlContent.nodes'.

Code:

Declare @ngTest Table(ID int Identity, XmlContent XML)
Insert into @ngTest(XMLContent)(SELECT TOP 1 Cast(Cast(Data as ntext) as XML) FROM [Accounts].[dbo].[GoalChartData]  where goalID = 3661 and typeID = 2)

SELECT Top 1000 ID, 
    [XmlContent].value('(GoalMonteCarloChartResult/MonthlyMedianResults/MonthlyAmount/MonthDate/node())[1]', 'datetime') as MonthDate,
    [XmlContent].value('(GoalMonteCarloChartResult/MonthlyMedianResults/MonthlyAmount/Value/node())[1]','float') as Value
    from @ngTest CROSS APPLY [xmlContent].nodes('/MonthlyMedianResults/MonthlyAmount')

When I remove the CROSS APPLY conditional it returns the first instance of the MonthDate and value, but I want them all.

Thank you

Answer 1

Most likely, you need something like this:

SELECT Top 1000 
    ID, 
    MonthDate = XCol.value('(MonthDate)[1]', 'DATETIME'),
    [Value] = XCol.value('(Value)[1]', 'FLOAT') 
FROM
    @ngTest 
CROSS APPLY 
    [xmlContent].nodes('/MonthlyMedianResults/MonthlyAmount') AS XTbl(XCol)

The .nodes() call creates a "virtual" table (XTbl) with a single XML column (XCol), where each row represents the XML fragment of each <MonthlyAmount> node (and you must include a AS table-alias(column-alias) to your CROSS APPLY section so you can refer to this virtual table in your query).

You need to apply the XQuery .value() to that virtual table to fetch the values from this virtual table or XML fragments.