Home » Developer & Programmer » JDeveloper, Java & XML » XMLELEMENT/ XMLAGG functions
| XMLELEMENT/ XMLAGG functions [message #199658] |
Wed, 25 October 2006 10:51  |
grover.nitin
Messages: 1
Registered: October 2006
|
Junior Member |
|
|
Hi, I have a query to generate XML data. I want to get the result of this query into a clob variable. I am using SQL* plus 8.0.5.0.0 client.
SELECT CAST(xmlelement("itemMaster",
xmlelement("itemNumber",lpad(d.dept_i,3,0)||lpad(d.class_i,2,0)||lpad(d.item_i,4,0)),
xmlelement("itemDesc",NVL(d.ITEM_DESC_T,lpad(d.dept_i,3,0)||lpad(d.class_i,2,0)||lpad(d.item_i,4,0))),
xmlelement("paltSize",dit_wm_intface_get_paltsize(d.dept_i,d.class_i,d.item_i)),
xmlelement("stdUOM",dit_wm_intface_get_stduom),
xmlelement("sellByDayQty",d.sell_by_day_q),
xmlelement("shflfUOM",dit_wm_intface_get_shflfUOM),
xmlelement("minShflfDayQty",d.min_shflf_day_q),
xmlelement("itemCatgCode",NVL(d.dc_item_catg_c,'GM')),
xmlelement("strgTempZoneCode",NVL(d.strg_tmpr_zone_c,'01')),
xmlelement("retailAmt",retl_a),
xmlelement("expireDateFlag",NVL(d.expire_date_f,'N')),
xmlelement("randomWtFlag",NVL(d.rndm_wt_f,'N')),
xmlelement("wetFlag",NVL(d.wet_f,'N')),
xmlelement("areaCode",d.area_c),
xmlelement("vcpQty",d.vcp_q),
xmlelement("sspQty",d.ssp_q),
xmlelement("hndlTypeCode",d.hndl_type_c),
xmlelement("mstrPackTypeCode",d.mstr_pk_type_c),
xmlelement("mstrItemTypeCode",NVL(d.mstr_item_type_c,'00')),
xmlelement("sszCode",d.reg_ssz_excpt_c),
xmlelement("agriFlag",NVL(d.agrl_f,'N')),
xmlelement("itemRankCode",d.item_rank_c),
xmlelement("hazardFlag",NVL(d.hazd_mtrl_f,'N')),
xmlelement("createDate",to_char(d.create_d,'MM-DD-YYYY HH24:MI:SS')),
xmlelement("updateDate",to_char(d.modf_ts,'MM-DD-YYYY HH24:MI:SS')),
xmlelement("actvnDate",to_char(d.actvn_d,'MM-DD-YYYY HH24:MI:SS')),
xmlelement("organicCode",d.ognc_c),
xmlelement("lotCntlFlag",NVL(d.lot_cntl_f,'N')),
xmlelement("flammableFlag",NVL(d.flam_c,'N')),
xmlelement("recordMode",c.action_c),
xmlelement("areaType",dit_wm_intface_get_areaType),
xmlelement("uoms",
xmlelement("uom",
xmlelement("prodUOM",dit_wm_intface_get_prodUOM),
xmlelement("ratioDen",dit_wm_intface_get_ratioDen),
xmlelement("consldtRule",dit_wm_intface_get_consldtRule),
xmlelement("unitWtQty",NVL(d.unit_wt_q,1)),
xmlelement("vcpHeightQty",NVL(d.vcp_ht_q,1)),
xmlelement("vcpWidthQty",NVL(d.vcp_wth_q,1)),
xmlelement("vcpLengthQty",NVL(d.vcp_lgth_q,1)),
xmlelement("layerQty",dit_wm_intface_get_layerQty)
)
),
(SELECT xmlelement("aliases",
xmlagg(xmlelement("upcCode",trunc(b.bar_code_i))
ORDER BY b.dept_i,b.class_i,b.item_i
)
)
FROM dc_item_bar_code b,
dc_item a
WHERE a.dept_i = b.dept_i
AND a.class_i = b.class_i
AND a.item_i = b.item_i
AND a.dept_i = c.dept_i
AND a.class_i = c.class_i
AND a.item_i = c.item_i
)
)
AS VARCHAR2(4000)
)
FROM dc_item d, item_wm_trigger_w c
WHERE d.dept_i = c.dept_i
AND d.class_i = c.class_i
AND d.item_i = c.item_i
/
Any help would be greatly appreciated.
Thanks,
Nitin
|
|
|
|
| Re: XMLELEMENT/ XMLAGG functions [message #201242 is a reply to message #199658] |
Fri, 03 November 2006 04:34  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you remove the CAST AS Varchar2(4000), then the result will be a SYS.XMLTYPE, which is a substype of CLOB
If you rewrite the query as
SELECT xmlelement("itemMaster",
...
...
).getclobval()
FROM dc_item d, item_wm_trigger_w c
WHERE d.dept_i = c.dept_i
AND d.class_i = c.class_i
AND d.item_i = c.item_i |
|
|
|
Goto Forum:
Current Time: Fri Nov 22 04:02:02 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
