RE: Table to XML file

It looks to me like you're giving your rows the same name as one of your columns.

Here:
XMLAGG(XMLROW(COMPNY as "Company_Code", CNAME as "Company_Name" OPTION ROW "Company_Code"))) AS



-----Original Message-----
From: Alan Shore [mailto:ashore@xxxxxxxx]
Sent: Friday, January 26, 2018 11:36 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Table to XML file

Hi everyone

I reads the article that Justin mentioned with interest, and decided to play around on my system (V7r1) with what the article said

I got as far as this (tailored to a simple file on my system)



SELECT XMLSERIALIZE(XMLELEMENT(NAME "Companies",

XMLAGG(XMLROW(COMPNY as "Company_Code", CNAME as "Company_Name" OPTION ROW "Company_Code"))) AS CLOB(1M) INCLUDING

XMLDECLARATION) AS XML_DOCUMENT

FROM (select * from prodfa.compnyp ORDER BY compny) COMPANY;



With the following results



<?xml version="1.0" encoding="UTF-8"?>

<Companies>

<Company_Code><Company_Code>A</Company_Code><Company_Name>Company Name A </Company_Name></Company_Code>

<Company_Code><Company_Code>B</Company_Code><Company_Name>Company Name B </Company_Name></Company_Code>

</Companies>



What somewhat surprised me was that there are 2 tabs with basically the same name

Company_Code



Am I doing something wrong in my SQL?





Alan Shore

E-mail : ASHORE@xxxxxxxx

Phone [O] : (631) 200-5019

Phone [C] : (631) 880-8640

'If you're going through hell, keep going.'

Winston Churchill



-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Friday, January 26, 2018 1:35 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxx>
Subject: RE: Table to XML file



Hi Justin,



the easiest way to create the XML for a Table is to use the SQL scalar Function XMLGROUP.



The following statement will return and XML-Document with the <rowset> root element and the <row> row element and the column names as elements in upper

case:

Select xmlgroup(EmployeeNo, Name, FirstName)

from lobstaff;



<rowset><row><EMPLOYEENO>20</EMPLOYEENO><NAME>Bauer</NAME><FIRSTNAME>Herrman

n</FIRSTNAME></row>

<row><EMPLOYEENO>40</EMPLOYEENO><NAME>Hauser</NAME><FIRSTNAME>Birgitta</FIRS

TNAME</row>

...

</rowset>



For returning other information such as renamed row and root elements check the XMLGROUP description.



You can also use the XML-Functions describe in the IT Jungle Article.



Mit freundlichen Grüßen / Best regards



Birgitta Hauser



"Shoot for the moon, even if you miss, you'll land among the stars." (Les

Brown)

"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"

"Train people well enough so they can leave, treat them well enough so they don't want to." (Richard Branson)





-----Original Message-----

From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Justin Taylor

Sent: Donnerstag, 25. Januar 2018 23:52

To: MIDRANGE-L (midrange-l@xxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxx>) <midrange-l@xxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxx>>

Subject: Table to XML file



I need to export a Db2 table to an XML file. I'm working off this IT Jungle article (https://www.itjungle.com/2012/08/08/fhg080812-story02/), but it's rather dated. Is there a newer reference/how-to?



TIA

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxx> To subscribe, unsubscribe, or change list options,

visit: https://lists.midrange.com/mailman/listinfo/midrange-l

or email: MIDRANGE-L-request@xxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxx> Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.



Please contact support@xxxxxxxxxxxx<mailto:support@xxxxxxxxxxxx> for any subscription related questions.



Help support midrange.com by shopping at amazon.com with our affiliate link:

http://amzn.to/2dEadiD



--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxx> To subscribe, unsubscribe, or change list options,

visit: https://lists.midrange.com/mailman/listinfo/midrange-l

or email: MIDRANGE-L-request@xxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxx> Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.



Please contact support@xxxxxxxxxxxx<mailto:support@xxxxxxxxxxxx> for any subscription related questions.



Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD