Code:
- D MYXMLDOC S SQLTYPE(XML_CLOB_FILE)
- d MsgDta s 52a
- d ts s z inz
-
- EXEC SQL SET OPTION COMMIT=*NONE, DATFMT=*ISO, CLOSQLCSR=*ENDACTGRP;
- ts = %timestamp();
-
- CLEAR MYXMLDOC;
- MYXMLDOC_NAME = '/edi/po_' + %char(ts) + '.xml';
- MYXMLDOC_NL = %LEN(%TRIM(MYXMLDOC_NAME));
- MyXMLDoc_FO = SQFOVR; //Replace if exists
-
- Exec SQL
- WITH D01 AS (
- SELECT D01.TPID,D01.PO#,D01.REL,D01.CTL#,D01.MBX,D01.LCNT,
- XMLGROUP(case when PO101 <> ' ' then PO101 end as "PO101",
- case when PO102 <> 0 then PO102 end as "PO102",
- case when PO414 <> 0 then PO414 end as "PO414"
- ORDER BY D01.TPID,D01.PO#,D01.REL,D01.CTL#,D01.MBX,D01.LCNT
- OPTION ROW "PO1"
- ROOT "detail" )
- AS detail
- FROM EDI850DP100 D01
- GROUP BY D01.TPID,D01.PO#,D01.REL,D01.CTL#,D01.MBX,D01.LCNT )
- ,
- H04 AS (
- SELECT H04.TPID,H04.PO#,H04.REL,H04.CTL#,H04.MBX,
- XMLGROUP(DTM01 AS "DTM01",
- DTM02 AS "DTM02"
- ORDER BY H04.TPID,H04.PO#,H04.REL,H04.CTL#,H04.MBX
- OPTION ROW "Date"
- ROOT "DTM" )
- AS DTM
- FROM EDI850HP400 H04
- GROUP BY H04.TPID,H04.PO#,H04.REL,H04.CTL#,H04.MBX )
-
- SELECT XMLDOCUMENT(
- XMLELEMENT(NAME "order",
- XMLFOREST(H01.TPID AS "TPID",
- H01.PO# AS "PO",
- H04.DTM AS "DTM",
- XMLFOREST(
- D01.detail AS "detail"
- ) AS order_detail )))
- AS order_doc
- into :MyXMLDoc
- FROM EDI850HP100 H01
- left outer join H04 on H04.TPID = H01.TPID
- and H04.PO# = H01.PO#
- and H04.REL = H01.REL
- and H04.CTL# = H01.CTL#
- and H04.MBX = H01.MBX
- left outer join D01 on D01.TPID = H01.TPID
- and D01.PO# = H01.PO#
- and D01.REL = H01.REL
- and D01.CTL# = H01.CTL#
- and D01.MBX = H01.MBX
- ;
-
- If SQLCODE <> *Zeros;
- EXEC SQL Get Diagnostics Condition 1 :MsgDta = MESSAGE_TEXT;
- Dsply MsgDta;
- else;
- Dsply 'XML Document generated';
- EndIf;
- *InLR = *On;
-
-
- DSPLY Result of SELECT more than one row.
-
- <?xml version="1.0" encoding="UTF-8"?>
- <order><TPID>abc123 </TPID><PO>0000249343899002 </PO><DTM><DTM><Date><DTM01>037</DTM01><DTM02>20120910</DTM02></Date><Date><DTM01>038</DTM01><DTM02>20120915</DTM02></Date><Date><DTM01>063</DTM01><DTM02>20120917</DTM02></Date><Date><DTM01>064</DTM01><DTM02>20120910</DTM02></Date></DTM></DTM><ORDER_DETAIL><detail><detail><PO1><PO101>6 </PO101><PO102>2880</PO102></PO1></detail></detail></ORDER_DETAIL></order>
-
- The DTM wrote out 4 rows but the PO1 failed after the first one (and it’s #6???).!
-
- After removing the D01.LCNT I get all 14 PO1 rows;
-
- DSPLY XML Document generated
-
- <?xml version="1.0" encoding="UTF-8"?>
- <order><TPID>DOLLAR TREE KY </TPID><PO>0000249343899002 </PO><DTM><DTM><Date><DTM01>037</DTM01><DTM02>20120910</DTM02></Date><Date><DTM01>038</DTM01><DTM02>20120915</DTM02></Date><Date><DTM01>063</DTM01><DTM02>20120917</DTM02></Date><Date><DTM01>064</DTM01><DTM02>20120910</DTM02></Date></DTM></DTM><ORDER_DETAIL><detail><detail><PO1><PO101>1 </PO101><PO102>4800</PO102></PO1><PO1><PO101>2 </PO101><PO102>7200</PO102></PO1><PO1><PO101>3 </PO101><PO102>2400</PO102></PO1><PO1><PO101>4 </PO101><PO102>4320</PO102></PO1><PO1><PO101>5 </PO101><PO102>1200</PO102></PO1><PO1><PO101>6 </PO101><PO102>2880</PO102></PO1><PO1><PO101>7 </PO101><PO102>4800</PO102></PO1><PO1><PO101>8 </PO101><PO102>4320</PO102></PO1><PO1><PO101>9 </PO101><PO102>3600</PO102></PO1><PO1><PO101>10 </PO101><PO102>6000</PO102></PO1><PO1><PO101>11 </PO101><PO102>2400</PO102></PO1><PO1><PO101>12 </PO101><PO102>7200</PO102></PO1><PO1><PO101>13 </PO101><PO102>4320</PO102></PO1><PO1><PO101>14 </PO101><PO102>21600</PO102></PO1></detail></detail></ORDER_DETAIL></order>
-
|
|