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>'
select 'USA','<states><state ID="1">Alaska</state><state ID="2">Alabama</state><state ID="3">Florida</state><state ID="4">Idaho</state></states>'
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)
print 'State Selected'
/*INSERT DATA::this will insert a state where id=1(India) with Name "Arunachal Pradesh"*/
set States.modify('insert<state ID="6">Arunachal Pradesh</state>into (/states)')
print 'State Inserted'
/*UPDATE DATA::this will update name of state with id=6 to Arunachal and country id=1*/
set States.modify('replace value of(/states/state[@ID=6]/text()) with "Aurnachal"')
print 'State Updated'
/*DELETE DATA::this will delete where Id=5 (UP) and countryid=1 from sql-xml column*/
set States.modify('delete (/states/state[@ID=5])')
print 'state Deleted'
hope this will help you.