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