Getting node value for data that is outside of a tag

Question

I have an xml fragment and I want to get the value of a within a tag that is not within another tag.

SET @xml = '<data>
    Cat
    <type>Black</type>
    <type>Orange</type>
<type>White</type>
    </data>'



SELECT @xml.value('/data[1]', 'varchar(80)') as result

When I run the above I get

  Cat
        BlackOrangeWhite

What I want is just

  Cat

Answer 1

You can use text() to select the text children of a node:

SELECT @xml.value('(/data/text())[1]', 'varchar(80)') as result

This should print Cat.