USP_UPDATEXMLPROPERTYFORSITECOPY

Updates a property in the XML Data for all parts referenced in the specified XML input.

Parameters

Parameter	Parameter Type	Mode	Description
@Property	nvarchar(400)	IN
@Data	xml	IN

Definition

Copy



CREATE procedure dbo.USP_UPDATEXMLPROPERTYFORSITECOPY
(
@Property nvarchar(400),
@Data xml
)
as

    declare @XMLData table ([SiteContentID] int,[data] xml, [Value] nvarchar(max))
    insert into @XMLData
    select Node.query('SiteContentID/text()').value('.', 'int'), null, Node.query('Value/text()').value('.', 'nvarchar(max)') from @Data.nodes('row') as t(Node)

    update X
    set X.data = SC.XMLData
    from @XMLData X
    inner join SiteContent SC on SC.ID = X.SiteContentID

    --Stick the new IDs back in the XML

    update @XMLData
    set data.modify('replace value of (XMLHashTable[1]/ItemArray[1]/Item[@Name=sql:variable("@Property")][1]/Value[1]/text()[1]) with sql:column("Value")')

    --Update the XMLData column to use the new XML string

    update SC
    set SC.XMLData = '<?xml version="1.0" encoding="utf-16"?>' + CAST(x.data as nvarchar(max))
    from  dbo.SiteContent SC
    inner join @XMLData X on X.SiteContentID = SC.ID