Updating a single node in XML from SQL

Question

Post reply

Updating a single node in XML from SQL

lee.hopkins

Old Hand

Points: 366
More actions
December 29, 2022 at 3:51 pm

#4131544

I have a xml parameter, i need to update the node <ReceiveDeliveryItem> where the node <PurchaseOrderReference>/<DocumentID> = "15500000174" and the node <LineNumber> = 1
update node <ReceivedQuantity unitCode="EA"> = 40

declare @myxml as xml
set @myxml = '<?xml version="1.0" encoding="UTF-8"?>
<SyncReceiveDelivery xmlns="http://schema.infor.com/InforOAGIS/2" releaseID="9.2">
<ApplicationArea>
<Sender>
<LogicalID>azure_prd</LogicalID>
<ComponentID>External</ComponentID>
<ConfirmationCode>OnError</ConfirmationCode>
</Sender>
<CreationDateTime>2022-12-20T01:28:39.890</CreationDateTime>
<BODID>azure_application_to_ReceiveDelivery:S00603851:2022-12-20T01:28:39.890</BODID>
</ApplicationArea>
<DataArea>
<Sync>
<TenantID>PRD</TenantID>
<AccountingEntityID>9991</AccountingEntityID>
<Location>9991</Location>
<ActionCriteria>
<ActionExpression actionCode="Add"/>
</ActionCriteria>
</Sync>
<ReceiveDelivery>
<ReceiveDeliveryHeader>
<DocumentID>
<ID>S00603851</ID>
</DocumentID>
<DocumentDateTime>2022-12-12T16:52:04.3770000</DocumentDateTime>
<Description>Manifest - S00603851</Description>
<Status>
Pending
<Description>Pending</Description>
<EffectiveDateTime>2022-12-20T07:28:39.9489681Z</EffectiveDateTime>
</Status>
<PackingSlip>S00603851</PackingSlip>
</ReceiveDeliveryHeader>
<ReceiveDeliveryItem>
<Classification>
<Codes>
<Code listID="Classes" sequence="1">*
</Codes>
</Classification>
<ServiceIndicator/>
<PurchaseOrderReference>
<DocumentID>
<ID accountingEntity="9991" lid="lid://infor.eam.aim_prd" location="9991">15500000174</ID>
</DocumentID>
<LineNumber>1</LineNumber>
</PurchaseOrderReference>
<ReceivedQuantity unitCode="EA">16</ReceivedQuantity>
<LineNumber>1</LineNumber>
<UserArea>
<Property>
<NameValue name="UDFCHAR02">S00603851</NameValue>
</Property>
<Property>
<NameValue name="UDFCHAR03">1</NameValue>
</Property>
</UserArea>
</ReceiveDeliveryItem>
<ReceiveDeliveryItem>
<Classification>
<Codes>
<Code listID="Classes" sequence="1">*
</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>
</ReceiveDelivery>
</DataArea>
</SyncReceiveDelivery>'

January 5, 2023 at 11:25 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

Here is an example and a link to a thread that should help you get passed this hurdle.

😎

USE TEEST;
GO
--https://www.sqlservercentral.com/Forums/2003814/how-to-update-xml-data-in-sql-server
/* Create a table (variable) */
DECLARE @XMLUPDATE TABLE
(
    XU_ID   INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
   ,XU_VAL  INT NOT NULL
   ,XU_XML  XML NOT NULL
);
 
/* Populate with few samples */
INSERT INTO @XMLUPDATE
    (
        XU_VAL
       ,XU_XML
    )
SELECT
    S.object_id + 10 AS XU_VAL
   ,(SELECT
        SO.name           AS 'MyNode/@name'
       ,SO.type_desc      AS 'MyNode/@type_desc'
       ,SO.create_date    AS 'MyNode/@create_date'
       ,SO.object_id      AS 'MyNode/@object_id'
       ,SO.object_id      AS 'MyNode'
    FROM sys.objects SO  
    WHERE S.object_id = SO.object_id 
    FOR XML PATH('MyParentNode'),TYPE) AS XU_XML
FROM sys.objects S;
/* filter if needed */  
    --WHERE YEAR(S.create_date) = 2009;

/* run the update */
UPDATE MU
SET XU_XML.modify('replace value of (/MyParentNode/MyNode/text())[1] with sql:column("MU.XU_VAL")')
FROM @XMLUPDATE MU;
/* filter if needed */  
    --WHERE MU.XU_ID < 40  
   
/* The node value is now equal to 10 + object_id attribute  */
SELECT
   MU.XU_ID
  ,MU.XU_VAL
  ,MU.XU_XML
FROM @XMLUPDATE MU;