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
Best Answer
In
(td//text())[1]
the predicate[1]
will return the first node returned by the XQuery(td//text())
.You are saying that you want the first
text()
node. But in the firsttd
element there are no text nodes present so you get the text node for the secondtd
element which is the first text node returned.You need to specify the position predicate on the
td
node instead.This will give you
null
incol1
And not specifying the text() node will give you an empty string in
col1
.