CODEWITHSUNDEEP
sql-serverxmlconcatenation
hgulyan
hgulyan

Reputation: 8239

How to Concatenate Same Node Values With XQuery

I'm trying to insert XML to a Table with XQuery. Some nodes are multiple, but there's only one column in the Table, so I need to concatenate same nodes.

How can I iterate in a "INSERT ... SELECT ... FROM @xmlDoc.nodes ..." kind of query.

Here's a sample of my xml file

<Persons>
    <Person>
        <FirstName>aaa</FirstName>
        <LastName>bbb</LastName>
        <DocumentNumber>AA 1234</DocumentNumber>
        <DocumentNumber>BB 1234</DocumentNumber>
        <WorkPlace>AAA Ltd</WorkPlace>
        <WorkPlace>BBB Ltd</WorkPlace>
    </Person>
    <Person>
        <FirstName>ccc</FirstName>
        <LastName>ddd</LastName>
        <DocumentNumber>CCC 1234</DocumentNumber>
        <DocumentNumber>DDD 1234</DocumentNumber>
        <DocumentNumber>EEE 1234</DocumentNumber>     
        <WorkPlace>CCC Ltd</WorkPlace>
        <WorkPlace>DDD Ltd</WorkPlace>
    </Person>
</Persons>

Thanks.

Upvotes: 0

Views: 3587

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Here is a version using a cte to isolate DocumentNumber and WorkPlace in xml columns first and then using for xml path('') to concatenate the values.

declare @xml xml
set @xml =
'
<Persons>
    <Person>
        <FirstName>aaa</FirstName>
        <LastName>bbb</LastName>
        <DocumentNumber>AA 1234</DocumentNumber>
        <DocumentNumber>BB 1234</DocumentNumber>
        <WorkPlace>AAA Ltd</WorkPlace>
        <WorkPlace>BBB Ltd</WorkPlace>
    </Person>
    <Person>
        <FirstName>ccc</FirstName>
        <LastName>ddd</LastName>
        <DocumentNumber>CCC 1234</DocumentNumber>
        <DocumentNumber>DDD 1234</DocumentNumber>
        <DocumentNumber>EEE 1234</DocumentNumber>     
        <WorkPlace>CCC Ltd</WorkPlace>
        <WorkPlace>DDD Ltd</WorkPlace>
    </Person>
</Persons>
'

;with cte as
(
  select
    p.value('FirstName[1]', 'varchar(50)') as FirstName,
    p.value('LastName[1]', 'varchar(50)') as LastName,
    p.query('DocumentNumber') as docXML,
    p.query('WorkPlace') as workXML
  from
    @xml.nodes('Persons/Person') p(p)
)
select
  FirstName,
  LastName,
  (select d.value('.', 'varchar(100)')+' '
   from cte.docXML.nodes('DocumentNumber') d(d)
   for xml path('')) as DocumentNumber,
  (select w.value('.', 'varchar(100)')+' '
   from cte.workXML.nodes('WorkPlace') w(w)
   for xml path('')) as WorkPlace
from cte

Upvotes: 2

marc_s
marc_s

Reputation: 754258

Your XML is very poorly organized..... the trouble is the multiple entries of <DocumentNumber> and <WorkPlace> entities directly inside <Person> and not within their own "container" node.

This makes is extremely hard to get this parsed properly.....

If you want to grab only the names, you could use this:

DECLARE @input XML = '<Persons>
    <Person>
        <FirstName>aaa</FirstName>
        <LastName>bbb</LastName>
        <DocumentNumber>AA 1234</DocumentNumber>
        <DocumentNumber>BB 1234</DocumentNumber>
        <WorkPlace>AAA Ltd</WorkPlace>
        <WorkPlace>BBB Ltd</WorkPlace>
    </Person>
    <Person>
        <FirstName>ccc</FirstName>
        <LastName>ddd</LastName>
        <DocumentNumber>CCC 1234</DocumentNumber>
        <DocumentNumber>DDD 1234</DocumentNumber>
        <DocumentNumber>EEE 1234</DocumentNumber>     
        <WorkPlace>CCC Ltd</WorkPlace>
        <WorkPlace>DDD Ltd</WorkPlace>
    </Person>
</Persons>'

SELECT
    Person.value('(FirstName)[1]', 'varchar(50)') 'First Name',
    Person.value('(LastName)[1]', 'varchar(50)') 'Last Name'
FROM
    @input.nodes('/Persons/Person') AS Persons(Person)

and of course, you could also do an INSERT INTO dbo.YourTable(FirstName, LastName) before that select to store the information.

But again: trying to get those <DocumentNumber> and <WorkPlace> nodes into a proper format is really really hard..... you either end up duplicating data, or you have to do a second and third parsing pass to get this stuff properly stored.

Upvotes: 1

Related Questions