XQuery / SQL Server iterative replace statement

Question

My question is how to put an iterative loop into a SQL Server 8 SQL procedure that uses XQuery to find and change node values.

I have a SQL Server Database table with a field called Data containing XML data, something like the following:-

<ContentTree xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://mynamespace">
    <ContentObject>
        <Id>164</Id>
        <Order>A</Order>
        <Catalogue>1</Catalogue>
    </ContentObject>
    <ContentObject>
        <Id>165</Id>
        <Order>A</Order>
        <Catalogue>1</Catalogue>
    </ContentObject>
    <ContentObject>
        <Id>166</Id>
        <Order>B</Order>
        <Catalogue>2</Catalogue>
    </ContentObject>
    <ContentObject>
        <Id>166</Id>
        <Order>B</Order>
        <Catalogue>2</Catalogue>
    </ContentObject>
</ContentTree>

I would like to be able to iterate through all the ContentObjects and where the Order value is A, change the Catalogue value to 3.

I can change the value of one instance at a time with the following:-

UPDATE [Database Table]
SET Data.modify('declare namespace c="http://mynamespace";
replace value of (//c:ContentObject[c:Order = "A"]/c:Catalogue/text())[1] with ("3")')
WHERE DatabaseRecordId = '5'

But I would like to be able to put this in a loop and have one procedure make all the changes - is this possible?

score 0 · Accepted Answer · edited May 23 '17 at 10:26

As per Mikael Eriksson's answer, you can loop over the nodes and call modify() for each one:

DECLARE @xml XML = 
    '<ContentTree xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://mynamespace">
        <ContentObject>
            <Id>164</Id>
            <Order>A</Order>
            <Catalogue>1</Catalogue>
        </ContentObject>
        <ContentObject>
            <Id>165</Id>
            <Order>A</Order>
            <Catalogue>1</Catalogue>
        </ContentObject>
        <ContentObject>
            <Id>166</Id>
            <Order>B</Order>
            <Catalogue>2</Catalogue>
        </ContentObject>
        <ContentObject>
            <Id>166</Id>
            <Order>B</Order>
            <Catalogue>2</Catalogue>
        </ContentObject>
    </ContentTree>'

The following code could be incorporated into a stored procedure fairly easily:

DECLARE @counter INT = (
    SELECT @xml.value('declare namespace c="http://mynamespace";
        count(//c:ContentObject[c:Order = "A"])', 'INT')
)

WHILE @counter > 0
BEGIN

    SET @xml.modify(
        'declare namespace c="http://mynamespace";
        replace value of (
            (//c:ContentObject[c:Order = "A"])[sql:variable("@counter")]
            /c:Catalogue/text())[1]
        with ("3")')

    SET @counter -= 1
END

SELECT @xml

Sorry, missed this answer at the time. I got my project done eventually by counting all orders, looping through and changing if = A, but this would be more efficient. — fbc, Aug 18 '14 at 13:49

XQuery / SQL Server iterative replace statement

1 Answers1