XMLQuery select and where clause

Mameli75

Hello,

Every PO line needs it's own <property name="ln.ItemGroup"> element in UserArea:

po line 10 has R350, getItemGroupByItem('KR350', ..., ...)

po line 20 has R100, getItemGroupByItem('KR100', ..., ...)

I've got the correct results but not has expected ... I have twice for each po line while each one should have it's own as above ... where is it wrong? i have added the clause "where i$/DataArea ..." but maybe I do not understand how that works.

<DataArea>

<PurchaseOrder>

  <PurchaseOrderHeader>

...

  </PurchaseOrderHeader>

<PurchaseOrderLine>

<LineNumber>10</LineNumber>

<Item>

<ItemID>

<ID>KR350</ID>

</ItemID>

</Item>

<UserArea>

...

</UserArea>

</PurchaseOrderLine>

<PurchaseOrderLine>

<LineNumber>20</LineNumber>

<Item>

<ItemID>

<ID>KR100</ID>

</ItemID>

</Item>

<UserArea>

...

</UserArea>

</PurchaseOrderLine>

</PurchaseOrder>

</DataArea>

FOR r IN (

     SELECT ExtractValue(Value(p),'/PurchaseOrderLine/Item/ItemID/ID/text()') as itemId,

         ExtractValue(Value(p),'/PurchaseOrderLine/LineNumber/text()') as lineNumber

     FROM  TABLE(XMLSequence(Extract(xmlContent,'DataArea/PurchaseOrder/PurchaseOrderLine'))) p

    ) LOOP

 BEGIN

ItemGroup := getItemGroupByItem(trim(r.itemId), AccountingEntityID, LogicalID);

xml_string := '<Property><NameValue name="ln.ItemGroup" type="StringType">'||ItemGroup||'</NameValue></Property>';

        

       UPDATE LN_BOD_DECORATION

       SET BOD_SPEC = 

       XMLQuery('copy $i := $p1 modify

           (for $j in $i/DataArea/PurchaseOrder/PurchaseOrderLine/UserArea where $i/DataArea/PurchaseOrder/PurchaseOrderLine/LineNumber =' || lineNumber 

            return (# ora:child-element-name Property #)

             {insert node $p2 into $j})

            return $i'

       PASSING BOD_SPEC AS "p1",

             XMLType(xml_string) AS "p2"    

        RETURNING CONTENT)

       WHERE BOD_NAME = 'SyncPurchaseOrder';

       COMMIT;

  

END;

END LOOP;

Thanks.

BR.

Marco.

Jason_(A_Non)

I'm not clear on what you are trying to accomplish in terms of a final XML given you didn't provide any examples. You also didn't provide the version of Oracle you are working with. I started a test shell for you simply because. I updated your parsing of variable "xmlContent" to be an approach that has been supported for a while now. Feel free to turn this into a working test case

declare 
  xmlContent XMLTYPE;
begin
  xmlContent := XMLType('<DataArea>
<PurchaseOrder>
 <PurchaseOrderHeader>
 </PurchaseOrderHeader>
<PurchaseOrderLine>
<LineNumber>10</LineNumber>
<Item>
<ItemID>
<ID>KR350</ID>
</ItemID>
</Item>
<UserArea>
</UserArea>
</PurchaseOrderLine>
<PurchaseOrderLine>
<LineNumber>20</LineNumber>
<Item>
<ItemID>
<ID>KR100</ID>
</ItemID>
</Item>
<UserArea>
</UserArea>
</PurchaseOrderLine>
</PurchaseOrder>
</DataArea>');
  FOR r IN (
            SELECT xt.item_id, xt.line_id
              FROM XMLTABLE('/DataArea/PurchaseOrder/PurchaseOrderLine'
                            PASSING XMLTYPE(xmlContent
                            COLUMNS
                            item_id   VARCHAR2(20) PATH 'Item/ItemID/ID',
                            line_id   VARCHAR2(20) PATH 'LineNumber') xt
           )
  LOOP
     BEGIN
        ItemGroup := getItemGroupByItem(trim(r.itemId), AccountingEntityID, LogicalID);
        xml_string := '<Property><NameValue name="ln.ItemGroup" type="StringType">'||ItemGroup||'</NameValue></Property>';

        UPDATE LN_BOD_DECORATION
        SET BOD_SPEC = 
        XMLQuery('copy $i := $p1 modify
            (for $j in $i/DataArea/PurchaseOrder/PurchaseOrderLine[LineNumber=$lineNumber]/UserArea
            return (# ora:child-element-name Property #)
              {insert node $p2 into $j})
             return $i'
        PASSING BOD_SPEC AS "p1",
                r.line_id AS "lineNumber",
                XMLType(xml_string) AS "p2"   
         RETURNING CONTENT)
        WHERE BOD_NAME = 'SyncPurchaseOrder';
        COMMIT;
     END;
  END LOOP;
end;