The table (and its columns) returned by a table-valued method need to be aliased sql server

If we will execute following sql query in sql server

DECLARE @Data AS XML = '

<Root>

    <Data>

         <Id>5</Id>

         <Name>Scott</Name>

         <Location>USA</Location>

    </Data>

    <Data>

         <Id>6</Id>

         <Name>Greg</Name>

         <Location>USA</Location>

    </Data>

</Root>'

SELECT * FROM @Data.nodes('/Root') AS Data

We will get error message :

The table (and its columns) returned by a table-valued method need to be aliased.

Cause: It is necessary to alias both table name and column name when we are using node function in from clause.

Solution:

DECLARE @Data AS XML = '

<Root>

    <Data>

         <Id>5</Id>

         <Name>Scott</Name>

         <Location>USA</Location>

    </Data>

    <Data>

         <Id>6</Id>

         <Name>Greg</Name>

         <Location>USA</Location>

    </Data>

</Root>'

SELECT Data.Col.value('(/Root/Data/Id)[1]','Int')

FROM @Data.nodes('/Root') AS Data(Col)

Output:

1