05 - XML and ADO

Working with XML Data

Organizations store data in different formats. This makes the process of data interchange complex and time consuming. To overcome this problem, organizations need a standard, simple way of storing data in a format that can be exchanged across multiple platforms. XML is a language that can display data in a standard and self describing format.

Data stored in XML format can be transferred to databases such as SQL Server with the help of ADO.NET. These objects help in exchanging data between the databases and XML documents, and enable to read and write the data stored in a dataset object as XML documents.

XML Data can be processed in .NET applications by using the System.Xml namespace, that contains many classes to write and read XML documents.

Writing XML Data

The XmlWriter class in the System.Xml namespace provides non-cached forward only and write only access to XML data. This class can be used to write either a stream of data or a text data. XmlWriter provides full support for XML namespace.Isolation levels

XmlWriter objects are created by using the Create () method. The Create () method is a static method. We can pass an object of XmlWriterSettings class to the Create () method in order to specify the settings or properties, which are to be enabled on the XmlWriter object. If no parameters are passed, the default settings are applied.

Properties of XmlWriterSettings class

XmlWriterSettings property

Description

CheckCharacters

Gets or Sets a value indicating whether to do character checking or not.

CloseOutput

Gets or Sets a value indicating whether the System.Xml.XmlWriter should close the underlying stream or System.IO.TextWriter when the System.Xml.XmlWriter.Close() method is called.

ConformanceLevel

Gets or Sets the level of compliance with which the System.Xml.XmlWriter complies with

Encoding

Gets or Sets the text encoding to use.

Indent

Gets or Sets a value indicating whether to indent elements or not.

IndentChars

Gets or Sets the character string to use while indenting

NewLineChars

Gets or Sets a character string for line breaks.

NewLineHandling

Gets or Sets a value indicating whether to normalize line breaks in the output or not.

NewLineAttributes

Gets or Sets a value indicating whether to write attributes on a new line or not.

OmitXmlDeclaration

Gets or Sets a value indicating whether to write an XML declaration or not.

 

Creating elements in an XML file

Elements can be created in two ways:

By calling the WriteElementString() Method:

WriteElementString() method takes two parameters, the name of the element and the value of the element. Like:

       writer.WriteElementString(“Student”,”Robin”);

Here student is the element name and Robin is the value

By calling the WriteStartElement() Method:

WriteStartElement() method takes the name of the element as a parameter. After calling it, WriteString() method is called to specify a value for this element. Then WriteEndElement() method is called to end the element tag. Like:

writer.WriteStartElement(“Student”);
writer.WriteString(“Robin”);
writer.WriteEndElement();

 

Creating Attributes in XML file:

Attributes can be created in two ways:

· By calling the WriteAttributeString() method:

This method takes two parameters the name of the attribute and value of attribute.

