Modify or Replace XML data using XSLT

There are many ways we can replace a block of xml node using various scripting languages. In a recent project I have used XSLT to replace the xml node. I thought that I should share that knowledge with you guys. In this example I have a XML file for company department and I want to replace an employee’s record in the xml file with a new one.
Companydept.xml

Code:

<?xml version=”1.0″?>
<Department>
    <ID>D7890000</ID>
    <DeptName>IT</DeptName>      
<Employees>
     <Employee>
         <EID>0001</EID>
   <Name>Ann James</Name>
            <Age>35</Age>  
  </Employee>
  <Employee>
         <EID>0002</EID>
   <Name>Batty Gomas</Name>
            <Age>42</Age>  
  </Employee>
  <Employee>
         <EID>0003</EID>
   <Name>Boss</Name>
            <Age>28</Age>
  </Employee>
</Employees>
</Department>

Employee ID 0001 should be replaced with a new record.
Following XSLT will replace this record.
Dept.xsl

Code:

<?xml version=”1.0″?>
<xsl:stylesheet version=”1.0″ xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”>
<xsl:output method=”xml” indent=”yes”/>
<xsl:template match=”node()|@*”>
    <xsl:choose>
    <xsl:when test=”local-name()=’Employee’”>
        <xsl:call-template name=”Employee”/>
    </xsl:when>
    <xsl:otherwise>
        <xsl:copy>
  <xsl:apply-templates select=”@*”/>
  <xsl:apply-templates/>
  </xsl:copy>
    </xsl:otherwise>
    </xsl:choose>
</xsl:template>
<xsl:template name=”Employee”>      
     <xsl:choose>
     <xsl:when test=”EID=’0001′”>
         <xsl:element name=”Employee”>
         <xsl:element name=”EID”><xsl:value-of select=”EID” /></xsl:element>
      <xsl:element name=”Name”>Agustine</xsl:element>
      <xsl:element name=”Age”>35</xsl:element>
     </xsl:element>
     </xsl:when>
     <xsl:otherwise>
         <xsl:copy-of select=”.” />
     </xsl:otherwise>        
     </xsl:choose>
</xsl:template>
</xsl:stylesheet>

For testing this XSLT in the xml add following line to XML file.

Code:

<?xml-stylesheet href=”Dept.xsl” type=”text/xsl”?>

The output XML should be like this:

Code:

<?xml version=”1.0″?>
<Department>
  <ID>001</ID>
  <DeptName>IT</DeptName>
  <Employees>
    <Employee>
      <EID>001</EID>
      <Name>Agustine</Name>
      <Age>35</Age>
    </Employee>
    <Employee>
      <EID>0002</EID>
      <Name>Batty Gomas</Name>
      <Age>42</Age>
    </Employee>
    <Employee>
      <EID>0003</EID>
      <Name>Boss</Name>
      <Age>28</Age>
    </Employee>
  </Employees>
</Department>

XSL reads all the nodes and check for the particular node to replace it with new nodes.
Please post any comments about this example.
Thanks

Formatting a date to dd/mm/yyyy

Following code will format sql server date in dd/mm/yyyy format.

Code:

select convert(varchar,getdate(),103)
– should return dd/mm/yyyy

convert the date as string based on the 3rd argument in the convert() function.

IDENTITY reset in SQL Server

We will have a look at IDENTITY value reset and explicit insert in database tables.
In certain scenarios you may need to start your IDENTITY column value from a new number. CHECKIDENT command can be used to force the table to restart the auto increment number.
Syntax,

Code:

DBCC CHECKIDENT (<TableName>, RESEED, <yourReseedValue>)

For example, to start employee id from 5000, use the following command.

Code:

DBCC CHECKIDENT (“DataBaseName.Employee”, RESEED, 5000);
GO

Insert on IDENTITY Column
In some cases you may need to insert some values to Identity column. Table will throw a warning message if tried to insert a value to auto increment value column. To insert the value in auto number value column, IDENTITY_INSERT should be set to ON and after the insert, it can set it back to OFF.
Syntax,

Code:

SET IDENTITY_INSERT <TableName> ON
– Insert statement
SET IDENTITY_INSERT <TableName> OFF

Example,

Code:

SET IDENTITY_INSERT Employee ON
INSERT INTO Empoyee (ID,NAME) VALUES ( 5003 , ‘Sam’ );
SET IDENTITY_INSERT Employee OFF

I hope this topic will help someone out there. Please post your comments here if you would like to discuss about this topic.