Forum Stats

  • 3,826,393 Users
  • 2,260,641 Discussions
  • 7,896,930 Comments

Discussions

XQuery statement for where clause

Mameli75
Mameli75 Member Posts: 11 Green Ribbon

Hello,

having following XML :

<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>

How can update / modify only certain lines e.g. PurchaseOrderLine\LineNumber = 20 ?

tried in this way below but it's updating any DataArea/PurchaseOrder/PurchaseOrderLine/UserArea

select XMLQuery('copy $i := $p1 modify

           (for $j in $i/DataArea/PurchaseOrder/PurchaseOrderLine/UserArea where $i/DataArea/PurchaseOrder/PurchaseOrderLine/LineNumber=20

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

             {insert node $p2 into $j})

            return $i'

       PASSING BOD_SPEC AS "p1",

             XMLType('<Property><NameValue name="ln.ItemGroup" type="StringType">100</NameValue></Property>') AS "p2"    

        RETURNING CONTENT)

       from ln_BOD_decoration

       WHERE BOD_NAME = 'SyncPurchaseOrder2';

Thanks.

Br. Marco.

Tagged:

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,693 Blue Diamond
    Answer ✓
    [snip..]
    XMLQuery('copy $i := $p1 modify
               (for $j in $i/DataArea/PurchaseOrder/PurchaseOrderLine[LineNumber=20]/UserArea
                return  (# ora:child-element-name Property #)
                  {insert node $p2 into $j}
                )
                return $i'
    [snip..]
    
    <DataArea>
      <PurchaseOrder>
        <PurchaseOrderHeader/>
        <PurchaseOrderLine>
          <LineNumber>10</LineNumber>
          <Item>
            <ItemID>
              <ID>KR350</ID>
            </ItemID>
          </Item>
          <UserArea/>
        </PurchaseOrderLine>
        <PurchaseOrderLine>
          <LineNumber>20</LineNumber>
          <Item>
            <ItemID>
              <ID>KR100</ID>
            </ItemID>
          </Item>
          <UserArea>
            <Property>
              <NameValue name="ln.ItemGroup" type="StringType">100</NameValue>
            </Property>
          </UserArea>
        </PurchaseOrderLine>
      </PurchaseOrder>
    </DataArea>
    
    
    
    
    Mameli75

Answers

  • Paulzip
    Paulzip Member Posts: 8,693 Blue Diamond

    It's not entirely clear what you want to update then, here's your input formatted, show what you expect your output to be :

      <DataArea>
        <PurchaseOrder>
          <PurchaseOrderHeader/>
          <PurchaseOrderLine>
            <LineNumber>10</LineNumber>
            <Item>
              <ItemID>
                <ID>KR350</ID>
              </ItemID>
            </Item>
            <UserArea/>
          </PurchaseOrderLine>
          <PurchaseOrderLine>
            <LineNumber>20</LineNumber>
            <Item>
              <ItemID>
                <ID>KR100</ID>
              </ItemID>
            </Item>
            <UserArea/>
          </PurchaseOrderLine>
        </PurchaseOrder>
      </DataArea>
    
    Mameli75
  • Mameli75
    Mameli75 Member Posts: 11 Green Ribbon

    Hello,

    I would like to add "Property" element only to PurchaseOrderLine/UserArea has /LineNumber = 20.

    <DataArea>
        <PurchaseOrder>
          <PurchaseOrderHeader/>
          <PurchaseOrderLine>
            <LineNumber>10</LineNumber>
            <Item>
              <ItemID>
                <ID>KR350</ID>
              </ItemID>
            </Item>
            <UserArea/>
          </PurchaseOrderLine>
          <PurchaseOrderLine>
            <LineNumber>20</LineNumber>
            <Item>
              <ItemID>
                <ID>KR100</ID>
              </ItemID>
            </Item>
           <UserArea>
            <Property><NameValue name="ln.ItemGroup" type="StringType">100</NameValue></Property>
           </UserArea>
          </PurchaseOrderLine>
        </PurchaseOrder>
      </DataArea>
    

    Thanks. Br. Marco.

  • Paulzip
    Paulzip Member Posts: 8,693 Blue Diamond
    Answer ✓
    [snip..]
    XMLQuery('copy $i := $p1 modify
               (for $j in $i/DataArea/PurchaseOrder/PurchaseOrderLine[LineNumber=20]/UserArea
                return  (# ora:child-element-name Property #)
                  {insert node $p2 into $j}
                )
                return $i'
    [snip..]
    
    <DataArea>
      <PurchaseOrder>
        <PurchaseOrderHeader/>
        <PurchaseOrderLine>
          <LineNumber>10</LineNumber>
          <Item>
            <ItemID>
              <ID>KR350</ID>
            </ItemID>
          </Item>
          <UserArea/>
        </PurchaseOrderLine>
        <PurchaseOrderLine>
          <LineNumber>20</LineNumber>
          <Item>
            <ItemID>
              <ID>KR100</ID>
            </ItemID>
          </Item>
          <UserArea>
            <Property>
              <NameValue name="ln.ItemGroup" type="StringType">100</NameValue>
            </Property>
          </UserArea>
        </PurchaseOrderLine>
      </PurchaseOrder>
    </DataArea>
    
    
    
    
    Mameli75