Announcement

**Vectorspace** · January 18, 2019, 02:38 AM

I don't follow. Do you want to take XML and insert it into the table, or take the table and output it as XML?

If the former, here is a primer on using the XMLTABLE() DB2 SQL function to present XML data (in an XML data type column/var) as relational data for querying or inserting into tables

https://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/index.html

If the latter, here is a primer on the XML build DB2 SQL functions for generating XML from relational data

IBM Developer

https://developer.ibm.com/articles/dm-0511melnyk/

IBM Developer is your one-stop location for getting hands-on training and learning in-demand skills on relevant technologies such as generative AI, data science, AI, and open source.

**vinomails** · January 18, 2019, 05:25 AM

Hi Vector, i am trying to generate XML build using DB2 SQL. I am struck due to multiple nested elements.

**Vectorspace** · January 22, 2019, 05:44 AM

Well that nearly broke my brain. But here is how I did it, with a step by step explanation:

Code:

-- I am using a CTE to create dummy table MYDATA, as the source data
  with mydata as (
select * from
    (values('AAA', 'bbb', '88888', '1111', '2018-12-03', '17:02:29', '1030', '11', 'NON-ROA', 211.12),
           ('AAA', 'bbb', '88888', '1111', '2018-12-03', '17:02:29', '2260', '11', 'NON-ROA', -49.53),
           ('AAA', 'bbb', '99999', '6666', '2018-12-03', '17:02:16', '1030', '12', 'NON-ROA', 211.12),
           ('AAA', 'bbb', '99999', '6666', '2018-12-03', '17:02:16', '2260', '12', 'NON-ROA', -29.77)
     ) z (org,store_group,store,gl_location,close_date,close_time,account,fop,sale_type,amount))
select * from mydata;


----------------------------------------
-- Now let's start by generating the SEG6 XML for every record
-- This will return 4 records, each record including the SEG6 xml for that record
  with mydata as (
select * from
    (values('AAA', 'bbb', '88888', '1111', '2018-12-03', '17:02:29', '1030', '11', 'NON-ROA', 211.12),
           ('AAA', 'bbb', '88888', '1111', '2018-12-03', '17:02:29', '2260', '11', 'NON-ROA', -49.53),
           ('AAA', 'bbb', '99999', '6666', '2018-12-03', '17:02:16', '1030', '12', 'NON-ROA', 211.12),
           ('AAA', 'bbb', '99999', '6666', '2018-12-03', '17:02:16', '2260', '12', 'NON-ROA', -29.77)
     ) z (org,store_group,store,gl_location,close_date,close_time,account,fop,sale_type,amount))

select org,store_group,store,gl_location,close_date,close_time,
       xmlelement(name "SEG6",
               xmlelement(name "ACCOUNT",account),
               xmlelement(name "FOP",fop),
               xmlelement(name "SALE_TYPE",sale_type),
               xmlelement(name "AMOUNT",amount)
       ) as seg6
  from mydata f;


----------------------------------------
-- Now we need to "aggregate" the SEG6 records - to group them together into a list of SEG6 tags, for every store/gl_location/date/time
-- Aggregation, with XMLAGG, works like any SQL aggregate function, like count() or max(). You use the group by clause to determine the grouping
-- The Seg6 column should contain the XML for two SEG6 XML elements
-- Note that you can specify an order by for an XMLAGG
-- This will return 2 records, one for each unique combination of org,store_group,store,gl_location,close_date,close_time
-- Each includes a column containing a list of all the SEG6's for that combo. Should be two in each
  with mydata as (
select * from
    (values('AAA', 'bbb', '88888', '1111', '2018-12-03', '17:02:29', '1030', '11', 'NON-ROA', 211.12),
           ('AAA', 'bbb', '88888', '1111', '2018-12-03', '17:02:29', '2260', '11', 'NON-ROA', -49.53),
           ('AAA', 'bbb', '99999', '6666', '2018-12-03', '17:02:16', '1030', '12', 'NON-ROA', 211.12),
           ('AAA', 'bbb', '99999', '6666', '2018-12-03', '17:02:16', '2260', '12', 'NON-ROA', -29.77)
     ) z (org,store_group,store,gl_location,close_date,close_time,account,fop,sale_type,amount))

