T-SQL XML Query, how to seperate matching nodes into individual rows?
- by FlySwat
I have a table that has a column full of XML like:
<parent>
<child>
<name>Sally</name>
</child>
<child>
<name>Bobby</name>
</child>
</parent>
I'm trying to extract all of the names of the children into seperate rows. My desired resultset would look like:
Sally
Bobby
However, if I do something like:
SELECT data.query('data(//parents/child/name)') FROM stuff
I get back a single row that looks like
Sally Bobby
What am I doing wrong with the XPath?