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">
    <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:

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

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

Remus Rusanu · Accepted Answer · 2009-06-01 22:29:18Z

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">
    <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;

edited Jun 1, 2009 at 22:29

answered Jun 1, 2009 at 22:21

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bob Smith Over a year ago

Can you explain how the JQuery solution would fit into the question i had? Thanks

Remus Rusanu Over a year ago

What is JQuery? My answer is Transact-SQL.

Collectives™ on Stack Overflow

XML question in SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related