|
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. 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?
|
|
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
1
Thanks a lot. This seems to be the solution. I'll adjust my queries in production and see what it will do. Thanks a lot.
(12 Jul '22, 10:50)
Frank Vestjens
|
