SQL Server XQuery XML indexing

Question

Using SQL Server 2008-

I have XML data stored in a column of my table which is the result of exporting some drawing info:

<layout>
    <config>
        <graphic_type>Box</graphic_type>
        <data_access>
        </data_access>
        <data>
            <dimension x="1" y="2" z="3" />
            <curve_info ir="-1.5" or="1.5" degree="0"/>
            <position x="4" y="5" z="6" />
            <rotation x="7" y="8" z="9" />
            <color>FFD3D3D3</color>
            <is_position_relative>false</is_position_relative>
        </data>
    </config>
    <config>
        ...
    </config>
</layout>

Where the number of to draw individual pieces is unknown. Currently if I wanted to do something like move the entire drawing 100 units along the X-axis, I have SQL code like:

SET @xTrans = 100
UPDATE TableName
SET xmlColumn.modify('replace value of (//data/position/@x)[1] with sql:variable("@xTrans")')
SET xmlColumn.modify('replace value of (//data/position/@x)[2] with sql:variable("@xTrans")')
SET xmlColumn.modify('replace value of (//data/position/@x)[3] with sql:variable("@xTrans")')
...
SET xmlColumn.modify('replace value of (//data/position/@x)[20] with sql:variable("@xTrans")')

And I essentially do that an arbitrary number of times because I don't know how many nodes actually exist in each drawing. I am fairly new to SQL, and even more so to XQuery, but is there a better way to go about this problem?

To be more extensible, the next problem I have is when Devices are drawn on top of this model, they were originally drawn in 2d before being exported to xml files, and so they take on the height value (happens to be the Y-axis in my case) of the first section of the drawing, when the devices X and Z coordinates potentially place it at the end of the entire drawing. This causes some devices to be floating either above or below the models. The only thing I could think to write for this problem is something like:

-- Determine if moving along X or Z axis by Y rotation
-- If its the Z-axis, find the range that section covers with position+dimension
    -- @range = (///position/@z)[1] + (///dimension/@z)[1]
-- See if the device falls in that range
    -- If (///position/@z)[1] < device position @z < @range
-- Then we need the rotation of that box in the Z-axis
-- to calculate the height change for the device

But this would involve having to copy and paste that code ~15 times (I'm not sure what the largest number of components a model could have, I have seen 6 on the current project) and changing the index [1] which seems extremely inefficient.

The device XML layout is exactly the same as the model, just with a different value for .

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

It's not possible to replace multiple values at once in xml in SQL Server, there're a several options. In your case I'd suggest to use simple loop:

select @i = max(t.xmlColumn.value('count(//data/position[@x != 100])', 'int'))
from TableName as t

while @i > 0
begin
    update TableName set
        xmlColumn.modify('
            replace value of (//data/position[@x != 100])[1]/@x
            with sql:variable("@xTrans")'
        )

    set @i = @i - 1
end

SQL Server XQuery XML indexing

1 Answers1