SQL & PL/SQL :: Reading Data From XML Message And Send Result To Net Using Bulk Collect

We want to send the following XML message as IN parameter to the procedure

<?xml version="1.0" encoding="UTF-8" ?>
- <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
- <s:Body>
- <CalculateWEDBCached xmlns="http://wedb.GGG.Services/Contracts/">
- <skuItems xmlns:a="http://schemas.datacontract.org/2004/07/wedb.GGG.Services.Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
- <a:SkuItem>
[code].......

I need a procedure to get the records from the table for every SKU of XML message from the table and take one record for each SKU based on order precedence.And send the set of output records to the .net code. The order precedence is

If MODEL is not null that record should be high precedence and consider max lead_time
If MODEL and VK_UNIT is NULL then the precedence goes to STATE_ID.

The parameters in the procedure are

CREATE OR REPLACE PROCEDURE sku_proc
(
p_bu_id IN number,
p_model IN varchar2,
p_vk_unit IN XMLTYPE,
p_country_id IN varchar2,
[code].......

Query to get the data from the table based on the parameters.

SELECT bu_id,
model,
vk_unit,
country_id,
state_id,
Lead_Time,
[code].......

Here every SKU from XML message should be passed as p_vk_unit to VK_UNIT column in the query

210-39348
210-39348
210-39348
405-12132
340-30904
340-30904
340-30904
403-10890
403-10890
709-10007

Based on the above query assume we got the following records.

BU_ID MODEL VK_UNIT COUNTRY_ID STATE_ID LEAD_TIME FC
0 M123 210-39348 AB 0 20 A
0 M123 210-39348 AB 0 30 B
0 NULL 210-39348 AB 0 10 C
0 M123 405-12132 AB 0 10 A
0 NULL 340-30904 AB 0 30 C
0 M123 340-30904 AB 0 20 B
0 M123 340-30904 AB 0 10 A
0 NULL 403-10890 AB 0 10 B
0 M123 403-10890 AB 0 20 A
0 M123 709-10007 AB 0 10 B
0 NULL NULL AB 0 20 B
0 NULL NULL AB 1 30 A

Th final query has to return the following result to the OUT parameters p_Lead_Time p_FC of the procedure.

LEAD_TIME FC
30 B
10 A
20 B
20 A
10 B
30 A

How to implement my above requirement using BULK COLLECT.