Search code examples
sql-serverxmlxpathsqlxml

Getting node value for data that is outside of a tag

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
Solution

  • 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.