RE: Creating XML document via SQL - Db2 for i 7.2

Birgitta,
Thanks for your great example. I appreciate you providing a direct answer to my question. I must admit I share Jon's thoughts about SQL, but that is mostly due to my lack of use. I would definitely be interested in taking one of your classes. Would that be through COMMON, or RPG/DB2 Summit?

Jim Bostwick


message: 1

date: Sat, 10 Mar 2018 11:24:13 +0100

from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx<mailto:Hauser@xxxxxxxxxxxxxxx>>

subject: RE: Creating XML document via SQL - Db2 for i 7.2



Hi Jim,



that is not as difficult.

You split your SQL-Statement into multiple Common Table Expressions that construct the XML components. These CTEs are joined together and for building the next component until the XML document is completed in the Final Select.



Look at the following XML document:

<Request>

<Product>

ProductA

<Profile>

ProfileA

<Data>

<Field1>Field1A</Field1>

<Field2>2018-03-10</Field2>

</Data>

<NewGroup>

<Field3>10,56</Field3>

<Field4>Field4A</Field4>

</NewGroup>

</Profile>

</Product>

<Product>

ProductB

<Profile>

ProfileB

<Data>

<Field1>Field1B</Field1>

<Field2>2018-03-09</Field2>

</Data>

<NewGroup>

<Field3>125,20</Field3>

<Field4>Field4B</Field4>

</NewGroup>

</Profile>

</Product>

</Request>



This XML Document is generated by the following SQL-Statement With x as (Select *

From (Values('ProductA', 'ProfileA', 'Field1A', Current_Date,

10,56 , 'Field4A'),

('ProductB', 'ProfileB', 'Field1B', Current_Date -

1 Day, 125,2 , 'Field4B'))

x (Product, Profile, Field1, Field2, Field3, Field4)),



-- Build the Data and NewGroup components

y as (Select Product, Profile, xmlrow(Field1 as "Field1",

Field2 as "Field2"

Option row "Data") yData,

xmlrow(Field3 as "Field3",

Field4 as "Field4"

Option row "NewGroup") yNewGroup



from x),



-- Build the nexted elements Product, Profile, with Data and NewGroup



z as (Select Product, Profile, XMLElement(Name "Product", Product,

XMLElement(Name "Profile", Profile,

yData,

yNewGroup)) zProduct

from y)



-- Final Select aggregate the different rows and include this information in

an root element

Select XMLElement(Name "Request", XMLAgg(zProduct))

from z;



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)