Apr 5, 2013

The column '' that was returned from the nodes() method cannot be used directly. It can only be used with one of the four XML data type methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks.


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(Col)

We will get error message :

The column '' that was returned from the nodes() method cannot be used directly. It can only be used with one of the four XML data type methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks.

Cause: It necessary to use methods like:

1. exist()
2. nodes()
3. query()
4. value()

In select list of SELECT clause with column returned by node methods

Solution:

Some examples are:

1.

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

Output:

<Data>
  <Id>5</Id>
  <Name>Scott</Name>
  <Location>USA</Location>
</Data>
<Data>
  <Id>6</Id>
  <Name>Greg</Name>
  <Location>USA</Location>
</Data>

2.

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

Output:

5

No comments:

Post a Comment