My query seems to not recognize self-closing or empty nodes at all. Shouldn't the empty <td/>
node return 'null'? Or an empty string. Even if I place an empty space in the node, it still is ignored.
declare @x xml;
set @x='<root>
<tr>
<td />
<td>sdfg</td>
<td>dfgh</td>
</tr>
<tr>
<td>foo</td>
<td>bar</td>
<td>poo</td>
</tr>
</root>'
select
x.c.value('(td//text())[1]', 'varchar(1024)') as col1
,x.c.value('(td//text())[2]', 'varchar(1024)') as col2
,x.c.value('(td//text())[3]', 'varchar(1024)') as col3
from @x.nodes('/root/tr') x(c)
I want the result to be this:
col1 col2 col3
null sdfg dfgh
foo bar poo
..but instead I get this:
col1 col2 col3
sdfg dfgh null
foo bar poo