Table-valued function 'Nodes' cannot have a column alias.

The following error occurred when we try to read xml data either from a Column or Expression...

1. XML data definition:

The following is the xml data, we just want to read data of Name and Place nodes.

Declare @xml xml

Select @xml = '<SQLBuddy><Name>Pandian S</Name><Place>Chennai</Place></SQLBuddy>'

The actual structure of the xml data is ...

2. Reading node value

Declare @xml xml

Select @xml = '<SQLBuddy><Name>Pandian S</Name><Place>Chennai</Place></SQLBuddy>'

Select Samples.[SQL].value('(Name)[1]', 'Varchar(100)') AS 'Name',
Samples.[SQL].value('(Place)[1]', 'Varchar(100)') AS 'Place'
FROM @xml.Nodes('/SQLBuddy') AS Samples([SQL])

 

It throws an error...

Msg 317, Level 16, State 1, Line 5

Table-valued function 'Nodes' cannot have a column alias.

Reason:

"nodes" is case sensitive. The actual script should be as follows

 

Declare @xml xml

Select @xml = '<SQLBuddy><Name>Pandian S</Name><Place>Chennai</Place></SQLBuddy>'

Select Samples.[SQL].value('(Name)[1]', 'Varchar(100)') AS 'Name',
Samples.[SQL].value('(Place)[1]', 'Varchar(100)') AS 'Place'

From @xml.nodes('/SQLBuddy') AS Samples([SQL])

Expected Result: