Skip to Main Content
  • Questions
  • Break the results in select and put them in an XML

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Usha Sai Padmaja.

Asked: July 19, 2017 - 10:58 am UTC

Last updated: July 21, 2017 - 3:57 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Team,

I have a table Employee as below

Employee_ID  Employee_Name
1            Usha 
2            Soma
3            Uma

.
.
. and so on I have 5000 records in the table

I have a defined count only 1000 records can be processed at a time
I need to divide 5000 by 1000 and with the result I can built 5 XML's(with 1000 employee numbers in each XML)
The XML can be as below

<?xml version="1.0" encoding="UTF-8"?>
<n1:Request xmlns:n1="urn:global:cs:common">

                      <n1:EmpNumber>1</n1:EmpNumber>
                      <n1:EmpNumber>2</n1:EmpNumber>
                      <n1:EmpNumber>3</n1:EmpNumber>
                      .
                      .
                      <n1:EmpNumber>1000</n1:EmpNumber>
</n1:Request>



once I create an XML with 1000 Employee Numbers I will put them in a table as below

Domain       InputXML

Employee   <XML1>(Cotaining 1000 Employee Numbers)
Employee   <XML1>(Cotaining 1000 Employee Numbers)
Employee   <XML1>(Cotaining 1000 Employee Numbers)
Employee   <XML1>(Cotaining 1000 Employee Numbers)
Employee   <XML1>(Cotaining 1000 Employee Numbers)



My question is how efficiently can I do it with a PL/SQL Stored Procedure.



and Chris said...

First create an XML fragment for each row using XMLForest, XMLElement or whatever.

Then assign each row to a group. You can do this by:

- Calculating the row_number() for each
- Dividing this by however many rows you want in each group
- Taking the CEILing of this

Then all you need to do is group by this calculation and XMLAGG() the fragments to combine them:

with grps as (
  select ceil(row_number() over (order by employee_id) / 20) grp, 
         xmlforest(employee_id emp_id) xdoc
  from   hr.employees
)
  select grp, xmlagg(xdoc) from grps
  group  by grp;

       GRP
----------
XMLAGG(XDOC)
------------------------------------------------------------------------------------------------------------------------
----------------------------------------
         1
<EMP_ID>100</EMP_ID><EMP_ID>119</EMP_ID><EMP_ID>118</EMP_ID><EMP_ID>117</EMP_ID><EMP_ID>116</EMP_ID><EMP_ID>115</EMP_ID>
<EMP_ID>114</EMP_ID><EMP_ID>113</EMP_ID>
<EMP_ID>112</EMP_ID><EMP_ID>111</EMP_ID><EMP_ID>110</EMP_ID><EMP_ID>109</EMP_ID><EMP_ID>108</EMP_ID><EMP_ID>107</EMP_ID>
<EMP_ID>106</EMP_ID><EMP_ID>105</EMP_ID>
<EMP_ID>104</EMP_ID><EMP_ID>103</EMP_ID><EMP_ID>102</EMP_ID><EMP_ID>101</EMP_ID>

         2
<EMP_ID>120</EMP_ID><EMP_ID>139</EMP_ID><EMP_ID>138</EMP_ID><EMP_ID>137</EMP_ID><EMP_ID>136</EMP_ID><EMP_ID>135</EMP_ID>
<EMP_ID>134</EMP_ID><EMP_ID>133</EMP_ID>
<EMP_ID>132</EMP_ID><EMP_ID>131</EMP_ID><EMP_ID>130</EMP_ID><EMP_ID>129</EMP_ID><EMP_ID>128</EMP_ID><EMP_ID>127</EMP_ID>
<EMP_ID>126</EMP_ID><EMP_ID>125</EMP_ID>
<EMP_ID>124</EMP_ID><EMP_ID>123</EMP_ID><EMP_ID>122</EMP_ID><EMP_ID>121</EMP_ID>

         3
<EMP_ID>140</EMP_ID><EMP_ID>159</EMP_ID><EMP_ID>158</EMP_ID><EMP_ID>157</EMP_ID><EMP_ID>156</EMP_ID><EMP_ID>155</EMP_ID>
<EMP_ID>154</EMP_ID><EMP_ID>153</EMP_ID>
<EMP_ID>152</EMP_ID><EMP_ID>151</EMP_ID><EMP_ID>150</EMP_ID><EMP_ID>149</EMP_ID><EMP_ID>148</EMP_ID><EMP_ID>147</EMP_ID>
<EMP_ID>146</EMP_ID><EMP_ID>145</EMP_ID>
<EMP_ID>144</EMP_ID><EMP_ID>143</EMP_ID><EMP_ID>142</EMP_ID><EMP_ID>141</EMP_ID>

         4
<EMP_ID>160</EMP_ID><EMP_ID>179</EMP_ID><EMP_ID>178</EMP_ID><EMP_ID>177</EMP_ID><EMP_ID>176</EMP_ID><EMP_ID>175</EMP_ID>
<EMP_ID>174</EMP_ID><EMP_ID>173</EMP_ID>
<EMP_ID>172</EMP_ID><EMP_ID>171</EMP_ID><EMP_ID>170</EMP_ID><EMP_ID>169</EMP_ID><EMP_ID>168</EMP_ID><EMP_ID>167</EMP_ID>
<EMP_ID>166</EMP_ID><EMP_ID>165</EMP_ID>
<EMP_ID>164</EMP_ID><EMP_ID>163</EMP_ID><EMP_ID>162</EMP_ID><EMP_ID>161</EMP_ID>

         5
<EMP_ID>180</EMP_ID><EMP_ID>199</EMP_ID><EMP_ID>198</EMP_ID><EMP_ID>197</EMP_ID><EMP_ID>196</EMP_ID><EMP_ID>195</EMP_ID>
<EMP_ID>194</EMP_ID><EMP_ID>193</EMP_ID>
<EMP_ID>192</EMP_ID><EMP_ID>191</EMP_ID><EMP_ID>190</EMP_ID><EMP_ID>189</EMP_ID><EMP_ID>188</EMP_ID><EMP_ID>187</EMP_ID>
<EMP_ID>186</EMP_ID><EMP_ID>185</EMP_ID>
<EMP_ID>184</EMP_ID><EMP_ID>183</EMP_ID><EMP_ID>182</EMP_ID><EMP_ID>181</EMP_ID>

         6
<EMP_ID>200</EMP_ID><EMP_ID>206</EMP_ID><EMP_ID>205</EMP_ID><EMP_ID>204</EMP_ID><EMP_ID>203</EMP_ID><EMP_ID>202</EMP_ID>
<EMP_ID>201</EMP_ID>


6 rows selected.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very Good

Usha S, July 21, 2017 - 12:50 pm UTC

It is such a great query .

Usha S, July 21, 2017 - 12:50 pm UTC

Again Thanks a lot team .


Usha S, July 21, 2017 - 3:03 pm UTC

I have tried it by inserting it in an function as follows

create or replace FUNCTION F_GI_CONSTRUCT_XML

( V_FROM_TS IN varchar2,
V_TO_TS IN varchar2,
Domain_name IN varchar2 )


RETURN VARCHAR2 AS

V_MAINQUERY clob;
MAINCURSOR SYS_REFCURSOR ;
final_XML XMLTYPE;
V_CURVAL NUMBER(38);

BEGIN

DBMS_OUTPUT.PUT_LINE('hii');

V_MAINQUERY:='

with grps as (

select ceil(row_number() over ( order by nos)/1000) grp,
xmlforest(nos as "Number") xdoc
from(

select Distinct EMPLOYEEPID as nos from(
SELECT A.EMPLOYEEPID
FROM IMPP.EMPLOYEE A ,IMPP.ORGANIZATION B
WHERE A.LASTUPDATEDATE BETWEEN TO_DATE('''||V_FROM_TS ||''',''YYYY-MM-DD HH24:MI:SS'')
AND TO_DATE('''||V_TO_TS ||''',''YYYY-MM-DD HH24:MI:SS'')
union
SELECT A.EMPLOYEEPID
FROM IMPP.EMPLOYEE a,IMPP.ORGANIZATION B
WHERE A.LASTUPDATEDATE BETWEEN TO_DATE('''||V_FROM_TS ||''',''YYYY-MM-DD HH24:MI:SS'')
AND TO_DATE('''||V_TO_TS ||''',''YYYY-MM-DD HH24:MI:SS'')
)

))
select grp, xmlagg(xdoc) from grps
group by grp

';

--DBMS_OUTPUT.put_line(V_MAINQUERY);

OPEN MAINCURSOR FOR V_MAINQUERY;

DBMS_OUTPUT.put_line('Hii2');

LOOP
FETCH MAINCURSOR INTO V_CURVAL,final_XML;
DBMS_OUTPUT.put_line('hii'|| V_CURVAL);

EXIT WHEN MAINCURSOR%NOTFOUND;



final_XML:='<?xml version="1.0" encoding="UTF-8"?>
<n1:Request xmlns:n1="urn:global:cs:common">
<n1:Conditions>
<n1:Numbers>'||
final_XML ||
'</n1:Numbers>
</n1:Conditions>
</n1:Request>
';

insert into IMPP.INPUT_XML values('EMPLOYEE',final_XML);
COMMIT;
END LOOP;

RETURN NULL;
END F_GI_CONSTRUCT_XML;

but I get some data mismatch errors
Chris Saxon
July 21, 2017 - 3:46 pm UTC

What error do you get and what's the DDL for all your tables?

Usha S, July 21, 2017 - 3:48 pm UTC

I could finish my function and rectified all my errors. Again Thanks a lot team for making it so easy with a super fine query.I am sorry for the previous review comment.
Chris Saxon
July 21, 2017 - 3:57 pm UTC

No problem.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.