Update XML Node

Posted: September 9, 2010 in SQL Server 2005 - XML

If you need to change the value of an XML node, you can try doing the following:

-- Create a schema for your XML data
CREATE XML SCHEMA COLLECTION my_xml_schema
AS
'<xsd:schema elementFormDefault="unqualified" attributeFormDefault="unqualified" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
    <xsd:element name="Transaction">
       <xsd:complexType>
          <xsd:sequence>
             <xsd:element name="Type" type="xsd:string"/>
             <xsd:element name="Code" type="xsd:string"/>
          </xsd:sequence>                      
       </xsd:complexType>
    </xsd:element>
 </xsd:schema>'
GO

-- Declare variables
DECLARE @lv_type   VARCHAR(10);
DECLARE @lxml_data XML(my_xml_schema); -- Declare this using the schema created above

-- Set change value
SET @lv_type = 'Change';

-- Set the XML data
SET @lxml_data = '<Transaction>
                     <Type>Node Type</Type>
                     <Code>Node Code</Code>
                  </Transaction>';

-- Check XML data
SELECT @lxml_data;

-- Modify the XML data
SET @lxml_data.modify('replace value of (/Transaction [1]/Type [1]) with sql:variable("@lv_type")');

-- Check XML data
SELECT @lxml_data;

-- Drop XML schema
DROP XML SCHEMA COLLECTION my_xml_schema
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s