XML

It is all about describing data. Below is a XML which describes Patient data.

1

1000001

Sion

Cat

An XML tag is not something predefined but it is something we have to define according to our needs. The XML document is self-explanatory, anyone can easily understand looking at the XML data what exactly it means. “version” tag shows which version of XML is used. Root element is the top most element for a XML. Every tag in XML which is opened should have a closing tag. It is case sensitive.

Our projects need XML because it is meant to exchange data between two entities as we can define our user friendly tags with ease. In real world scenarios XML is meant to exchange data.

DTD defines how our XML should structure. So we can define the DTD document and use that DTD document within that XML. If an XML is confirming to XML rules then it’s a well formed XML and if it is confirming to DTD rules then it is a valid XML. All data is normally parsed in XML but if we want to exclude some elements we will need to put those elements in CDATA section. We can format a XML document with CSS. XSL is used to transform XML document to some other document. So its transformation document which can convert XML to some other document.

Difference between XML and HTML

XML describes data while HTML describes how the data should be displayed. So HTML is about displaying information while XML is about describing information. One cannot be the replacement of other. Both go together as one is for describing data while other is for displaying data.

Implementation

Defining a column as XML is a feature provided by SQL Server. We can define a column data type as XML for a table.

Specify XML data type

If there is a XSD schema specified to the data type then it’s typed or else it’s untyped. If we specify XSD then with every insert SQL Server we have to validate and see that is the data adhering to XSD specification of the data type.

Below is the DDL statement for creating XML schema.

CREATE XML SCHEMA COLLECTION MyXSD AS

N’

elementFormDefault=”qualified” targetNamespace=”http://MyXSD”>

 
‘

After the schema gets created we can see the MYXSD schema in the schema collections folder.

XSD can be viewed in explorer of Management Studio

When we create the XML data type we can assign the MyXsd to the column.

MyXSD assigned to a column.

It has now XSD schema attached to it and hence it is now a well formed datatype. The above table I have named as xmltable. We have specified in the schema two nodes one is FirstName and the other LastName. To insert we need the below insert statement.

insert into xmltable values(‘AbhishekSrivastav’)

Maximum size for XML datatype is 2GB and is stored like varbinary. In a typical XML table below is the type of data which is seen.

XML data

XQUERY is a language defined by W3C to query and manipulate data in XML. Below is a sample.

SELECT * FROM xmltable where TestXml.exist(‘declare namespace xd=”http://MyXSD”;/xd:MyXSD[xd:FirstName eq “Abhishek”]’) = 1

XML data types have huge size 2 GB. To create a XML index first of all we should add a new column as a primary key in the table. Then we can use the below SQL statement to create index on the XML column.

CREATE PRIMARY XML INDEX xmlindex ON xmltable(TestXML)

Secondary indexes are built on document attributes. FOR XML clause returns data in XML rather than simple rows and columns. To get XML output we can use the below query.

SELECT * FROM Patient FOR XML AUTO

The below SQL Syntax will return the SCHEMA of the table.

SELECT * FROM Patient FOR XML AUTO, XMLSCHEMA

So, FOR XML returns a XML format of a table data whereas OPEN XML statement in SQL Server does the vice versa of it. If we pass XML document to it then it will convert it to rows and columns. In case, we have a huge XML file “MyXml.xml” and we want to load in database table “MyTable” we can use the below SQL statement.

INSERT into MyTable(MyXMLColumn) SELECT * FROM OPENROWSET (Bulk ‘d:MyXML.xml’, SINGLE_CLOB) as abc

XMLA

It stands for XML for Analysis Services. Using XMLA we can expose the Analysis service data to the external world in XML. So that any data source can consume it as XML is universally known.