CODEWITHSUNDEEP
sql-serverxmlxquery
Ubercoder
Ubercoder

Reputation: 741

Search for element *without* a given child (where the child element is variable)

In SQL Server 2008 I want to identify elements that do not contain a given child element in the case where the child element name is dynamic. In this XML all three li elements contain F1 and F2 children, and the 2nd and 3rd li's do not contain an edges element. I can find the li's without the edges children if the name of the edges element is known beforehand - see the exist query below that returns 1 as it should.

declare @x xml = '<data>
  <li>
    <F1>ellipse</F1>
    <F2 />
    <edges/>
  </li>
  <li>
    <F1>triangle</F1>
    <F2>3</F2>
  </li>
  <li>
    <F1>square</F1>
    <F2>4</F2>
  </li>
</data>';

select @x.exist('/data/li[not(F1)]') -- returns 0, there is no li without an F1 (OK)
select @x.exist('/data/li[not(edges)]') -- returns 1, there is an li without edges (OK)

But if the edges value is not known in advance, how do I specify it using a SQL variable? I would like to code something like the following:

declare @ChildElement varchar(100) = 'edges';
select @x.exist('/data/li[not(sql:variable("@ChildElement"))]') -- not allowed

but sql:variable is not valid in this context in SQL Server. I have also tried combinations using local-name():

select @x.exist('/data/li[not(local-name()=sql:variable("ChildElement"))]') -- always returns 1

but it always returns 1. I think because there is always an li with a child element that is not F1, and there is always an li with a child element that is not edges. How I can identify just the li without the edges sub-element if edges is in a SQL variable? BTW I am using SQL Server 2008, although I think this applies for all SQL Server versions from 2005 up.

Upvotes: 2

Views: 119

Answers (1)

Jens Erat
Jens Erat

Reputation: 38682

I guess that your first approach does not work, as the variable is "replaced" by a string (I don't know SQL Server's XQuery capabilities well enough to explain what's going on in detail).

The second approach is querying all li elements, where the local-name() of the li element (current context!) equals the SQL variable – probably not what you want to do. Instead, try

select @x.exist('/data/li[not(./*[local-name()=sql:variable("@ChildElement")])]')

which tests all children instead.

Upvotes: 3

Related Questions