Apr 5, 2013

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

No comments:

Post a Comment