SQL Server: XML Value () Query

The value() method must return a single value, and the XML contains multiple events. Therefore, the correct query includes the nodes() XML method in the FROM clause of the query. The nodes() method accepts an XQuery string and returns all of the specified nodes as a result set. In this scenario, the nodes() method returns a result set that contains one row for each event. Each row contains the corresponding XML for the event. Then, in the SELECT list, the table alias defined is used with the value() method to return the details for each specific event. DBA/Developer could also use the nodes() method to separate XML. For example, the following statement would generate a result set containing a single xml column, Events, with each row containing the XML representing a specific event.

SELECT x.Event.query('.')

AS Events FROM @xmldoc.nodes('//EventData/Event')
AS x (Event);

The following query will not display the desired results:

SELECT @xmldoc.query('/EventData/Event[@ID]')

AS EventID,@xmldoc.query('/
EventData/Event[@EventDesc]')
AS Description,@xmldoc.query('/EventData/Event
[@EventLoc]')
AS Location,@xmldoc.query('/EventData/Event/AvailSeats')
AS TotalSeats,@xmldoc.query('/EventData/Event/Enrolled')
AS Enrolled,@xmldoc.query('/
EventData/Event/AvailSeats') @xmldoc.query('/EventData/Event/Enrolled')
AS Remaining;