CODEWITHSUNDEEP
sql-serverxmlsql-server-2008t-sql
Simon
Simon

Reputation: 5493

Replacing XML elements in SQL server with descending value

Given an XML variable with these contents:

<Root>
    <Parent>
        <ItemID>28</ItemID>
        <Child>
            <ItemID>28</ItemID>
        </Child>
        <Child>
            <ItemID>28</ItemID>
        </Child>
    </Parent>
    <Parent>
        <ItemID>38</ItemID>
        <Child>
            <ItemID>38</ItemID>
        </Child>
    </Parent>
</Root>

I need to replace each /Root/Parent/ItemID value with a descending number starting from -1, and then replace each Child/ItemID with the value from it's parent ItemID element. For example, the XML above should be transformed into this: 

<Root>
    <Parent>
        <ItemID>-1</ItemID>
        <Child>
            <ItemID>-1</ItemID>
        </Child>
        <Child>
            <ItemID>-1</ItemID>
        </Child>
    </Parent>
    <Parent>
        <ItemID>-2</ItemID>
        <Child>
            <ItemID>-2</ItemID>
        </Child>
    </Parent>
</Root>

Easy to do in c#, I'm stumped on getting the syntax right in SQL Server. Unfortunately I have to do it this way to work with a legacy system.

Upvotes: 3

Views: 192

Answers (2)

Simon
Simon

Reputation: 5493

Mikael's solution was elegant but in reality my XML was a bit more complicated than in the question (many more elements, some attributes, etc), so recreating the XML became more complicated and a bit of a maintenance issue. So in the end I solved the problem with some good old procedural code:

declare @XML xml = '<Root>
    <Parent>
        <ItemID>28</ItemID>
        <Child>
            <ItemID>28</ItemID>
        </Child>
        <Child>
            <ItemID>28</ItemID>
        </Child>
    </Parent>
    <Parent>
        <ItemID>18</ItemID>
        <Child>
            <ItemID>18</ItemID>
        </Child>
    </Parent>
</Root>'

DECLARE @CountOfParent INT = @XML.value('count(/Root/Parent)', 'int') 
WHILE @CountOfParent > 0
BEGIN
    PRINT @CountOfParent
    --Replace the current Parent/ItemID element with -@CountOfParent
    SET @XML.modify('replace value of (/Root/Parent/ItemID/text())[sql:variable("@CountOfParent")][1] with -sql:variable("@CountOfParent")')

    --Now loop through all of THIS elements <Child> elements, also setting the ItemID to -@CountOfParent
    DECLARE @CountOfChildren INT = @XML.value('count(/Root/Parent[sql:variable("@CountOfParent")]/Child)', 'int') 
    WHILE @CountOfChildren > 0
    BEGIN
        set @XML.modify('replace value of (/Root/Parent[sql:variable("@CountOfParent")]/Child[sql:variable("@CountOfChildren")]/ItemID/text())[1] with -sql:variable("@CountOfParent")')
        set @CountOfChildren = @CountOfChildren - 1
    END
    SET @CountOfParent = @CountOfParent - 1
END

SELECT @XML

However, as Mikael answered the question, I have accepted his answer and am just including this for reference.

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Your XML is not valid so I changed it to what I think you meant.

You can shred the XML, use row_number() to calculate the new ItemID and rebuild it using for xml path.

declare @XML xml = '
<Root>
    <Parent>
        <ItemID>28</ItemID>
        <Child>
            <ItemID>28</ItemID>
        </Child>
        <Child>
            <ItemID>28</ItemID>
        </Child>
    </Parent>
    <Parent>
        <ItemID>38</ItemID>
        <Child>
            <ItemID>38</ItemID>
        </Child>
    </Parent>
</Root>'

select P.ItemID,
       (
       select P.ItemID
       from P.Child.nodes('Child') as C(N)
       for xml path('Child'), type
       )
from
  (
  select -row_number() over(order by P.N) as ItemID,
         P.N.query('Child') as Child
  from @XML.nodes('/Root/Parent') as P(N)
  ) as P
for xml path('Parent'), root('Root'), type

Upvotes: 3

Related Questions