Does anyone know if this is possible in SQL Server? I have an XML value: <R><I>A</I><I>B</I><I>C</I></R>. I want to concatenate all I values and return them as a single value: ABC. I know I can do that by shredding the XML and aggregating the results back as a nodeless XML, and then extracting the result using .value('text()[1]', ...). But I'd like to know if it's possible to do all that without shredding, using XPath/XQuery (whatever is the right term) only.
In XQuery 3.0, you can use || as a string concatenation operator:
return $x || "[" || fn:string-join($y/a, " or ") || "]"
In XQuery 1.0, you need to use fn:concat():
return fn:concat($x, fn:concat("[", fn:concat(fn:string-join($y/a, " or "), "]")))
It uses fn:string-join, which didn't work for me (XQuery [value()]: There is no function '{http://www.w3.org/2004/07/xpath-functions}:string-join()')
I guess what I'm asking is whether there's an equivalent to fn:string-join supported by the SQL Server implementation of XQuery
Or maybe just different syntax altogether but with the same result. In my case I don't need to insert delimiters, I just want to concatenate all the values.
@McNets query, I think that's what I was trying to remember. I saw something like that in one of Mikael's answers, or maybe his postings here. Thank you very much, good sir.
@TomV I have no idea either, maybe that's by design, although it's somewhat counter-intuitive. Anyway, here's a variation that doesn't have spaces: dbfiddle.uk/…
I have an XML value like this:
<R>
<I>A</I>
<I>B</I>
<I>C</I>
...
</R>
I want to concatenate all I values and return them as a single string: ABC....
Now I know that I can shred the XML, aggregate the results back as a nodeless XML, and apply .values('text()[1]', ...) to the result:
...
I'm working with a really good tester at a customer project the last few weeks. He tests all sorts of expected but more importantly unexpected combinations of user input and then files a bug report "If I do x, then y, then xyz, then z, then x again, then y again, then undo y and redo y, then xyz again abc happens"
Then the analysts have to get into a meeting because nobody has a definitive answer what is supposed to be the outcome if you do all that
I'm looking forward to the ticket where he sacrificed a goat somewhere in the sequence of actions
@TomV In my experience, that's a very rare – and extremely valuable – kind.
@TomV Mikael has "stolen" McNets's suggestion but yours hasn't been posted yet. I strongly believe it should be there. Feel free to use the modified version. It's a viable alternative that produces the expected result.
@TomV You are right. That's the beauty of having such alternatives. Your solution fits the requirements without being too contrived. And it offers flexibility being more easily adaptable to changes in requirements.
@TomV It did, slightly more than an hour after being posted. I think it had like 25 views when I first noticed. How they pick them is a total mystery to me.
I really like the new Stack Exchange home page, where certain questions from the Stack Exchange Network are presented, along with a hotness rating that is described as "arbitrary" in its tooltip. Such questions also appear randomly on the sidebar on questions across the network, under the heading...
@TomV Not at all so. SQL Server does magic and comes up with the exact same execution plan as if you don’t use the loop. The other differences makes more of a difference. Don’t use nodes, use the variable twice and loop over R/I/text() and it should be exactly the same as my answer.
In XQuery 3.0, you can use || as a string concatenation operator:
return $x || "[" || fn:string-join($y/a, " or ") || "]"
In XQuery 1.0, you need to use fn:concat():
return fn:concat($x, fn:concat("[", fn:concat(fn:string-join($y/a, " or "), "]")))
I have an XML value like this:
<R>
<I>A</I>
<I>B</I>
<I>C</I>
...
</R>
I want to concatenate all I values and return them as a single string: ABC....
Now I know that I can shred the XML, aggregate the results back as a nodeless XML, and apply .values('text()[1]', ...) to the result:
...
