I'm using SQL anywhere 17.0.11 build 6933.

I'm using the SQL Anywhere 17 demo database to explain what I'm trying to achieve.
I created the following query to generate an XML line per customer:

select 
 xmlconcat(xmlelement(name Customer,
                         (select xmlconcat(XMLelement(name Name,CompanyName)
                                          ,XMLelement(name City,City)
                                          ,(select 
                                              SurName ||', '|| GivenName as FullName
                                            from groupo.Contacts
                                            where CustomerID = groupo.Customers.Id 
                                            for xml auto, elements)
                                          )
                         ) 
                     )
          )
from groupo.Customers where Id between 117 and 119
For very customer I get a line returned in the format I'm lookgin for:
<textarea rows="3" cols="180">
<customer><name>Square Sports</name><city>Unionville</city></customer>
<customer><name>Raleigh Active Wear</name><city>Chandler</city></customer>
<customer><name>Ocean Sports</name><city>Owen Sound</city><contacts><fullname>Hildebrand, Jane</fullname></contacts></customer>
</textarea>
So far so good. But now I want these lines added into 1 XML string surrounded by Customers.
So it should look like:
<customers>
   <customer>
      <name>Square Sports</name>
      <city>Unionville</city>
   </customer>
   <customer>
      <name>Raleigh Active Wear</name>
      <city>Chandler</city>
   </customer>
   <customer>
      <name>Ocean Sports</name>
      <city>Owen Sound</city>
      <contacts><fullname>Hildebrand, Jane</fullname></contacts>
   </customer>
</customers>
Anyone any idea how to achieve this?

asked 12 Jul '22, 07:34

Frank%20Vestjens's gravatar image

Frank Vestjens
1.2k324358
accept rate: 20%

edited 12 Jul '22, 07:40


I don't know whether this is a good solution, the XML functions are still something I usually have to fiddle around with...- but XMLAGG is used to generate the sequence of customer nodes, and an outer XMLELEMENT is required for the root node:

select xmlelement(name Customers, 
   xmlagg(xmlconcat(xmlelement(name Customer,
      (select xmlconcat(XMLelement(name Name,CompanyName)
                                            ,XMLelement(name City,City)
                                            ,(select 
                                                 SurName ||', '|| GivenName as FullName
                                               from groupo.Contacts
                                               where CustomerID = groupo.Customers.Id 
                                               for xml auto, elements)
))))))
from groupo.Customers where Id between 117 and 119
permanent link

answered 12 Jul '22, 10:41

Volker%20Barth's gravatar image

Volker Barth
39.7k357545815
accept rate: 34%

1

Thanks a lot. This seems to be the solution.
I've been fiddling around for a day to get what I already had but couldn't figure out the last step.

I'll adjust my queries in production and see what it will do.

Thanks a lot.

(12 Jul '22, 10:50) Frank Vestjens
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×230
×29

question asked: 12 Jul '22, 07:34

question was seen: 327 times

last updated: 12 Jul '22, 10:51