Friday, December 18, 2009

Insert/Update/Delete data in sql-xml column

How to manipulate data in Untyped(sql-xml) column.

With advent of xml datatype in sql server, developer get a power of xml in sql server. with the help of "XML DML" we can insert,update node in sql-xml column.
this sql-xml column also known as Untyped column.

we can reduce number of records in table by using this xml column wisely.
for example let's say we have a "country" table which stores name and ID of country in SQL table.now if we want to add state for all country, we have a separate table "state" and for each state there will be separate entry in table along with same country id.

we can handle this situation by storing all state of that country in "country" table as xml nodes.

At many points you will find this sql-xml data type helpful and powerful.this datatype can be queried with almost same concept as xml query except some syntax variation.

declare @Country as table(ID int IDENTITY(1,1) PRIMARY KEY,Name varchar(20),States xml)
INSERT INTO @Country
select 'India','<states><state ID="1">Gujarat</state><state ID="2">Rajasthan</state><state ID="3">AP</state><state ID="4">MP</state><state ID="5">UP</state></states>'
union all
select 'USA','<states><state ID="1">Alaska</state><state ID="2">Alabama</state><state ID="3">Florida</state><state ID="4">Idaho</state></states>'
union all
select 'Uk','<states><state ID="1">Uk1</state><state ID="2">Uk-2</state></states>'


/*SELECT DATA:: this will select all state from table where ID=1(India)*/

declare @States xml
set @States=(select states from @Country where id=1)
select tab.col.value('@ID','int') as ID,tab.col.value('.','varchar(20)') as Name from @States.nodes('states/state') tab(col)
--where tab.col.value('@ID','int')=1
print 'State Selected'

/*INSERT DATA::this will insert a state where id=1(India) with Name "Arunachal Pradesh"*/

Update @Country
set States.modify('insert<state ID="6">Arunachal Pradesh</state>into (/states)[1]')
where ID=1
print 'State Inserted'

/*UPDATE DATA::this will update name of state with id=6 to Arunachal and country id=1*/

Update @Country
set States.modify('replace value of(/states/state[@ID=6]/text())[1] with "Aurnachal"')
where ID=1
print 'State Updated'

/*DELETE DATA::this will delete where Id=5 (UP) and countryid=1 from sql-xml column*/

Update @Country
set States.modify('delete (/states/state[@ID=5])')
where ID=1

print 'state Deleted'

/*
http://www.simple-talk.com/sql/t-sql-programming/xml-data-modification-language-workbench/
*/

hope this will help you.

thanks.