Xmlserialize

[Reboot1970]

Hello,

With this SQL in RPG I'm using it to creat a clob in the IFS:

SELECT XMLSERIALIZE(XMLDOCUMENT(
xmlelement(name "list",
xmlelement(name "cuso", a.cuso),
xmlagg(xmlelement(name "line", b.ordl55) order by b.ordl55))
) as varchar(10000) ccsid 37)
as response
INTO :ResultCLOB
FROM tedi36pf a, oep55 b
where a.cono = '60'
AND b.cono55 = a.cono
AND b.ordn55 = SUBSTR(a.cord, 3, 7)
group by a.cuso



Only the first line is in the XML, how can I group it in a higher lever tag like "group" for example?


FIle B has multiple records per each of file A



Any help would be appreciated

regards

[B.Hauser]

If you want to aggregate all rows you got into another element, you have to embedd them in another XMLAGG function.
The easiest way is to use common table expressions to build the puzzle pieces and put them togehter.
Something like this:

With x as (SELECT xmlelement(name "list",
xmlelement(name "cuso", a.cuso),
xmlagg(xmlelement(name "line", b.ordl55)
order by b.ordl55)) as response
FROM tedi36pf a, oep55 b
where a.cono = '60'
AND b.cono55 = a.cono
AND b.ordn55 = SUBSTR(a.cord, 3, 7)
group by a.cuso)
Select XMLSerialize(XMLDocument XMLElement(Name "Root", XMLAgg(Response))
as VarChar(10000) CCSID 37)
into :ResultCLOB
From x;

BTW you are retuning a VARCHAR not a CLOB.
Why to convert the string into CCSID 37 and not into UTF-8 (CCSID 1208)

[Reboot1970]

If you want to aggregate all rows you got into another element, you have to embedd them in another XMLAGG function.
The easiest way is to use common table expressions to build the puzzle pieces and put them togehter.
Something like this:

With x as (SELECT xmlelement(name "list",
xmlelement(name "cuso", a.cuso),
xmlagg(xmlelement(name "line", b.ordl55)
order by b.ordl55)) as response
FROM tedi36pf a, oep55 b
where a.cono = '60'
AND b.cono55 = a.cono
AND b.ordn55 = SUBSTR(a.cord, 3, 7)
group by a.cuso)
Select XMLSerialize(XMLDocument XMLElement(Name "Root", XMLAgg(Response))
as VarChar(10000) CCSID 37)
into :ResultCLOB
From x;

BTW you are retuning a VARCHAR not a CLOB.
Why to convert the string into CCSID 37 and not into UTF-8 (CCSID 1208)

Hi Brigitta, I'm using varchar just to run it interactive on green on black.

[Reboot1970]

Finally got it working

