XML question in SQL Server

Question

In one of my sql scripts, I need to execute a stored procedure with the following xml string

<Collection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
    <Field>
     <Attributes>
      <Attribute Name="CODE1" IsRequired="true" Order="1" IsVisible="true"/>
      <Attribute Name="CODE2" IsRequired="true" Order="2" IsVisible="true"/>
     </Attributes>
     <Rows>
      <Row ProductState="5">
       <Items>
        <Item Name="PROD1" SendCustomer="false"/>
        <Item Name="PROD2" SendCustomer="false"/>
       </Items>
      </Row>
     </Rows>
    </Field>
</Collection>

I get the Attribute and the Item information from different tables. I am writing a generic function in which you pass an ID and returns this XML string that is used by the SQL script to execute the stored procedure

Sometimes, I need to override the attribute values of some elements like SendCustomer. My initial thought was to deserialize this to a temp table, update the temp table with the override value and then serialize it back to XML.

So, essentially, the entire process boils down to:

1. Query tables, serialize to XML in the function
2. Deserialze XML, store in temp table
3. Override values if necessary
4. Serialze from table to XML again

Is there a more elegant way in sql server 2005 to do this entire process?

Answer 1

The XML datatype actually can be modified using XQuery. See the modify() method.

declare @x XML;
select @x = N'<Collection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
    <Field>
        <Attributes>
                <Attribute Name="CODE1" IsRequired="true" Order="1" IsVisible="true"/>
                <Attribute Name="CODE2" IsRequired="true" Order="2" IsVisible="true"/>
        </Attributes>
        <Rows>
                <Row ProductState="5">
                        <Items>
                                <Item Name="PROD1" SendCustomer="false"/>
                                <Item Name="PROD2" SendCustomer="false"/>
                        </Items>
                </Row>
        </Rows>
    </Field>
</Collection>';

set @x.modify(N'replace value of 
    (/Collection/Field/Rows/Row/Items/Item[@Name="PROD2"]/@SendCustomer)[1]
    with "true"');

select @x;