replacing value of a node in XML using parameters to set a given node-CodePudding

I under stand the basics of "replacing" a value in xml in the very basic sample below.

But I have a more complicated XML. What I am trying to do is update the node of a given where the PurchaseOrderReference/documentid/id = 14500000999 and the <PurchaseOrderReference/lineNumber = 1

how do i add the "filter" for the PurchaseOrderReference/documentid/id and PurchaseOrderReference/lineNumber

I have tried many permutations in the modify but nothing works I either get an error that tells me nothing or it runs but no change is made.

declare @blocation nvarchar(50) set @blocation = 'Legs'
    select @blocation
    DECLARE @myDoc xml
    SET @myDoc = '<Workout>
    <BodyPart Location="Chest" 
      Reps="15" >Exercises in order.
    <Exercise>First exercise goes here</Exercise>
    <Exercise>Second exercise goes here</Exercise>
    </BodyPart>
    <BodyPart Location="Legs" 
      Reps="10" >Exercises in order.
    <Exercise>First exercise goes here</Exercise>
    <Exercise>Second exercise goes here</Exercise>
    </BodyPart>
    </Workout>'
   -- update text in the first exercise

    SET @myDoc.modify('
    replace value of 
 
 
 (/Workout/BodyPart[@Location=sql:variable("@blocation")]/Exercise[1]/text())[1]
  with     "Bench Press" ')

<ReceiveDeliveryItem>
        <Classification>
          <Codes>
            <Code listID="Classes" sequence="1">*</Code>
          </Codes>
        </Classification>
        <ServiceIndicator/>
        <PurchaseOrderReference>
          <DocumentID>
            <ID accountingEntity="9991" lid="lid://infor.eam.aim_prd"     location="9991">14500000999</ID>
          </DocumentID>
          <LineNumber>1</LineNumber>
        </PurchaseOrderReference>
        <ReceivedQuantity unitCode="EA">20</ReceivedQuantity>
        <LineNumber>1</LineNumber>
        <UserArea>
          <Property>
            <NameValue name="UDFCHAR02">S00603851</NameValue>
          </Property>
          <Property>
            <NameValue name="UDFCHAR03">1</NameValue>
          </Property>
        </UserArea>
      </ReceiveDeliveryItem>'

    set @myDoc.modify('
    replace value of (/ReceiveDeliveryItem/ReceivedQuantity/text())[1]
    with "15"')

CodePudding user response：

Please try the following solution.

It is checking for both conditions via XPath predicates:

[PurchaseOrderReference/DocumentID/ID[text()="14500000999"]]
[PurchaseOrderReference/LineNumber[text()="1"]]

SQL

DECLARE @myDoc XML = 
N'<ReceiveDeliveryItem>
    <Classification>
        <Codes>
            <Code listID="Classes" sequence="1">*</Code>
        </Codes>
    </Classification>
    <ServiceIndicator/>
    <PurchaseOrderReference>
        <DocumentID>
            <ID accountingEntity="9991" lid="lid://infor.eam.aim_prd" location="9991">14500000999</ID>
        </DocumentID>
        <LineNumber>1</LineNumber>
    </PurchaseOrderReference>
    <ReceivedQuantity unitCode="EA">20</ReceivedQuantity>
    <LineNumber>1</LineNumber>
    <UserArea>
        <Property>
            <NameValue name="UDFCHAR02">S00603851</NameValue>
        </Property>
        <Property>
            <NameValue name="UDFCHAR03">1</NameValue>
        </Property>
    </UserArea>
</ReceiveDeliveryItem>';

set @myDoc.modify('replace value of 
    (/ReceiveDeliveryItem[PurchaseOrderReference/DocumentID/ID[text()="14500000999"]]
        [PurchaseOrderReference/LineNumber[text()="1"]]/ReceivedQuantity/text())[1]
with "15"');

-- test
SELECT @myDoc;