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
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');
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.
