Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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.3k374866
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
40.5k365556827
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:

×248
×29

question asked: 12 Jul '22, 07:34

question was seen: 567 times

last updated: 12 Jul '22, 10:51