By user2566049


2019-06-11 22:44:22 8 Comments

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

1 comments

@Mikael Eriksson 2019-06-12 05:24:17

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 first td element there are no text nodes present so you get the text node for the second td 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 in col1

select
     x.c.value('td[1]/text()[1]', 'varchar(1024)') as col1
    ,x.c.value('td[2]/text()[1]', 'varchar(1024)') as col2
    ,x.c.value('td[3]/text()[1]', 'varchar(1024)') as col3
from @x.nodes('/root/tr') x(c)

And not specifying the text() node will give you an empty string in col1.

select
     x.c.value('td[1]', 'varchar(1024)') as col1
    ,x.c.value('td[2]', 'varchar(1024)') as col2
    ,x.c.value('td[3]', 'varchar(1024)') as col3
from @x.nodes('/root/tr') x(c)

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Why is the auto created statistic on this column empty?

2 Answered Questions

[SOLVED] How to write this SQL query for a XML type?

3 Answered Questions

[SOLVED] Storing NULL versus storing '' in a varchar column

2 Answered Questions

1 Answered Questions

[SOLVED] Why is the output of this query being returned like this?

1 Answered Questions

1 Answered Questions

[SOLVED] Select row from non-empty node value in xml column using xml query

  • 2016-03-30 09:40:38
  • IT researcher
  • 846 View
  • 1 Score
  • 1 Answer
  • Tags:   xquery

2 Answered Questions

[SOLVED] Alias of COUNT is not being "recognized" by SQL Server

  • 2016-01-22 14:50:32
  • Andrei
  • 3496 View
  • 6 Score
  • 2 Answer
  • Tags:   sql-server

Sponsored Content