writer.WriteAttributeString("StudentID”,”S-109”);

In this code snippet StudentID is attribute and S-109 is value.

· By calling the WriteStartAttribute() method:

This method takes the name of attribute as a parameter. After calling it, WriteString() method is called to specify a value for this attribute, then WriteEndAttribute() method is called to end the attribute.Like:

write.WriteStartAttribute(“StudentID”);
writer.WriteString(“S-109”);
writer.WriteEndAttribute();writer.WriteStartElement(“Student”);
writer.WriteString(“Robin”);
writer.WriteEndElement();

 

Writing comments in XML file

Comments can be write using the WriteComment() method. This method takes a string as a parameter. This string is written as a comment in the XML file.

writer.WriteComment(“This XML file is created for students details”);

Creating an XML file

XML file can be created in following ways:

By using XmlWriter

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data.SqlClient;
namespace xmlWrite
{
    class Program
    {
    static void Main()
    {
    XmlWriterSettings settings=new XmlWriterSettings();
    settings=.Indent=true;
    settings.IndentChars=” ”;
    settings.NewLineOnAttributes=true;
    using(XmlWriter writer=XmlWriter.Create(“D:\\productdetails.xml”,settings));
    {
        writer.WriteComment(“Product details”);
        writer.WriteStartElement(“Product_details”);
        writer.WriteStartElement(“Product”);
        writer.WriteAttributeString(“Product_ID”,”P-0001”);
        writer.WriteElementString(“ProductName”,”Toys”);
        writer.WriteElementString(“Price”,”200”);
        writer.WriteEndElement();
        writer.WriteStartElement(“Product”);
        writer.WriteAttributeString(“Product_ID”,”P-0213”);
        writer.WriteElementString(“ProductName”,”Mouse”);
        writer.WriteElementString(“Price”,”500”);
        writer.WriteEndElement();
        writer.WriteEndElement();
        writer.Flush();
    }
    }
    }
}

By using XmlTextWriter

Methods of XmlTextWriter

Methods

Description

writeStartDocument()

Writes the XML declaration in the beginning of XML document: <? xml version=”1.0” ?>

writeStartElement()

Writes the start tag for a specified element

writeElementString()

Writes an element that contains a string value

writeStartAttribute()

Writes the start of attribute

writeAttributeString()

Writes the value of given attribute

writeEndAttribute()

Writes the end of attribute

writeEndElement()

Writes the end tag of element

 

By saving a DataSet as XML data

DataSet can be saved in XML format by using the GetXml() method. It returns the XML representation of a dataset.

String xmlString=daProduct.GetXml();

Dataset object methods

Method

Description

WriteXml()

Enables to write the data of the datset into an XML file

WriteXmlSchema()

Enables to write the data in the dataset as on XML Schema

 

Following code can be used to write the details stored in the Product table to an XML file:

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data.SqlClient;
    
namespace xmlWrite
    {
class Program
    {
static void Main(string[ ] args)
{
    SqlConnection con=new SqlConnection();
Con.ConnectionString=”DataSource=SQLSERVER01;Intial Catalog=HR;User ID=sa;Password=password#1234”;
// Create a data adapter
SqlDataAdapter da=new SqlDataAdapter(“Select * from Product”,con);
DataSet ds=new DataSet();
da.Fill(ds,”Product”);
// Write dataset contents to an xml file by calling WriteXml() method
ds.WriteXml(“D:\\ProductDetails.xml”);
}
}
    }

WriteXml( ) takes two parameters first the destination file where the output of XML file will be stored and second but optional parameter is XmlWriteMode option that specifies how the XML output is to be written.

 

XmlWriteMode option

Description

IgnoreSchema

Used to write the contents of dataset as XML data without an XML Schema. It is default

WriteSchema

Used to write the contents of dataset as XML data with the dataset structure as an inline schema

DiffGram

Used to write the contents of dataset as a DiffGram

 

Reading XML Data

The XmlReader class in the System.Xml namespace provides non-cached, forward only and read only access to XML data.

The objects of XmlReader can be created by using the Create( ) method. The XmlReaderSettings class is used to specify the settings or properties.

Properties of XmlReaderSettings class

XmlReaderSettings

Description

CheckCharacters

Indicates whether to do character check or not

ConformanceLevel

Gets or Sets the level of compliance

IgnoreComments

Gets or Sets whether to ignore comments or not

IgnoreProcessingInstruction

Gets or Sets whether to ignore processing instructions or not

ValidationType

Gets or Sets a value indicating whether the reader should perform validation or not

Schemas

Gets or Sets the XmlSchemaSet object to use while performing schema validation

 

XmlReaderSettings settings=new XmlReaderSettings( );
XmlReader reader=null;
Reader=XmlReader.Create(“D:\\Products.xml”,settings);write.WriteStartAttribute(“StudentID”);
writer.WriteString(“S-109”);
writer.WriteEndAttribute();writer.WriteStartElement(“Student”);
writer.WriteString(“Robin”);
writer.WriteEndElement();

XmlTextReader

XmlTextReader class is derived class of XmlReader class. The XmlTextReader class reads the XML document but does not allow to access or validate the schema information.

Method

Description

IsStartElement()

Verifies the element is the start element

MoveToElement()

Moves to the element that contains of the current attribute node

MoveToFirstAttribute()

Moves to the first attribute

MoveToNextAttribute()

Moves to the next attribute

Read()

Reads the very next node from the stream

ReadAttributeValue()

Reads the value of the attribute and parses it into one or more nodes.

ReadString()

Reads the content of an element or text node in a string

ReadStartElement()

Verifies if the current node is an element and moves the reader to the next node.

ReadEndElement()

Verifies if the current node is an end tag and moves the reader to the next node

Skip()

Skips the child nodes of the current node

 

Validating XML Data

A Schema is used to define the structure of an XML document. An XML document is considered to be well formed if it conforms to all the syntactical requirements, as defined by the W3C XML 1.0 recommendation.

XML document can validate in the following ways:

· XmlReader

· XmlValidatingReader

Validating XML document using XmlReader

The XmlReader class can impose validation by using a XSD Schema. The ValidationType property on the XmlReaderSettings class determines whether the XmlReader instance imposes validation or not.

Validating XML document using XmlValidatingReader

An XML document can be validated by using the XmlValidatingReader as well. The XmlValidatingReader class uses the ValidationEventHandler() callback method that is called when a validation error occurs.

The following code explains validation of XML document using XmlValidatingReader class.

 

XmlTextReader textReader=new XmlTextReader(“Product.xml”);
XmlValidatingReader validatingReader=new XmlValidatingReader(“textReader”);

 

To validate an XML document, first load XmlValidatingReader for an XML document by using the Load() method.

To validate an XML document, first load XmlValidatingReader for an XML document by using the Load() method.
XmlDocument xdoc=new XmlDocument();
XmlTextReader textReader=new XmlTextReader(“Product.xml”);
XmlValidatingReader validatingReader=new XmlValidatingReader(textReader);
xdoc.Load(validatingReader);

After loading the XmlValidaingReader for an XML document, the ValidationType property is used to specify the type of validation that we have to perform on that document.

DiffGrams

When we create an XML representation of a dataset, ADO.NET enables to present the data in a DiffGram format. The DiffGram format preserves information about changes to the data in the dataset so that we can choose whether to accept or reject the changes when we read the XML data back to the dataset.

A DiffGram is an XML format that identifies current and original versions of data elements.

Elements of DiffGram

Block

Description

<DataInstance>

It contains the name of the dataset or the data table. It contains the current data.

<diffgr:before>

It contains the original row version

<diffgr:errors>

It contains the error information for a particular row

 

Attributes of DiffGram

Attribute

Description

Id

Used to pair the elements in the <diffgr:before> and <diff:errors> blocks to elements in the <DataInstance> block

parented

It identifies which element from the <DataInstance> block is the parent element of the current element

hasChanges

It identifies a row in the <DataInstance> block as modified. It has three values:

inserted

modified

descent

hasErrors

It identifies a row in the<DataInstance> block with a RowError. The error is placed in the <diffgr:errors> block

Error

It contains the text of the RowError for an element in the <diff:errors> block

Following code explains it:

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data.SqlClient;
    namespace xmlWrite
    {
        class Program
        {
            static void Main(string[] args)
            {
            SqlConnection con=new SqlConnection();
                con.ConnectionString=”Data Source=SQLSERVER01; Initial Catalog=HR;User ID=sa;Password=password#1234”;
                con.Open();
                SqlDataAdapter da=new SqlDataAdapter(“Select * from Product”,con);
                DataSet ds=new DataSet();
                da.Fill(ds,”Product”);
                //write dataset contents to an XML file by calling WriteXml() method in a diffgram mode
                ds.WriteXml(“D:\\ProductDetails.xml”, XmlWriteMode.DiffGram);
                con.Close();
            }    
        }
    }

 

Like us on Facebook