WITH X AS (
SELECT
XMLELEMENT(NAME "ignore", XMLELEMENT(NAME "Consignment", XMLELEMENT(NAME "Header", XMLELEMENT(NAME
"ManifestDate", TRIM(SUBSTR(ddat, 7, 4) || '-' || SUBSTR(ddat, 4, 2) || '-' || SUBSTR(ddat, 1, 2))), XMLELEMENT(NAME
"ConsignmentNumber", TRIM(cuso)), XMLELEMENT(NAME "CustomerReference", SUBSTR(TRIM(cord), 3, 7)), XMLELEMENT(NAME
"AccountCode", ''), XMLELEMENT(NAME "PlanningNotes", TRIM(mnot))), XMLELEMENT(NAME "CollectionDetails", XMLELEMENT(NAME
"Date", TRIM('20' || SUBSTR(dldd, 7, 2) || '-' || SUBSTR(dldd, 4, 2) || '-' || SUBSTR(dldd, 1, 2))), XMLELEMENT(NAME
"Location", XMLELEMENT(NAME "Name", 'Tetrosyl'), XMLELEMENT(NAME "Address1", 'Royle Barn Road'), XMLELEMENT(NAME
"Address2", 'Rochdale'), XMLELEMENT(NAME "Postcode", 'OL11 3DT'), XMLELEMENT(NAME "Country", 'GB')), XMLELEMENT(NAME
"Contact", XMLELEMENT(NAME "Name", (SELECT TRIM(CNTN1A)
FROM tstt1f2.T1P1A01P
WHERE cono1A = cono
AND TPAC1A = cusn
AND dseq1a = dseq
AND ctnu1a = (SELECT pyct05
FROM tstt1f2.cusnames
WHERE cono05 = cono
AND cusn05 = cusn
AND dseq05 = dseq))), XMLELEMENT(NAME "Phone", (SELECT TRIM(phon05)
FROM tstt1f2.cusnames
WHERE cono05 = cono
AND cusn05 = cusn
AND dseq05 = dseq)))), XMLELEMENT(NAME "DeliveryDetails", XMLELEMENT(NAME "Date", TRIM(
'20' || SUBSTR(dldd, 7, 2) || '-' || SUBSTR(dldd, 4, 2) || '-' || SUBSTR(dldd, 1, 2))),
XMLELEMENT(NAME "Time", TRIM(dldt)), XMLELEMENT(NAME "Location", XMLELEMENT(NAME "Name", TRIM(cnam)),
XMLELEMENT(NAME "Address1", TRIM(cad1)), XMLELEMENT(NAME "Address2", TRIM(cad2)), XMLELEMENT(NAME "Address3", TRIM(
cad3)), XMLELEMENT(NAME "Address4", TRIM(cad4)), XMLELEMENT(NAME "Address5", TRIM(cad5)), XMLELEMENT(NAME
"Postcode", TRIM(post)), XMLELEMENT(NAME "Country", 'GB')), XMLELEMENT(NAME "Contact", XMLELEMENT(NAME "Name", (
SELECT TRIM(CNTN1A)
FROM tstt1f2.T1P1A01P
WHERE cono1A = cono
AND TPAC1A = cusn
AND dseq1a = dseq
AND ctnu1a = (SELECT pyct05
FROM tstt1f2.cusnames
WHERE cono05 = cono
AND cusn05 = cusn
AND dseq05 = dseq))), XMLELEMENT(NAME "Phone", (SELECT TRIM(phon05)
FROM tstt1f2.cusnames
WHERE cono05 = cono
AND cusn05 = cusn
AND dseq05 = dseq)))), XMLELEMENT(NAME "ServiceProfile", XMLATTRIBUTES ('Pallet' AS "unitType")
, XMLELEMENT(NAME "Service", '1D'), XMLELEMENT(NAME "ListOfSurcharges",
XMLELEMENT(NAME "Surcharge", XMLATTRIBUTES ('AM' AS "Code"), ''))), XMLELEMENT(NAME "DefinitionOfGoods",
XMLELEMENT(NAME "TotalFull", oebulk), XMLELEMENT(NAME "TotalHalf", CHAR(oepals-oebulk)), XMLELEMENT(NAME
"TotalSpaces", oepals), XMLELEMENT(NAME "TotalKilos", INTEGER(oewght)), XMLAGG(XMLELEMENT(NAME
"ListOfADRInformation", XMLELEMENT(NAME "ADRInfo", XMLATTRIBUTES (TRIM(hazuno) AS "UNNo", TRIM(
CHAR(CAST(IFNULL(qtor55 / spc135, 0) AS INT))) AS "NoOfPackages", TRIM(CHAR(CAST(spc135 AS INT))) AS
"QtyPerPackage", TRIM(pdes35) AS "ProductDescription", TRIM(HZ2PKC) AS "PackingGroup", IFNULL((SELECT TRIM(prmd15)
FROM tstt2f2.desc
WHERE cono15 = cono
AND prmt15 = 'HZ01'
AND psar15 = hz2pkc),
'tbd') AS "PackageDescription", TRIM(HZ2PKU) AS "PackageMeasure"))) ORDER BY ordl55)))) AS response
FROM TST400A2.tedi36pf,
tstt2f2.ordlnord,
TST400A2.TOEHAZl1,
tstt2f2.parts,
TST400A2.toe416lf,
TST400A2.TOEHAZ2PF
WHERE cono = '60'
AND cono55 = cono
AND ordn55 = SUBSTR(cord, 3, 7)
AND catn55 = hazitm
AND cono35 = cono
AND pnum35 = catn55
AND oeordn = ordn55
AND hz2itm = hazitm
AND hazlmt = 'HZ'
GROUP BY cuso,
cord,
cusn,
dseq,
oewght,
oepals,
oebulk,
cnam,
cad1,
cad2,
cad3,
cad4,
cad5,
post,
dldt,
dldd,
ddat,
mnot
)
SELECT replace(replace((XMLSERIALIZE(CONTENT
XMLELEMENT(Name "ConsignmentList",
XMLAGG(Response)) AS VARCHAR(10000) CCSID 37)),
'<ignore>', ''), '</ignore>', '')
FROM x;

Thanks!

[B.Hauser]

As an aside ... since you are working with Common Table Expressions.
Instead of nesting sub-selects in your Common Table expression, you should build additional CTEs.
In the first CTE(s) you should prepare the "raw" data you have to include in your XML document ... and then you start constructing the XML-Document from the lowest level, include the result in the next level ... until you finally get the complete xml-document