select org,store_group,store,gl_location,close_date,close_time,
        xmlagg(
           xmlelement(name "SEG6",
                   xmlelement(name "ACCOUNT",account),
                   xmlelement(name "FOP",fop),
                   xmlelement(name "SALE_TYPE",sale_type),
                   xmlelement(name "AMOUNT",amount)
           )
           order by account, fop, sale_type, amount
        ) as seg6
  from mydata f
 group by org,store_group,store,gl_location,close_date,close_time;


----------------------------------------
-- Now we may as well add in the XML for SEG5, to wrap each reord's SEG6 list. And add in STORE, GL_LOCATION, and CLOSE_DATE
-- I have kept store and gl_loaction as separate output fields for later use (order of a later xmlagg)
-- This will return 2 records, one for each unique combination of org,store_group,store,gl_location,close_date,close_time
-- Each includes a column containing the SEG5 XML for that combo (which contains a list of all the SEG6's for that combo)
  with mydata as (
select * from
    (values('AAA', 'bbb', '88888', '1111', '2018-12-03', '17:02:29', '1030', '11', 'NON-ROA', 211.12),
           ('AAA', 'bbb', '88888', '1111', '2018-12-03', '17:02:29', '2260', '11', 'NON-ROA', -49.53),
           ('AAA', 'bbb', '99999', '6666', '2018-12-03', '17:02:16', '1030', '12', 'NON-ROA', 211.12),
           ('AAA', 'bbb', '99999', '6666', '2018-12-03', '17:02:16', '2260', '12', 'NON-ROA', -29.77)
     ) z (org,store_group,store,gl_location,close_date,close_time,account,fop,sale_type,amount))

select org,store_group,store,gl_location,
            xmlelement(name "SEG5",
               xmlelement(name "STORE",store),
               xmlelement(name "GL_LOCATION",gl_location),
               xmlelement(name "CLOSE_DATE",
                  xmlelement(name "DATE",close_date || ' ' || close_time),
                  xmlagg(xmlelement(name "SEG6",
                           xmlelement(name "ACCOUNT",account),
                           xmlelement(name "FOP",fop),
                           xmlelement(name "SALE_TYPE",sale_type),
                           xmlelement(name "AMOUNT",amount)
                        ) order by account, fop, sale_type, amount)
               )
            ) as seg5
  from mydata f
 group by org,store_group,store,gl_location,close_date,close_time;


----------------------------------------
-- Now the next logical step would be to aggregate again, to group the two SEG5's per ORG/STORE_GROUP
-- But you cannot have to aggregation levels in a single select.
-- So instead, let's wrap it in a subselect first
-- Other than that, this will return identical to the previous
  with mydata as (
select * from
    (values('AAA', 'bbb', '88888', '1111', '2018-12-03', '17:02:29', '1030', '11', 'NON-ROA', 211.12),
           ('AAA', 'bbb', '88888', '1111', '2018-12-03', '17:02:29', '2260', '11', 'NON-ROA', -49.53),
           ('AAA', 'bbb', '99999', '6666', '2018-12-03', '17:02:16', '1030', '12', 'NON-ROA', 211.12),
           ('AAA', 'bbb', '99999', '6666', '2018-12-03', '17:02:16', '2260', '12', 'NON-ROA', -29.77)
     ) z (org,store_group,store,gl_location,close_date,close_time,account,fop,sale_type,amount))

select * from table(
    select org,store_group,store,gl_location,
                xmlelement(name "SEG5",
                   xmlelement(name "STORE",store),
                   xmlelement(name "GL_LOCATION",gl_location),
                   xmlelement(name "CLOSE_DATE",
                      xmlelement(name "DATE",close_date || ' ' || close_time),
                      xmlagg(xmlelement(name "SEG6",
                               xmlelement(name "ACCOUNT",account),
                               xmlelement(name "FOP",fop),
                               xmlelement(name "SALE_TYPE",sale_type),
                               xmlelement(name "AMOUNT",amount)
                            ) order by account, fop, sale_type, amount)
                   )
                ) as seg5
      from mydata f
     group by org,store_group,store,gl_location,close_date,close_time
    ) as seg5Table
 ;


----------------------------------------
-- And now, finally, we can aggregate the SEG5 xml to get one XML per ORG/STORE_GROUP
-- We will add in the SI top level wrapper and ORG/STORE_GROUP tags at the same time
-- This will complete the XML
-- note this returns the xml as the XML data type. You would have to wrap in in xmlserialize() if you want the string.
-- if you use Run SQL Scripts from iACS then the query output will display the string value for you.
  with mydata as (
select * from
    (values('AAA', 'bbb', '88888', '1111', '2018-12-03', '17:02:29', '1030', '11', 'NON-ROA', 211.12),
           ('AAA', 'bbb', '88888', '1111', '2018-12-03', '17:02:29', '2260', '11', 'NON-ROA', -49.53),
           ('AAA', 'bbb', '99999', '6666', '2018-12-03', '17:02:16', '1030', '12', 'NON-ROA', 211.12),
           ('AAA', 'bbb', '99999', '6666', '2018-12-03', '17:02:16', '2260', '12', 'NON-ROA', -29.77)
     ) z (org,store_group,store,gl_location,close_date,close_time,account,fop,sale_type,amount))

select xmlelement(name "SI",
            xmlelement(name "ORG",org),
            xmlelement(name "STORE_GROUP",store_group),
            xmlagg(seg5 order by store,gl_location))
  from table(
    select org,store_group,store,gl_location,
                xmlelement(name "SEG5",
                   xmlelement(name "STORE",store),
                   xmlelement(name "GL_LOCATION",gl_location),
                   xmlelement(name "CLOSE_DATE",
                      xmlelement(name "DATE",close_date || ' ' || close_time),
                      xmlagg(xmlelement(name "SEG6",
                               xmlelement(name "ACCOUNT",account),
                               xmlelement(name "FOP",fop),
                               xmlelement(name "SALE_TYPE",sale_type),
                               xmlelement(name "AMOUNT",amount)
                            ) order by account, fop, sale_type, amount)
                   )
                ) as seg5
      from mydata f
     group by org,store_group,store,gl_location,close_date,close_time
    ) as seg5Table
 group by org,store_group
 ;

**vinomails** · January 22, 2019, 02:53 PM

Thanks a lot vector. I learned it. Good Explanation.

**vinomails** · January 23, 2019, 10:22 AM

In general is there any size limit to the XML document. How many rows i can use to populate this kind of data?

**B.Hauser** · January 24, 2019, 12:17 AM

If you are writing the result into a RPG XML_CLOB Field the maximum is 16MB (RPG Restriction).
If you are writing the result (with SQL) directly into the IFS (output into a file reference variable XML_CLOB_FILE) the maximum is 2GB

Birgitta

**Vectorspace** · January 24, 2019, 02:39 AM

And I assume, if you are writing it to a regular char or varchar, it would be 32k unless you use teraspace?

What is the maximum size of an XML column in a DB2 table, I wonder?

**B.Hauser** · January 24, 2019, 09:08 AM

Originally posted by Vectorspace View Post

And I assume, if you are writing it to a regular char or varchar, it would be 32k unless you use teraspace?

What is the maximum size of an XML column in a DB2 table, I wonder?

If you define your receiver variable as VARCHAR or CHAR in RPG the maximum is 32 K (SQL restriction for CHAR and VARCHAR Columns/Variable - larger SQL variables must be defined as Large Object - CLOB, BLOB, DBCLOB)
If you define your receiver variable as SQLTYPE(XML_CLOB: Length) the maximum length is 16MB (RPG Restriction for Variables).
SQL does not handle pointers, so a pointer cannot be used and it is not possible to profit from the TeraSpace.
If you define your receiver variable as Locator (kind of pointer in SQL) SQLTYPE(XML_CLOB_LOCATOR) or File Reference Variable SQLTYPE(XML_FILE) the maximum length is 2 GB.
The maximum length for an XML column, i.e. for the XML data type is 2 GB.

Birgitta

Announcement

SQL/XML - Creation

SQL/XML - Creation

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment