XML conversion

[instrucrat1]

Hi
I am trying to convert data in to XML using SQL. I converted data to XML but struggling to add subgroup in XML.
It will be great help if someone can help.

Exec SQL
Select XmlDocument
(xmlgroup(TDTABL as "TABL",
KEYF as "KEYF",
DESC as "DESC",
TABL as "STRU"
Option Row "TABLE"
Root "TEST"))
into :XMLDoc
From SYdd ;

e.g.
<TABLE>
<TABL>NOHDSH</TABL>
<KEYF>29 </KEYF>
<DESC>FREIGHT CHARGES(/DESC><STRU>NOHDSH</STRU>
</TABLE>

But I want to make it as
<TABLE>
<TABL>NOHDSH</TABL>
<KEYF>29 </KEYF>
<DESC>FREIGHT CHARGES(/DESC>
<STRU>
<STRU1>NOHDSH</STRU1>
</STRU>
<STRU>
<STRU1>NOHDSH1</STRU1>
</STRU>

</TABLE>

[Vectorspace]

It's hard to tell without knowing the structure of the SYdd table, but I assume it's something like:

TABL    KEYF  DESC             TABL
NOHDSH  29    FREIGHT CHARGES  NOHDSH
NOHDSH  29    FREIGHT CHARGES  NOHDSH1

In which case xmlGroup is not going to work for you because you have a mix of repeating and non-repeating columns. xmlAgg allows you to aggregate multiple rows into one, it works in the same was as sum() or count() so you have to apply it with a group by clause.

If my assumption about the layout of table SYdd is correct then this SQL will generate the XML you want:

select * from SYdd;
select
  xmlElement(NAME "TABLE",
    xmlForest(TDTABL as "TABL",
              KEYF as "KEYF",
              DESC as "DESC"
    ),
    xmlAgg(
      xmlElement(NAME "STRU",
        xmlElement(NAME "STRU1", TABL)
      )
    )
  )
from SYdd
group by TDTABL,KEYF,DESC;

If not, then please describe the layout of the SYdd table. We need to know that in order to work out how to convert it into XML.

[instrucrat1]

Thank you.
Below is file structure. I tried many ways but one way or other it breaking.

AS400 File -

CNTYP 18 Cnd Type
CNTBID 18 Cnd ID
KEYVAL 18 Key
EFFF 18 from date
CVAL 18 Cnd Value
EXTIM 18 Ext time
CTNAM 18 Cnt Name
CTTYP 18 Cnt Type
CTVAL 18 Cnt Value
SPNM 18 SName
SPTP 18 S Type
SPVL 18 S Value
STAT 18 Status

Data -
Record 1 -
Cnd Type Cnd ID Key Effective from
CN1 ID1 KY1 20210210

Name Contract Type Contract Value
Cn FL String N

Record 1 -

Cnd Type Cnd ID Key Effective from
CN1 ID1 KY1 20210210

Name Contract Type Contract Value
SP FL Boolean TRUE



<Data_Set>
<Data_Record>
<Type>CN1</Type>
<Tbl_ID>ID1</Tbl_ID>
<Key>KY1</Key>
<From_Date>20210210</From_Date>

<El_Record>
<Name>CN FL</Name>
<Type>String</Type>
<String_Value>N</String_Value>
</El_Record>

<El_Record>
<Name>SP FL</Name>
<Type>boolean</Type>
<String_Value>TRUE</String_Value>
</El_Record>

</Data_Record>
</Data_Set>

[instrucrat1]

Thank you very much for all details and apology if asking duplicate info again and again.
I am doing it in SQLRPGLE program so below detailed queries are failing in SQLRPGLE. In above example XMLAGG can I add only one child element?
File with 6 fields as below FLD1 to FLD6. I thought adding child elements is same straight forward procedure but looks like it is not easy as group by conversion.

<TABLE>
<TABL>NOHDSH</TABL> FLD1
<KEYF>29 </KEYF> FLD2
<DESC>FREIGHT CHARGES(/DESC> FLD3
<STRU>
<STRU1>NOHDSH</STRU1> FLD4
<STRU2>DASH2</STRU2> FLD5
<STRU3>DASH3</STRU3> FLD6
</STRU>
<STRU>
<STRU1>NOHDSH1</STRU1>
<STRU2>DASH21</STRU2>
<STRU3>DASH31</STRU3>
</STRU>

</TABLE>

[instrucrat1]

Thank you very much Sir for response. Actually requirement is like as below and file structure is like this


Data in file - 12 fields in file (field 11 and 12 are conditional and only used if required)

Type TblID Key From To Cur Qty UOM Value Time CFlag sFlag
J1 Rtest 000001/1234/1 20201120 20211131 USD 1 EA 1.00 20201101 16:19:18 tt

J1 Rtest 000001/1234/1 20201120 20211131 USD 1 EA 1.00 20201101 16:19:18 True

Fields in file can be changed to adjust group by but now requirement is as above.

Exec SQL
Select XmlDocument
(xmlgroup(Type as "TYPE",
TblID as "Tbl_ID",
Key as "Key",
From as "From_Date"
To as "To_Date"
Cur as "Cur_Code"
Qty as "Per_Quantity"
UOM as "U_Code"
Value as "Value"
Time as "Ext_Time"
Option Row "Data_Record"
Root "Data_Set"))
into :XMLDoc
From SYdd ;

Above query will give result without CFlag and sFlag and El_Record but I want data in below format. Not sure if possible but struggling with it


<Data_Set>
<Data_Record>
<Type>J1</Type>
<Tbl_ID>Rtest</Tbl_ID>
<Key>000001/1234/1</Key>
<From_Date>20201120</From_Date>
<To_Date>20211231</To_Date>
<Cur_Code>USD</Cur_Code>
<Per_Quantity>1</Per_Quantity>
<U_Code>EA</U_Code>
<Value>1.00</Value>
<Ext_Time>20201101 16:19:18</Ext_Time>

<El_Record>
<Name>CnFlg</Name>
<Type>String</Type>
<String_Value>tt</String_Value>
</El_Record>

<El_Record>
<Name>sFlag</Name>
<Type>boolean</Type>
<String_Value>True</String_Value>
</El_Record>

</Data_Record>
</Data_Set>

[Vectorspace]

It can be done

But you still cannot use xmlGroup, becuase xmlGroup is for grouping entire rows, whereas you want some columns grouped and some not grouped.

It is hard to tell because pasting into the forum post removed all the spacing in your table sample, but I am assuming in your sample table 'tt' is the CFlag column and 'True' is the sFlag column.
And therefore that a row will contain only CFlag OR sFlag, but not both. (Though my solution still works if both are specified).
And a blank value in CFlag/sFlag means that value is not specified and an El_Record section should not be included for it
And you want to group by all columns up to and including Time, then aggregate the CFlag and sFlags together.
And each grouping should be in its own separate Data_Record, all of which are in a single Data_Set

In which case, this is what I came up with, and how.

My sample table is identical to yours, except I added a third row.

First: Build the El_Record sections for cFlag and sFlag
xmlElement to provide El_Record name, containing an xmlForest to build the three xml tags
That is inside a case statement so null is returned instead of xmlElement, if the field is blank (this is important).
And also returning all fields from the SYdd table for use later.

select
  case when CFlag = '' then null else
    xmlElement(NAME "El_Record",
      xmlForest(
        'CnFlg'  as "Name",
        'String' as "Type",
        CFlag    as "String_Value"
      )
    )
    end as cFlagSection,

  case when sFlag = '' then null else
    xmlElement(NAME "El_Record",
      xmlForest(
        'sFlag'  as "Name",
        'boolean' as "Type",
        sFlag    as "String_Value"
      )
    )
  end as sFlagSection,
  s.*
from SYdd s;

For each row this will return column cFlagSection that will be null if CFlag was blank, else an instance of <El_Record>
And column sFlagSection, which is the same but for sFlag
No aggregation has happened yet


Now I wrap those two in an xmlConcat() to combine them into one field called flagGroup.

select
  xmlConcat(
    case when CFlag = '' then null else
      xmlElement(NAME "El_Record",
        xmlForest(
          'CnFlg'  as "Name",
          'String' as "Type",
          CFlag    as "String_Value"
        )
      )
      end,

    case when sFlag = '' then null else
      xmlElement(NAME "El_Record",
        xmlForest(
          'sFlag'   as "Name",
          'boolean' as "Type",
          sFlag     as "String_Value"
      )
    )
    end
  ) as flagSection,
  s.*
from SYdd s;

XMLConcat() ignores null values. So if one of them is null instead of xml (because of the case statement) it is skipped


Now we aggregate. And this requires using subselects. Which I have coded using Common Table Expressions (CTE) to make the SQL statement more readable
The original statement is wrapped in : "with elRecords as (select ... from SYdd s)". This presents it like a dummy read-only table that can then be used in a subsequent select: "with elRecords as (select ... from SYdd s) select ... from elRecords;".
It is far more readable than "select ... from table (select ... from SYdd s);". CTE's look like you are creating and then reading temporary intermediary tables, but you are not - it's all still a single SQL statement so does not have the performance drawbacks of intermediary tables

We use xmlElement to create the Data_Record element
Inside that we use xmlForest to add the columns for all the grouping fields, and xmlAgg() the flagSection column that contains all the El_Record xmls
And then we group by all the grouping fields

with elRecords as (
select
  xmlConcat(
    case when CFlag = '' then null else
      xmlElement(NAME "El_Record",
        xmlForest(
          'CnFlg'  as "Name",
          'String' as "Type",
          CFlag    as "String_Value"
        )
      )
      end,

    case when sFlag = '' then null else
      xmlElement(NAME "El_Record",
        xmlForest(
          'sFlag'  as "Name",
          'boolean' as "Type",
        sFlag    as "String_Value"
      )
    )
    end
  ) as flagSection,
  s.*
from SYdd s
)

select
  xmlElement(NAME "Data_Record",
    xmlForest(
      Type  as "Type",
      TblID as "Tbl_ID",
      Key   as "Key",
      From  as "From_Date",
      To    as "To_Date",
      Cur   as "Cur_Code",
      Qty   as "Per_Quantity",
      UOM   as "U_Code",
      Value as "Value",
      Time  as "Ext_Time"
    ),
    xmlAgg(flagSection)
  ) as record,
  Type,TblID,Key,From,To,Cur,Qty,UOM,Value,Time

from elRecords
group by Type,TblID,Key,From,To,Cur,Qty,UOM,Value,Time;

This will return two rows. One Data_Record that combines both TYPE J1 record into one, that contains two El_Record xmls - one for each aggregated CFlag and sFlag.
And one for my additional TYPE J2 record, that contains two El_Record xmls - one for each CFlag and sFlag value because my row had both populated
Note that it in addition to combining the grouping columns in xmlForest, it is also still returning the individual columns. This is for later use


Now we just have to aggregate these rows into one (with no grouping) under a single Data_Set xml tag. So again we will put the previous select into a CTE for readability

elRecords as (
select
  xmlConcat(
    case when CFlag = '' then null else
      xmlElement(NAME "El_Record",
        xmlForest(
          'CnFlg'  as "Name",
          'String' as "Type",
          CFlag    as "String_Value"
        )
      )
      end,

    case when sFlag = '' then null else
      xmlElement(NAME "El_Record",
        xmlForest(
          'sFlag'  as "Name",
          'boolean' as "Type",
        sFlag    as "String_Value"
      )
    )
    end
  ) as flagSection,
  s.*
from SYdd s
),

dataRecords as (
select
  xmlElement(NAME "Data_Record",
    xmlForest(
      Type  as "Type",
      TblID as "Tbl_ID",
      Key   as "Key",
      From  as "From_Date",
      To    as "To_Date",
      Cur   as "Cur_Code",
      Qty   as "Per_Quantity",
      UOM   as "U_Code",
      Value as "Value",
      Time  as "Ext_Time"
    ),
    xmlAgg(flagSection)
  ) as record,
  Type,TblID,Key,From,To,Cur,Qty,UOM,Value,Time

from elRecords
group by Type,TblID,Key,From,To,Cur,Qty,UOM,Value,Time
)

select
  xmlElement(NAME "Data_Set",
    xmlAgg(
     record
     order by Type,TblID,Key,From,To,Cur,Qty,UOM,Value,Time
    )
  ) as dataSet
from dataRecords;

An xmlElement called Data_Set, that contains the aggregation of all the Data_Records form all the rows
And we use the grouping fields to apply an order to the aggregation

This returned the following XML, which I think is correct?

<Data_Set>
  <Data_Record>
    <Type>J1</Type>
    <Tbl_ID>Rtest</Tbl_ID>
    <Key>000001/1234/1</Key>
    <From_Date>20201120</From_Date>
    <To_Date>20211131</To_Date>
    <Cur_Code>USD</Cur_Code>
    <Per_Quantity>1</Per_Quantity>
    <U_Code>EA</U_Code>
    <Value>1.00</Value>
    <Ext_Time>20201101 16:19:18</Ext_Time>
    <El_Record>
      <Name>CnFlg</Name>
      <Type>String</Type>
      <String_Value>tt</String_Value>
    </El_Record>
    <El_Record>
      <Name>sFlag</Name>
      <Type>boolean</Type>
      <String_Value>True</String_Value>
    </El_Record>
  </Data_Record>
  <Data_Record>
    <Type>J2</Type>
    <Tbl_ID>Rtest</Tbl_ID>
    <Key>000001/1234/1</Key>
    <From_Date>20201120</From_Date>
    <To_Date>20211131</To_Date>
    <Cur_Code>USD</Cur_Code>
    <Per_Quantity>1</Per_Quantity>
    <U_Code>EA</U_Code>
    <Value>1.00</Value>
    <Ext_Time>20201101 16:19:18</Ext_Time>
    <El_Record>
      <Name>CnFlg</Name>
      <Type>String</Type>
      <String_Value>xy</String_Value>
    </El_Record>
    <El_Record>
      <Name>sFlag</Name>
      <Type>boolean</Type>
      <String_Value>False</String_Value>
    </El_Record>
  </Data_Record>
</Data_Set>

[Vectorspace]

I saw your new comment on my earlier post. It is still hard to tell what you mean because of the missing spaces. In the reply edit box, the leftmost button switches the input from rich text to plain text - then when you paste it does not remove extra spaces. If you use that you can preserve the formatting (as long as you use [code] tags) and it's easier to see the structure.

[instrucrat1]

@Vectorspace thank you very much Sir. I am trying it out. Really amazing query.

[Vectorspace]

Sorry, but it's getting confusing when you reply to posts by adding comments to earlier posts. And every time you post sample XML it is using different field and column names? So I'm not sure if you have multiple tables and need to build multiple different XML docs, or if you are trying to build simplified examples.

Yes, xmlAgg() can only accept a single column. Because it's job is to combine the values for one column for every row.
e.g. for table/xml:

col1    col2
a       b
a       c
w       x
w       y

<xmldoc>
  <row>
   <val1>a</val1>
   <val2>b</val2>
   <val2>c</val2>
  </row>
  <row>
   <val1>w</val1>
   <val2>x</val2>
   <val2>y</val2>
  </row>
</xmldoc>

You would need to use xmlagg to combine the different values for col2 into one row:

select 
xmlElement(NAME "xmldoc",
  xmlElement(NAME "row",
    xmlElement(NAME "val1",col1),
    xmlAgg(
      xmlElement(NAME "val2",col2),
    )
  )
)
from myTable group by col1;

The xmlAgg() combines the (xml converted) values for col2 for all rows, for each col1 group.

To combine columns in the same row into one, you want xmlConcat()

I'm not sure what you're trying to concatenate since for your newest comment a few posts up, you provided xml but not an idea of what the table looked like.
If there is a specific table you are trying to build into a specific XML document, then please reply to this thread (not Comment) providing the table info (formatted as in my example in this post with column headings and rows, with accurate positioning and the XML document it should convert into, and I can tell you how to build the XML