How to use SQL variable to iterate XML nodes

Question

I have this XML in SQL Server 2008:

DECLARE @xml xml = '<Root>
                      <Contacts>
                        <Contact name="John Doe" type="REG" other="value" />
                        <Contact name="Jane Doe" type="REG" other="value" />
                        <Contact name="Jennifer Doe" type="REG" other="value" />
                        <Contact name="Jane Doe" type="REG" other="value" />
                      </Contacts>
                    </Root>'

I want to change the value of the types to something else. I can hard code which one(s) I want to change with no problem.

SET @xml.modify('replace value of (/Root/Contacts/Contact)[1]/@type with "NEW"')
SELECT @xml

That "does the right thing," regardless of what value I put in the brackets. IOW, the above changes the first one, changing it to [3] changes the third one, etc.

I want to change all of them, regardless of how many or how few there are. So, I need to get the count, then iterate over them using a variable. Getting the count is easy, but I can't get the variable to work.

IOW, this XML, which just replaces the [1] above with the variable, doesn't work.

DECLARE @i int = 1
SET @xml.modify('replace value of (/Root/Contacts/Contact)[sql:variable("@i")]/@type with "NEW"')
SELECT @xml

It gives me this error, which is telling me I did something wrong, but I can't grok the error to figure out what I did wrong:

Msg 2337, Level 16, State 1, Line 14
XQuery [modify()]: The target of 'replace' must be at most one node, found 'attribute(type,xdt:untypedAtomic) *'

As I was about to hit "Post" on this, I found reference to something that said you had to use position() in order to use a variable on the node index, but the example was a query, not a modify. And that doesn't work, either.

DECLARE @i int = 1
SET @xml.modify('replace value of (/Root/Contacts/Contact)[position()=sql:variable("@i")]/@type with "NEW"')
SELECT @xml

I have no doubt this is something easy, but I've searched here and elsewhere for how to use sql:variable, and almost all of the examples I've found are using it as the value in a replace, not the index (none of the "Questions which may already have your answer" helped, either.) I've also experimented with extra parentheses in various places, etc., and just haven't been able to hit on the magic combination.

What am I missing to make the above SET work with @i?

Gottfried Lesigang · Accepted Answer · 2016-09-26 19:24:49Z

5

Your own code would work with just one more [1]. The function .modify() cannot interpret the [sql:variable(...)]... This might be any filter, even one with more than one result... So just change this to:

DECLARE @xml xml = '<Root>
                      <Contacts>
                        <Contact name="John Doe" type="REG" other="value" />
                        <Contact name="Jane Doe" type="REG" other="value" />
                        <Contact name="Jennifer Doe" type="REG" other="value" />
                        <Contact name="Jane Doe" type="REG" other="value" />
                      </Contacts>
                    </Root>';
DECLARE @i int = 1
SET @xml.modify('replace value of (/Root/Contacts/Contact)[sql:variable("@i")][1]/@type with "NEW"')
SELECT @xml

But I would go another path... You might read the whole lot as derived table and rebuild the XML like this:

SELECT
(
    SELECT c.value('@name','nvarchar(max)') AS [@name]
          --,c.value('@type','nvarchar(max)') AS [@type]
          ,'NEW' AS [@type]
          ,c.value('@other','nvarchar(max)') AS [@other]
    FROM @xml.nodes('/Root/Contacts/Contact') AS A(c)
    FOR XML PATH('Contact'),ROOT('Contact'),TYPE
)
FOR XML PATH('Root')

Another approach would be FLWOR:

SELECT @xml.query('
for $r in /Root/Contacts
    return <Root><Contacts>
    {
        for $c in /Root/Contacts/Contact
           return <Contact name="{$c/@name}" type="NEW" other="{$c/@other}"/>
    }
    </Contacts></Root>
')

edited Sep 26, 2016 at 19:24

answered Sep 26, 2016 at 19:15

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

2 Comments

vr8ce Over a year ago

Well, that works, but I'll be durned if I understand why it works. :) The variable is acting as the index (it's in brackets). Why do I need another index? (Mostly rhetorical; it works, so I'll use it, I just won't understand it.) The derived table would get messy; the actual XML is a lot more complicated, with a lot more parent/child nodes like Contacts. I just need one attribute of one set of nodes in the multi-parent node XML changed, so I think iterating over just those nodes is easiest. Thanks!

Gottfried Lesigang Over a year ago

@vr8ce, It's not so difficult: The square brackets allow you to place an XQuery-filter. You might, e.g., filter for all Contact-elements like /Root/Contacts/Contact[@name="Jane Doe"]. This would - in your example - return two rows. But the literal number is one clear position. With .modifiy() the engine must be sure, that this will affect one single node only. In other words: Find all elements at the position of the variable and then take the first of this set (funny for us, but not for the engine...)

Collectives™ on Stack Overflow

How to use SQL variable to iterate XML nodes

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related