CODEWITHSUNDEEP
sql-serverxmlt-sqlxquery-sql
Mark O Keeffe
Mark O Keeffe

Reputation: 301

Update xmlstate in tsql column based on other attributes

Struggling abit with an update to a column storing xml data in tsql. Basically I want to update the rows where a certain flag is missing. I have an advantage in that I know that if the attribute part_id=1 is present then the attribute i want to add is missing

This is an example of my output xml currently. As you can see the groupname is missing, where part is equal to 1. This is the structure in one particular row, there will be other rows to update but if I could get this example working that be a start.

<report>
  <materials>
    <material part_vendor_name="tests" vendor_pricing_id="wwwewew" part="1"   />
    <material part_vendor_name="tests" vendor_pricing_id="wwwewew" part="2"                     groupname="test" />
  </materials>
</report>

The code to create the above structure is 

 declare @my_table table ( my_xml_column xml)
insert into @my_table
    select  '<report><materials><material part_vendor_name="tests" vendor_pricing_id="wwwewew" part="1"> </material>
            <material part_vendor_name="tests" vendor_pricing_id="wwwewew" part="2" groupname="test"> </material>
    </materials></report>'

I can add new elements under the report or materials flag, just can't work out on to only update the rows containing part =1

Ps. My latest attempt was the following, but i've tried many approaches

UPDATE 
 @my_table
  SET @my_table.modify('insert <groupname> test </groupname> after               (/report/materials/material)[1]') 
    WHERE    @my_table.value('(/report/materials/material/part[text() = "1"])[1]', 'varchar(64)') = '1'

thanks mark

Upvotes: 0

Views: 34

Answers (1)

har07
har07

Reputation: 89285

Assuming that you want to insert attribute * groupname instead of element of the same name :

UPDATE @my_table
SET my_xml_column.modify('
insert attribute groupname {"test"} into(/report/materials/material[@part = "1"])[1]') 
WHERE my_xml_column.exist('/report/materials/material[@part = "1"]') = 1

Or if you really want to insert a <groupname> element :

UPDATE @my_table
SET my_xml_column.modify('
insert <groupname> test </groupname> after(/report/materials/material[@part = "1"])[1]') 
WHERE my_xml_column.exist('/report/materials/material[@part = "1"]') = 1

*) Note that attribute order in SQL Server is not preserved, and the order shouldn't change meaning of the element : Sort XML Attributes in SQL

Upvotes: 1

Related Questions