Thanks Justin
Cant see the wood for the trees
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 Justin Taylor
Sent: Friday, January 26, 2018 12:39 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: 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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: 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 Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
