Thread: help for xml create in postgresql

i need to create xml by using xml function in postgresql.

i can create following xml by using this query
Query:
SELECT  XMLELEMENT(name chapter,
XMLATTRIBUTES(
'1' AS number,
CURRENT_DATE as current_date),
XMLELEMENT(name document, XMLATTRIBUTES((select typeaccountcode from typeaccount limit 1) AS doc_name)));

Result:

<chapter number="1" current_date="2011-07-11">
    <document doc_name="Billing"/>
</chapter>

BUT IF i want result in following format then?

<chapter number="1" current_date="2011-07-11">
    <document doc_name="Billing"/>
    <document doc_name="EManager"/>
    <document doc_name="Immunization"/>
    <document doc_name="NueMD"/>
    <document doc_name="NueMDSched"/>
    <document doc_name="SuperAccount"/>
    <document doc_name="UnivScheduler"/>
</chapter>

--
Thanks & Regards,
Jignesh Ramavat
Software Engineer
Yosa Technology Solutions Pvt. Ltd
09924407751

On 11/07/2011 3:44 PM, Jignesh Ramavat wrote:
> *BUT IF i want result in following format then?*
>
> <chapter number="1" current_date="2011-07-11">
> <document doc_name="Billing"/>
> <document doc_name="EManager"/>
> <document doc_name="Immunization"/>
> <document doc_name="NueMD"/>
> <document doc_name="NueMDSched"/>
> <document doc_name="SuperAccount"/>
> <document doc_name="UnivScheduler"/>
> </chapter>

Use xmlagg and a subquery.

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/