sql-serverxqueryxquery-sqlflwor

Concat XML nodes using XQuery

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
Solution

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