Remove Special Character from XML string using SQL Server

Create a function in MSSQL to remove all special characters from XML.
ALTER FUNCTION [dbo].[RemoveSpChar]
(
– Add the parameters for the function here
@sInput varchar(MAX)=”
)
RETURNS varchar(MAX)
AS
BEGIN
– Declare the return variable here
DECLARE @sOutput Varchar(MAX),
@iIndex int,
@iLength int,
@sChar varchar(1),
@iASCII int,
@iLen int,
@iRem int
set @sInput= ltrim(rtrim(@sInput))
set @iLength = len(@sInput)
set @iIndex =1
set @sOutput=”
while @iIndex <= @iLength
begin
set @sChar=substring(@sInput,@iIndex,1)
set @iASCII=ascii(@sChar)
if ((@iASCII>=48 and @iASCII<=57) or (@iASCII>=65 [...]

- Fetch XML (xmldatadocument) from database

This post shows you how to return XmlDataDocument from the database.
This helps you to fetch the xml data created in sql and use in asp.net.
If you are familiar how to get xml data from sql then this post helps you to fetch the xml data in XmlDataDocument.
//Code Starts Here
// http://helpindotnet.blogspot.com/
// http://helpindotnet.wordpress.com
public XmlDataDocument getauditnames(string prefixText, int [...]

– Encode Decode XML Name

<span style=”font-weight:bold;”>While coding with XML Files, Sometimes you need to Encode and Decode the Xml Name.
Here is the simplest method to encode the name XmlConvert.EncodeName(string).
</span>
using System.Xml;
namespace ConsoleApplication1
{
class classEncodeDecodeXMLName
{
static void encodedecode()
{
// Encode and decode a name with spaces.
Console.WriteLine(XmlConvert.ToByte(“Some Name”));
string name1 = XmlConvert.EncodeName(“Order Detail”);
Console.WriteLine(“Encoded name: ” + name1);
Console.WriteLine(“Decoded name: ” + XmlConvert.DecodeName(name1));
// Encode and decode a local [...]

– Unicode Characters

ASCII does’nt recognizes the unicode character. Unicode code character are different from special character. For Ex: scientific characters like pi, pmu,…
Unicode Character is :  Pi : Π
Try out with different unicode characters
//Code starts here
static void convertunicode()
{
string unicodeString = “This string contains the unicode character Pi(\u03a0)”;
// Create two different encodings.
Encoding ascii = Encoding.ASCII;
Encoding unicode = Encoding.Unicode;
// [...]

- Validate XML against Schema

Here I will show you how to “Validate Xml against the provided Schema xsd”.
This is the simplest method to validate the xml from the given Schema xsd
using System.IO;
using System.Xml;
using System.Xml.Schema;
using System.Data;
using System.Web;
namespace ConsoleApplication1
{
class clsValidateXml
{
public static string ErrorMessage=””;
static void validatexml()
{
//Physical path of the XML File
string XmlFilePath = @”c:\Test.xml”;
//Physical path of the XSD File
string XSDFilePath = @”c:\Test.xsd”;
XmlDocument [...]