-- Create Table
CREATE TABLE Parts
(
	[id] [int] IDENTITY(1,1) NOT NULL Primary key clustered,
	[Data] [xml] NOT NULL
)

-- Insert data
  insert Parts( data )
  values
  (
  N'<Part PartId="85" Description="Fuse 10A" />'
  )

-- Update data
  update Parts
  set data.modify
  ('
    replace value of (/Part[1]/@Description) with "Fuse 15 A"
  ')
  from
  Parts
  where data.exist( '/Part/@PartId[.="85"]') = 1

-- Review data
select * from Parts
-- Review data in a different way
select data.query('<Description Value="{ /Part/@Description }" />') from Parts
where Data.exist( '/Part/@PartId[.="85"]' ) = 1
-- If you modify the script and it doesn't work, first thing to check is the casing of the identifiers: XPath and XML are case-sensitive!
-- Retrieve data for relational use
select data.value('/Part[1]/@Description', 'varchar(100)' ) from Parts
where Data.exist( '/Part/@PartId[.="85"]' ) = 1
Advertisements