Select item without a particular child of X

Question

For the following xml

<Root>
  <A><B>....</B></A>
  <A><B>....</B><X>...</X></A>

I'm using the following xquery in SQL Server 2008R2. How to get the elements of A without child X?

select @xml.query('<r>...
{ for $a in //A return 
   <a>... {... $a[????] ...} .... (: use $a which doesn''t have child X only :)
     { for $x in $a/X return <x>... $x ...</x> } (: otherwise use //a/x :)
   </a>
}</r>')

Update: Testable example:

declare @xml xml = '
<Root>
  <A><B>123</B><C>***</C></A>
  <A><B>456</B><C>***</C><X>abc</X></A>
</Root>'

select @xml.query('<r>
{ for $a in //A return 
   <a>{ $a/B/text() }  (: use $a which doesn''t have child X only :)
     { for $x in $a/X return $x/text() } (: otherwise use //a/x :)
   </a>
}</r>')

expected <r><a>123</a><a>abc</a></r> instead of <r><a>123</a><a>456abc</a></r>.

possible duplicate of [How to get node without children in xQuery?](http://stackoverflow.com/questions/3026038/how-to-get-node-without-children-in-xquery) — dirkk, Dec 17 '14 at 09:22
well, looking again I agree it is not the most appropriate one, SO search is unfortunately not the best. But the generic question (How to return an XML node without some children) has been asked many times before here, e.g. https://stackoverflow.com/questions/5159100/xquery-that-selects-node-but-omits-returning-child-nodes or https://stackoverflow.com/questions/6547381/how-to-select-with-omitting-a-specific-child-using-xpath — dirkk, Dec 17 '14 at 16:28
No, they are not. Maybe it is because I come from an XQuery background, but the principle is exactly the same to me. — dirkk, Dec 18 '14 at 01:04
The purpose is different. My question is not not removing a child element but do something if a particular child element doesn't exist. I don't see how they are principle the same. — ca9163d9, Dec 18 '14 at 04:43
I don't think the question is duplicated with these questions too. — , Dec 18 '14 at 04:49
ah, I see. Sorry, but this wasn't clear for me from the question. As you ask _"How to get the elements of A without child X?"_ I expected you to want exactly this as a result. Rewording this to what you said in your last comment would have helped me a lot. However, I retracted my close vote. — dirkk, Dec 18 '14 at 09:04

Code Different · Accepted Answer · 2014-12-16T22:23:16.233

1

declare @xml xml = '
<Root>
  <A><B>123</B></A>
  <A><B>456</B><X>abc</X></A>
</Root>'

select @xml.query('
<r>{
    for $a in //A
    return <a>{
        if (count($a/X) = 0)
        then $a/*/text()
        else $a/X/text()
    }</a>
}</r>
')

edited Dec 16 '14 at 22:23

answered Dec 16 '14 at 21:42

Code Different

90,614
16
144
163

It also removed the first `.. 123 456abc ' select @xml.query(' { for $a in //A return { for $c in $a/* where local-name($c) != "X" return $c/B/text() } (: use $a which doesn''t have child X only :) { for $x in $a/X return $x/text() } (: otherwise use //a/x :) }')`. Very strange. – ca9163d9 Dec 16 '14 at 21:56
Thanks. The updated returns `123 456` instead of expected `123 abc`. BTW, the `` may have other children and they shouldn't be in the output. – ca9163d9 Dec 16 '14 at 22:13
1

Generally `string()` and `node-name()` should be used instead of `text()` or `local-name()`. There are exceptions, but those are safer. – wst Dec 16 '14 at 22:23
The new answer doesn't use any of those. – Code Different Dec 16 '14 at 22:25
I still see `text()` in your answer. This will return all text node children, which may not be what you want, depending on the content of the parent node. If you simply want the string value of an element, then use `string()`. – wst Dec 17 '14 at 00:44

wst · Answer 2 · 2014-12-17T00:41:43.070

0

for $a in //A return 
element { node-name($a) } {
  $a/*[local-name(.) != 'X']
}

Reconstructing the element name dynamically isn't necessary, but assuming this is a simplified example, it might come in handy if you are iterating over non-identical element names.

edited Dec 17 '14 at 00:41

answered Dec 16 '14 at 20:41

wst

11,681
1
24
39

How to select child `B` of `$a/* except $a/X`? – ca9163d9 Dec 16 '14 at 21:33
BTW, Sql server 2008 xquery doesn't support `except`. – ca9163d9 Dec 16 '14 at 21:40
Thanks. I changed `node-name` to `local-name` since SQL Server 2008 doesn't support `node-name`. But I still got error `XQuery [query()]: Cannot explicitly convert from 'xs:string' to 'xs:QName'`. – ca9163d9 Dec 16 '14 at 22:31
@dc7a9163d9 This is because `node-name()` returns an 'xs:QName' type, not `xs:string`. If you have to use `local-name()`, then you have to compare it to a string. See updated answer. – wst Dec 17 '14 at 00:41

Select item without a particular child of X

2 Answers2