0

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

Mark O Keeffe
  • 191
  • 2
  • 15

1 Answers1

1

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

Community
  • 1
  • 1
har07
  • 88,338
  • 12
  • 84
  • 137
  • thank you har07 for your time, it was the first version that i was looking for. I was close on numerous times but probably have spent days trying to get it right. – Mark O Keeffe Dec 22 '15 at 13:31
  • One more question, I want to change the exists to look for two conditions, so the where clause be the same as above but have a second part. This was my attempt but I seem to be missing something-------------- WHERE my_xml_column.exist('/report/materials/material[@part = ''1''] and /report/materials/material[@groupname=''test''] ') = 1 – Mark O Keeffe Dec 23 '15 at 11:25
  • This is the correct XPath to select `material` elements that meet the 2 conditions : `/report/materials/material[@part = "1" and @groupname="test"]` – har07 Dec 23 '15 at 11:56
  • So the where clause would be : `WHERE my_xml_column.exist('/report/materials/material[@part = "1" and @groupname="test"] ') = 1` – har07 Dec 23 '15 at 11:56
  • thanks Har07.. that solves this for me.. I must have tried every combination except this.. actually if i wanted to check the and condition for where groupname does not exist..one i do this ------------ and not(@groupname) --- seems to work but that might not be the right approach – Mark O Keeffe Dec 23 '15 at 12:15
  • No, that's a fine approach actually. – har07 Dec 23 '15 at 12:17