Forum Stats

  • 3,826,400 Users
  • 2,260,641 Discussions
  • 7,896,931 Comments

Discussions

XMLQuery select and where clause

Mameli75
Mameli75 Member Posts: 11 Green Ribbon

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.

Tagged:

Answers

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,098 Silver Trophy

    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;