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?

Community · Accepted Answer · 2017-05-23 10:26:15Z

0

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

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Jul 8, 2014 at 1:39

Tom Hunter

5,96011 gold badges54 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

fbc Over a year ago

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.

Collectives™ on Stack Overflow

XQuery / SQL Server iterative replace statement

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related