Concat XML nodes using XQuery

Question

I required the following output

<name>Thomas Mapother</name>
<name>Tom Cruise</name>

using the below XML using XQuery FLOWR Expressions.

INSERT INTO XMLO1 VALUES ('<Contact>
<Names>
    <Name Type = "Legal">
        <First>Thomas</First>
        <Middle>T</Middle>
        <Last>Mapother</Last>
    </Name>
    <Name Type = "Stage">
        <First>Tom</First>
        <Middle>C</Middle>
        <Last>Cruise</Last>
    </Name>
</Names>
</Contact>')

I tried the below query. But it returns a different output.

SELECT xDoc.query('let $names := Contact/Names/Name
return <name>{
    for $x in $names
    return ($x/First,$x/Last)}
</name>')
FROM XMLO1

Mikael Eriksson · Accepted Answer · 2019-06-28T09:46:25.697

2

Something like this:

select xDoc.query('
for $x in Contact/Names/Name
return element Name {concat($x/First[1]," ", $x/Last[1])}
')
from XMLO1

Or shred and recombine (will be faster):

select T.X.value('(First/text())[1]', 'nvarchar(100)')+' '+
         T.X.value('(Last/text())[1]', 'nvarchar(100)')
from XMLO1
  cross apply xDoc.nodes('Contact/Names/Name') as T(X)
for xml path('Name');

edited Jun 28 '19 at 09:46

answered Jun 28 '19 at 09:39

Mikael Eriksson

136,425
22
210
281

score 2 · Answer 2 · answered Jun 28 '19 at 12:01

From this and from Return multiple XML nodes and Custom parent tag using FLWOR XQuery , I think you're a bit confused about how FLWOR expressions work.

In the other question, you only wanted one wrapper element (oldPlanes), but you made the mistake of creating the element inside the return clause of a FLWOR expression, which is executed once for each node selected by the for clause. In this question, you have made the opposite mistake: you want one name element for each Name in the input, so you need to generate it within the return clause.

So instead of

return <name>{
    for $x in $names
    return ($x/First,$x/Last)}
</name>

you want

return 
    for $x in $names
    return <name>{($x/First,$x/Last)}</name>

Again, it can be simplified to

return $names/<name>{(First,Last)}</name>

Many people coming to XQuery from a SQL background make the mistake of thinking that every query has to be a FLWOR expression. In fact, the vast majority of queries don't need variables and don't need FLWOR.

Concat XML nodes using XQuery

2 Answers2

Linked